As more and more organizations deploy to the cloud, FetchXML becomes the primary query method for Dynamics 365 Reports as Microsoft does not expose the SQL Server for reporting purposes. This creates new challenges to meet our customers’ complex reporting needs, given that most of what we need to do we need to do with FetchXML. One challenge is to dynamically add images to a report: for example, adding a signature image or a header image on a report.
Traditionally, there are three ways to get images onto your report.
Embed the image into the report The down side is you can’t change the images without updating the report itself. The image is serialized into the .rdl file that is uploaded to Dynamics 365.
Use a URL For this you have to have a web server that can return the resource, generally not an option for the cloud version of Dynamics 365.
Return the image from a SQL data source This requires that you are using the on-premises version of Dynamics CRM. You can’t connect directly to the SQL server for your Dynamics CRM instance when it is hosted by Microsoft in the cloud.
Recently we had a customer that wanted to control an image that displayed in the header of a Dynamics 365 report. They wanted to be able to change the image occasionally without requiring an update to the report. To accomplish this, we needed to Base64 encoded the image into a text field, return it with a FecthXML query, and convert it back to an image in the SSRS report. This requires a plugin that takes an image in Dynamics 365 and Base64 encodes it whenever it changes. As it turns out, this is a very simple plugin.
1) Add a configuration entity to hold an image for your report.
2) Upload an image to the entity.
3) Encode it into a text field.
4) Return it to the SSRS report.
5) Display the encoded string as an image on the report.
I have broken this solution into three steps. First create an entity to hold the image, add a Plugin to encode the image, and then convert the image back for display in the report.
Add an Entity to store the image
Let’s create a ConfigurationSettings entity that has various fields on it that the customer can edit. This allows the customer to change header and footer text and the header image any time. Since we can have many instances of this entity, we also add a field called “Key” that will identify where the values are used and allow us to select a particular record.
This entity will need the “Primary Image” selection set to “Image”. This will automatically add a field called EntityImage of type Image to the entity. To allow a user to upload and see the image on a form we need to check the “Show image on the form” checkbox in the Display Properties on the form. Now we can create instances of this ConfigurationSettings entity and add an image to it and/or any other text values that we want displayed in the report. In my case I have set the Key field to “HEADER_IMAGE”. I use this Key field value to select the correct record when building the report.
The image stored in CRM is not in a format that we can access through FetchXML queries. In order to use this image we need to convert it to a Base64 encoded string and place the string in a field that we can query using FetchXML. For this I have added one more field to the SystemValues entity called imagebase64 and made it multi-line text field.
Create a Plugin
Now we need a plugin that will encode an image on a CRM entity into a text field on that same entity. This plugin will need to be registered to the Update message for the Pre-operation stage on the ConfigurationSettings entity. This way whenever a user changes the image, a new Base64 encoded version of it will be put in the imagebase64 text field before it is saved.
The code is very simple, basically one line in the ProcessImage method that encodes the image and adds it to the text field called “new_imagebase64”.
Display the image in the SSRS Report
To display the image in an SSRS report, use the usual FetchXML query to get the field called new_imagebase64 into a dataset. In my use case I also returned other text strings to display in the report so I have a data set just for configuration of the report. This dataset has many records with text for various parts of the report. Wherever I need a configured piece of text displayed I use the Lookup function in SSRS to get it from the data set. I use the “Key” value to select the correct configuration record.
To add an image to the report, add an Image control and set the source to “Database”, set the MIME type to match the image type, and add this code to the field expression:
=System.Convert.FromBase64String(Lookup(“HEADER_IMAGE”,Fields!inew_key.Value, Fields!new_imagebase64.Value,” ConfigurationSettings”))
The Lookup() function selects the record with the “Key” value =”HEADER_IMAGE” from the data set ConfigurationSettings returning the text from the field new_imagebase64. This text is the Base64 encoded version of the image.
The key to getting the image displayed is the System.Convert.FromBase64String() function. This function takes the Base64 encoded string and makes an image out of it. Thus completing the round trip going from an image in CRM -> to a string ->to the report -> to an image displayed in SSRS.
Hopefully someday FetchXML will support an “Image” type that will encode the image for us and return it as a Base64 string. This would eliminate the need to write the plugin. One thing to remember is this will only work for smallish images. If the image is large and/or very high resolution, the maximum size on the text field can be surpassed. Also, FetchXML has a string size limit that you could reach if the image is large.