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.