In most environments, CRM reports uses a Shared Data Source for retrieving and viewing report data. This data source helps to determine the type of data a CRM user can access when viewing a report.
There are some scenarios when the CRM System Administrator might want to override security and allow all users access to the data return for a report. For example, some user’s may only have rights to view Account record data that they own. The System Administrator might create a report that shows a map of all the Accounts in CRM by State, County, Zip Code, etc. The Management Team wants all CRM users to be able to view that data ‘On Demand’ and not be restricted by Security Role conditions.
One way to achieve the above is to modify the CRM report’s datasource. Here are the steps:
- Go to your SQL Server Reporting Services Home page. The URL should be something like http://CRM_SQL_Server_Name/reports/pages/folder.aspx
- In the top right corner select Show Details
- Select your CRM Organization folder that contains your list of CRM reports
- Select the 4.0 folder
- Find the report that you want to edit
- Select the Edit icon to the left of the report
- Select Data Sources from the left navigation menu
- After selecting the Data Source link, you’ll see that the report is using the MSCRM data source which is a shared Data Source for the CRM application
- Just below the “shared data source” option, you’ll notice a “custom data source” option. We’ll use this option to change the data source used by the report.
- Select the “custom data source option” from the menu
- Select Microsoft SQL Server from the Connection Type picklist options
- Enter “Data Source=CRM_SQLServer_Name;Initial Catalog=CRM_Org_Name”
- In the next option, select, “Credentials stored securely in the report server”.
- Enter a User Name like the CRM System Administrator or any other user that has full access to all the CRM data
- Enter the User’s Password
- Select the check to “Use as Windows credentials when connecting to the data source
- Select Apply
- Your Data Source Connection properties should look similar to this
Your changes should be saved and you are ready to test the report from CRM. You can test the report by having someone who does not have security rights to the data run the report and confirm that he or she can view the data from the report as expected.
Have a look at the links below if you would like to learn more about SQL Server Reporting Services in relationship to CRM and discover the many possibilities.
How it Works: SQL Server Reporting Services and Dynamics CRM
Microsoft Dynamics CRM 4.0 – Report Wizard with John O’Donnell
Create a report in 15 minutes or less
Dashboards Made Easy With Reporting Services
Windows Live Tags: Reports,Data Source,Administrator,MSCRM,Initial Catalog,Report Wizard,Create,Dashboards, Edit Datasource
Hey I know this is off topic but I was wondering if
you knew of any widgets I could add to my blog that automatically tweet my
newest twitter updates. I’ve been looking for a plug-in like this for quite some time and was hoping maybe you would have some experience with something like this. Please let me know if you run into anything. I truly enjoy reading your blog and I look forward to your new updates.
WordPress has a widget that you can add to your blog that displays your twitter feed. You can find it in the widget area of your dashboard. If you are trying to automatically tweet new blog articles that you write, I use Windows Live Writer for that. There is a setting in Windows Live Writer that sends a tweet whenever I publish a new blog article from the program.
I am currently facing the problem that whenever I import a new Report-Solution the datasource configuration for one special report gets overruled and resetted to the default MSCRM_DataSource. Do you have any idea how to fix this?
Thank you in advance.
I am currently facing the problem that whenever I import a new Report-Solution the datasource configuration of one special report gets resetted to the default MSCRM_DataSource. Do you have any idea how to fix this?
Thank you in advance.