When we create a demo using Eagle technology we always try to show many different ways you can collect and consume data. One thing we did recently was to embed a SSRS (SQL Server Reporting Services) report into a data collection workflow. Essentially when a user creates a new feature, and it is loaded into the database, a PDF report is generated for the feature and stored on the server. One of the key requirements of this report was to embed in it any attachments that were in the form of a photograph. This way if you take a photo as an attachment in the field, you can easily get the photo back in your report. But how could we get an Esri attachment into a SSRS report?
As it turns out Esri makes this doable! The attachment can be retrieved via the REST service for the feature that the attachment was collected for.
In this picture from Esri’s documentation, we can see that there is an attachment URL that can be created to download an attachment. In order to use this endpoint we will need to know the layerID, featureID and attachmentID. The layerID is simply the number that represents the layer. The featureID is probably going to be the objectID and the attachemntID is probably the objectID of the attachment table, let's take a look:
As we can see from this image, the table we are going to work with, ActionRequired, has an Attachment table created by Esri called ActionRequired__ATTACH. If we look carefully at the data we see that the ActionRequired__ATTACH table contains a column named REL_OBJECTID, which just so happens to contain the ObjectID that the attachment is connected to. One thing to remember here is that for each feature, there can be many attachments (a one-to-many relationship). Given that we understand the relationship we can now write a query to get the IDs that we need for the rest endpoint.
select ar.OBJECTID, ara.ATTACHMENTID from eim_pug_2012.dbo.actionrequired ar left outer join eim_pug_2012.dbo.actionrequired__attach ara on ar.objectid = ara.rel_objectid
This SQL will return all OBJECTIDs and the associated ATTACHMENTID(s) for each record in the ActionRequired table. Now we need to construct the URL using these IDs, we already know the format of the URL from the esri documentation:
In SQL, this looks like:
select ar.OBJECTID, ara.ATTACHMENTID, 'http:///arcgis/rest/services/EIM_PUG_2012/PipelineMapFeatures/FeatureServer/0/'+cast(ar.objectid as VARCHAR)+'/attachments/'+cast(ara.attachmentid as VARCHAR) as attachmentURL from eim_pug_2012.dbo.actionrequired ar left outer join eim_pug_2012.dbo.actionrequired__attach ara on ar.objectid = ara.rel_objectid
This gives us a URL like:
Once the SQL is ready, build a new SSRS report using this query:
If we run the report now, we get something like this:
Now, in design view, right click on a column and add a new column. Then select the cell, right click on it and go to Insert > Image.
Change the source to “External”, from the drop down under Use This Image: select the column that has the URL in it.
Change to the Size tab, and make sure the Display is set to “Fit proportional” and click OK to insert the image.
Now run the report! A word of caution though, if there are many records, you might want to update your query to only return a few of them, as it can take a while for the feature service to return each image.
Let us know what type of reports you generate in the comments!