Publish External Reports - Credentials

My previous post introduced the topic of publishing reports for external usage and the conclusion drawn was that - as far as I can tell - the only reason why this action would be performed would be to grant non-CRM users a view into CRM data. But unless we make some changes to the report credentials, external users will be prevented from running the report. This post describes how to go about performing this step.

We'll use the default "User Summary" report for the sake of this exercise. In order to demonstrate how this works end to end, first modify the Default Filter for this report and arbritarily add some filter criteria. We'll add a filter by user name.

Subsequently edit the report and select the "Publish Report for External Use" option. We'll publish the default "User Summary" report for the sake of this exercise.

The report will now appear in the CRM report server home page (http://CRM/Reports/Pages/Folder.aspx?ItemPath=%2fCRM_MSCRM&ViewMode=Detail).In its current state it will only be accessible to CRM users. In order to make this accessible to other users we need to have it impersonate a user who has the necessary credentials to run the report. By doing so, you will actually be giving permission to anyone who has the URL to run the report.

To do so, hover over the report name and click Manage from the menu that appears.

Let's first take a look at the report parameters. What's interesting to note is that anything that was specified as part of the default filter will be stuffed into the single parameter called CRM_FilteredEntity (in our case CRM_FilteredSystemUser).

So you'll see something like the following appear for this parameter based on the default filter that we used:

select  [systemuser0].*  from  FilteredSystemUser as "systemuser0"  where  ("systemuser0".systemuserid = N'{43AF6573-C97C-E011-9556-00155DA5304E}')

The "systemuserid" is the identity (guid) of Adam Barr's user record (see above). If we had instead specified a default filter of "last name = Smith" then this parameter would like like this:

select  [systemuser0].*  from  FilteredSystemUser as "systemuser0"  where  ("systemuser0".lastname = N'Smith')

You get the picture. Understanding how this parameter is constructed will feature prominently in the next related post. Now let's move onto updating the report credentials.

Click on "Data Sources" and create a custom data source and specify a user that has the necessary permissions as shown.

Alternatively, create a custom data source using the same input as shown above and reference it using the "a shared data source" option shown above. This approach is the recommended approach because if you have more than one external report, you can just link to the same shared data source for each rather than specifying the same credentials in each case (which also helps with maintenance as passwords need to change and such like). And finally bear in mind that if your report has sub-reports then you will already have more than one report that needs to have the credentials updated (yes, all generated reports need to have this applied).

Now that you've performed this action, test the report by running it with a non-CRM user and it should return a report that looks something like what we have below. Note how the result is being limited to the default filter defined for this report in CRM.

Next: We will build on this walk through by manipulating the parameters of the report in order to have the output be dynamic and context sensitive versus the static report output that we've just presented. Being able to do so provides all kinds of interesting options.

Publishing reports externally

It's been a while since I last posted. Things got a little busy over the summer. Anyway, during that period I collected quite a few tidbits here and there and hope to be posting them over the course of the next few weeks. We'll see how it goes.

As anyone who has been using CRM for a while knows, CRM reports can be published externally as shown in the screenshot below. Note: this option is only available to CRM On Premise since there is no report server accessible via online.

After the report has been published it is copied to the Org_MSCRM home page in the report server i.e. http://reportserver/Reports/Pages/Folder.aspx?ItemPath=%2fOrg_MSCRM&ViewMode=Detail. Navigating to this location you will see the result of your publish action - the report along with any sub-reports that it referenced will be published as shown. In this case, the published report contained 3 sub-reports resulting in 4 reports being published.

But the question has to be asked - seems like a nifty little piece of functionality - but what is the practical application of it? To compound it further - if you try and run this report from the reporting server location - it will only work for a CRM user that has access to the CRM database. And therefore why would you not just execute the same report from within CRM? The latter option provides you with the CRM pre-filter option and a generally much better overall user experience - so there is no reason that I can think for a CRM user to execute the report from anywhere other than from within CRM.

In light of all of the above, the only reason that I can think of for using this feature is to provide a report that can be accessed by non-CRM users. There are a number of scenarios in increasing degrees of complexity:

1. Just provide a URL for external users to be able to access the report
2. Use workflow to send out a report link to non-CRM users based on a given business scenario
3. Use workflow to email a PDF document based on an externally published report

As will be demonstrated over the next few posts, each of these builds on top of one another. And an issue that we'll have to contend in both cases 2 and 3 is one of context sensitivity i.e. typically if you're automating something you're going to want it to generate a report that is specific to the quote, order, invoice etc. that it was generated from.

The first thing we need to deal with is giving access to an externally published report. Because, if you've been paying attention, there is an inherent contradiction - externally published reports cannot be accessed by non-CRM users... By default.

In the next post, I will walk through the steps for allowing external users to access the CRM published report.