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

DataSource1 6.10

  • Select Data Sources from the left navigation menu

DataSource2 6.10

  • 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

DataSource3 6.10

  • 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

DataSource4 6.10

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

4 thoughts on “CRM Reports – DataSource

  1. 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.

      Cheers

  2. Hi,
    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.
    BR Phil

  3. Hi,
    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.
    BR Phil

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s