Many organizations use Dynamics 365 for Sales for creating Quotes and providing an emailed or hard copy document to their customers. Dynamics 365 for Sales has a robust Quoting functionality that is out of the box, but some organizations may want a tailored document that meets their unique needs and the needs of their customers. This is where SQL Server Reporting Services (SSRS) comes in! SSRS allows the creation of reports that can be generated from Dynamics 365 data using Fetch XML or SQL queries.

In this blog post, we will cover a requirement to display the Quote owner's signature on the Quote document. Fortunately, this can be done using SSRS and notes/attachments in Dynamics 365. There are many resources online for using SSRS and Dynamics 365 together, so that will not be covered in this post.

Step 1:

Create an entity in Dynamics 365 enabled for Notes that has a relationship to the Quote entity or an entity related to the Quote. This entity will store the user's signature and show whose signature it is. To store who the signature is for, you can use the Owner field or create a new lookup field to the User entity. In the below screenshot, we use a new custom lookup field, po_userid.

In the example above, the entity used to store signature information is called "Report Localizations." We have an additional option set field to identify that this record is for storing a User's signature.

Step 2:

On your existing SSRS report, add a new dataset with FetchXML that queries this entity.

The above example passes in the quoteid parameter of this example report. From the Quote entity, it links to the User entity through the Owner field on the Quote. From User, it links to the Report Localization entity and finally to the Annotation (Note) entity where the image lives.

Step 3:

Add an Image control to your report. The Name and ToolTip can be whatever you like. The Image source should be "Database" and the MIME Type should be the file type of whatever image you added as an attachment. The expression should be similar to:

=Lookup("Image_UserSIgnature",Fields!l_po_label.Value,Fields!aa-documentbody.Value, "LocalizationImages")

This expression will lookup Report Localization records with the Label = ImageUserSignature then display the attached image file.

Voila! Looking for more Dynamics 365 related tips and tricks? Be sure to subscribe to our blog!

Happy Dynamics 365'ing!