Follow Eagle

Get Blog Updates By Email

Your email:

Learn More about Eagle Software and Services

Pipeline GIS Softwaredescribe the image

Eagle Pipeline GIS Blog

Current Articles | RSS Feed RSS Feed

Embedding Esri Feature Service Attachments in SSRS Reports

  
  
  

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.

Esri Feature Service Model Diagram

 

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:

Feature Attachment DB Structure

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:

http://:6080/arcgis/rest/services/EIM_PUG_2012/PipelineMapFeatures/FeatureServer/0//attachments/

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:

http:///arcgis/rest/services/EIM_PUG_2012/PipelineMapFeatures/FeatureServer/0/5601/attachments/3201

Once the SQL is ready, build a new SSRS report using this query:

Building an SSRS Report

If we run the report now, we get something like this:

SSRS Report with no Images

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.

SSRS Image Properties

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.

SSRS Report With Feature Service Images

Let us know what type of reports you generate in the comments!

Comments

Woah! I’m really enjoying the template/theme of this site. It’s simple, yet effective. A lot of times it’s challenging to get that perfect balance between usability and visual appearance. 
Posted @ Friday, November 28, 2014 5:18 AM by Child Tax Benefit In Mississauga
Post Comment
Name
 *
Email
 *
Website (optional)
Comment
 *

Allowed tags: <a> link, <b> bold, <i> italics