Generating Asset Reports with Photos & Maps
The majority of our customers want to get asset master data to upload into their maintenance system (CMMS/EAM). That data is commonly exported from ORDITAL into EXCEL spreadsheets in conformity with the client’s master data upload template. However, from time to time our clients want to produce reports that include photographs and maps. In this blog post I’ll provide a workflow to produce these reports that utilizes Microsoft Excel and Microsoft Word along with exports from the ORDITAL database.
In this sample we’ll export the following data from the ASSETS table (geolocations randomly generated in this example) :
Then we want to export the following data from the PHOTOS table. The photo is a URL to an Amazon S3 bucket (the data is an example only and does not go to a valid bucket/image).
Then with an Excel lookup we join the Assets & Photos into a single record that can be addressed by a Microsoft Word Mail Merge. In the example below we have a maximum number of 3 photos per item. When doing a mail merge you have do decide on the maximum possible number of photos you want to display so you can set up your template appropriately.
In the load sheet above there is a MAP column, but the contents are hidden. This is a formula that provides a link to the geolocation on a Google Map generated as an image. Lets describe how that works.
The simple approach is to have 4 cells in Excel that contains the following information:
This is a formula that is simply LATITUDE&”, “&LONGITUDE
&key=<<YOUR GOOGLE API KEY>>
Substitute your own Google API Key for <<YOUR GOOGLE API KEY>>
Then you put these strings together as 1 & 2 & 3 & 2 & 4 and it creates a very long string like this
https://maps.googleapis.com/maps/api/staticmap?center=-37.7318024057069,144.970020159586&zoom=19&maptype=satellite&size=400x400&markers=color:red%7Clabel:AC%7C-37.7318024057069,144.970020159586&key=<<YOUR GOOGLE API KEY>>
Setting Up your Word Template
In Microsoft Word you want to lay out your template as required, then go to “MAILINGS” > “SELECT RECIPIENTS” > “USE AN EXISTING LIST” to load the spreadsheet we created above.
Then we want to insert the images (photos & maps). To do this we go to “INSERT”> “QUICK PARTS” > “FIELD” then select “IncludePicture” and give the Filename or URL a temporary value (ie. TEMP-PIC1). We will repeat this process four times (one for each of the 3 photos and 1 for the map).
Then we will press the ALT-F9 toggle to swap to an edit screen where we can replace the temporary Filename or URL values we entered above with the MAIL MERGE values (ie. use MAILINGS > INSERT MERGE FIELD). At the end of this process it should look something like this.
Performing the Mail Merge
The Microsoft Word Mail Merge will generate a new document with a new page for each record in the spreadsheet. If that is what you want then just use the default Microsoft Mail Merge. However, many of our clients want an individual document (typically a PDF) for every asset, and the default Microsoft Mail Merge does not handle this.
However, there is a great utility from the following web site that will produce individual PDF’s and name those files based on a field in the mail merge spreadsheet (ie. assetId.PDF). The web site is here (http://www.gmayor.com/MergeAndSplit.htm). Typical disclaimers - ORDITAL is not affiliated, does not guarantee, or endorse this organization/tool.
Also, in some instances you may want the generated document to be saved as a JPG - in this case there are sites that will perform this in bulk like - https://pdftoimage.com/ - once again, ORDITAL is not affiliated, does not guarantee, or endorse this organization/tool.
Once all the templates are set up - which is generally a one time thing - you can produce reports very quickly by generating these mail merges from the spreadsheets exported from ORDITAL. The document below is a JPG generated from the PDF mail merge.