Publish External Reports - Dynamic Content

Time to complete what I started... So in my last post we walked through the steps to make an externally published report accessible to non-CRM users which would seem to be the objective of the whole publishing externally exercise as documented in the introduction to this topic.

However by following those steps we are limited to a fairly static report based on the report parameters of the originally published report. This is going to be of limited benefit. For example, consider the scenario where I want to include a link via an email to a non-CRM user that would allow them to view data from within CRM - for instance - we might want to send them a report on an opportunity whereby clicking on the link they are able to view the opportunity details. Well, unless we're able to pass in parameters to this published report we will be prevented from doing so i.e. it will lack the necessary context sensitivity.

In order to overcome this limitation we'll be manipulating the CRMFilteredEntity report parameter mentioned in my previous post. We'll continue using the example of the User Summary report for the sake of illustration.

The first thing to do is to navigate to the report using the Report Server navigation i.e.:

Now click on your externally published report (in our case "User Summary") and retrieve the execution URL i.e. http://crm2011/ReportServer/Pages/ReportViewer.aspx?%2fCRM_MSCRM%2fUser+Summary&rs:Command=Render and you should be presented with something that looks like this:

Now take the URL and carefully graft on the value from the CRMFilteredEntity parameter (provided in my previous post). That is, your URL should now include the CRM_FilteredSystemUser parameter and look something like the following:

http://crm2011/ReportServer/Pages/ReportViewer.aspx?%2fCRM_MSCRM%2fUser+Summary&CRM_FilteredSystemUser=select  [systemuser0].*  from  FilteredSystemUser as "systemuser0"  where  ("systemuser0".systemuserid = N'{43AF6573-C97C-E011-9556-00155DA5304E}')&rs:Command=Render

And voila - your report will now render the User Summary report only returning the corresponding user specified by the report parameter.

Also test with a non-CRM user to ensure that this is working outside of CRM authentication.

This exercise proves that we can now manipulate the URL of this report to pass in parameters to the report. Essentially all you need to do is to update the query specified in the CRM_FilteredEntity parameter and this will filter the results of the report. Of course the same approach can be applied to any other report in the system. For example, if we were working with an account report we'd manipulate the query for the CRM_FilteredAccount parameter; if we were working an opportunity report we'd manipulate the query for the CRM_FilteredOpportunity parameter. And so on and so forth.

Now of course this result doesn't lend itself very well to specifying report parameters manually - your users would lynch you if you tried proposing this to them!  But it does lend itself quite well to automated mechanisms such as via workflow whereby you can construct a URL using dynamic workflow fields resulting in a report URL in an email that a user just has to click on in order to see the relevant, context-sensitive, dynamic report content.

In the next post we'll take a look at performing those steps as well as seeing how this approach can also be leveraged to generate PDF documents that can be sent to external users or customers.