CRM Reports – DataSource

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

Cheers,

Donna

Windows Live Tags: Reports,Data Source,Administrator,MSCRM,Initial Catalog,Report Wizard,Create,Dashboards, Edit Datasource