{Fix}–SSRS Reports in Dynamics CRM not running under executing user’s context

Wondering how is it possible. I myself was confused when the one of the developers in our project reported this. Surely I thought it is not possible. There is something the developer is confusing with.

So I checked the report and what I did is print the logged in userid in the report using the function dbo.fn_finduserguid(). And the developer was right, it was not fetching the executing user’s guid in the report.

Also in parallel to that, the reports were not running connecting to the the organization in which they were uploaded. They were running with the dataset specified in the BIDS while developing the report. So I  was pretty sure that it has to do something with the way the report got uploaded.

 

So I logged in the report sever and browsed to the report. There is a trick in the way you should browse the report so that you can troubleshoot it. One of our developers was using the url – /ReporServer">http://<servername>/ReporServer. And below is the screen you get once you browse the url. We have three organizations in our environment and for each of organizations, there exists a report folder in the environment which I have blurred here.

 

If you go inside any of these folders, you would find something like below. These are all the guids of the reports you have uploaded for your organization. Not much use isn’t it?

 

What you need to is go to URL – /Reports">http://<servername>/Reports. Then go inside you organization folder and click on the Detailed View. And then click on custom reports. Then you would find all the reports that you have uploaded for your organization. Please check for the screenshots below.

 

Now going back to the main topic, I select my report and from the report menu, click of manage and then click on datasources.

 

 

When you publish a report in Dynamics CRM, internally it configures the connection to use the shared data source for its organization and makes the option of Connect Using to – Credentials supplied by the user running the report.

However in this case, the option ‘Custom data source’ was selected with the connection string that was used to develop the report. I had to black it out due to security reasons.

So first of all I changed the data source to use the shared data source and enabled the option Credentials supplied by the user running the report. Then everything started working as expected.

 

So far so good. But why did the report upload like that. What about some other reports which got uploaded in the same way from CRM. Do I have to go and publish each report by changing the data source?

Publishing the reports again using the publishreports.exe utility in CRM.

Open a command window and navigate to C:\Program Files\Microsoft Dynamics CRM\Tools\   and run PublishReports.exe. It requires a parameter to specify your organization name. PublishReports.exe "<Organization_name>".

Hope this helps!