Tip #44: Reflecting current user in CRM SSRS reports

It is a best practice to reflect the user running the report in an SSRS report and when the report was run in the report footer. This gives you a snapshot of who ran the report, and if there are any questions about the validity of the data reflected in the report, can be very helpful to troubleshoot the issues. Maybe the person running the report doesn’t have security permission to view all the data.

If you read an SSRS book, it will probably tell you to use the built in User!UserID expression in SSRS to reflect the current user. However, when writing reports for CRM, this doesn’t work. The reason is, for CRM reports, all users authenticate to the SSRS server using the same account (the Microsoft Dynamics CRM service account). If you use User!UserID, the report will reflect the name of the CRM service account in the footer.

If you are using SQL based reports, you can get the current user by using the CRM SQL function fn_FindUserGuid. Create a dataset in your RDL with the following query.

select fullname
from FilteredSystemUser
where systemuserid = dbo.fn_FindUserGuid()

If you are using Fetch based reports, you can get the current user by using the following FetchXML query.

<fetch version="1.0" output-format="xml-platform" 
       mapping="logical" distinct="false">
   <entity name="systemuser">
      <attribute name="fullname" />
      <attribute name="systemuserid" />
      <order attribute="fullname" descending="false" />
      <filter type="and">
         <condition attribute="systemuserid" 
                    operator="eq-userid" />
      </filter>
   </entity>
</fetch>
Share on FacebookTweet about this on TwitterShare on Google+

3 thoughts on “Tip #44: Reflecting current user in CRM SSRS reports

  1. Daniel says:

    Exactly what I was looking for! Thanks.

  2. Katie says:

    Brilliant, this was exactly what I needed. Thanks so much!

  3. Javier says:

    Thank you! Great tip!

Leave a Reply

Your email address will not be published. Required fields are marked *