The Microsoft Dynamics CRM Blog
News and views from the Microsoft Dynamics CRM Team

How it Works: SQL Server Reporting Services and Dynamics CRM

How it Works: SQL Server Reporting Services and Dynamics CRM

  • Comments 13

At Tech-Ed Developer in Orlando a few weeks back I lead an interactive session on CRM and Business Intelligence. The session was open to any CRM and BI topic so I expected a lot of hard questions about data mining and the like but the topic of greatest interest turned out to be the CRM and SQL Server Reporting Services (SSRS) integration. This should be helpful to folks with questions about deployment practices or with an interest in exposing CRM reports to users outside of the CRM application.

First things:

  1. With CRM 4 reports are an entity within CRM. They have meta data and CRM security is applied to determine whether or not a user may view the report . Note that that is the report, not the underlying data. Users can have access to CRM data that a report points to and not have access to the report itself. Likewise a user may have access to a report but not the data that it would show (in which case the user could run the report but it would return no data).
  2. The SQL Reporting Services Report Viewer is an ASP.Net control which runs on the CRM 4.0 Web server. In CRM 3.0, and when you interact with the SSRS Report Manager, that control is running on the Web server fronting SSRS. When you choose to run a report from CRM 4.0 the ASP.Net control requests the report and data from the remote SSRS box. In practical terms: in CRM 3.0 the URL for a report was the URL for the SSRS Web server; in CRM 4.0 the URL for a report is the CRM Web server.

Because CRM 4.0 reports are always run in a delegated mode the CRM and SSRS integration has to handle security. There are two ways to do this in CRM 4.0. One way to do this is to use integrated authentication where trust for delegation is required between the CRM server, the SSRS server and the SQL server with the CRM db. This was the required configuration on CRM 3.0 and frankly, it was a bit of a headache for folks to manage [see HOW TO: Configure Kerberos authentication for Microsoft CRM 3.0 and Microsoft SQL Server Reporting Services and Microsoft CRM 3.0: Additional Setup Tasks Required if Reporting Services Is Installed on Different Server . ]

The other mechanism is to use the SQL Server Reporting Services MS CRM connector. This connector runs as an SSRS Data Processing Extension and handles all of the delegation for you. The use of the data connector is recommended for Internet facing deployments and anywhere users are not using NT Auth to connect to CRM. When using the Data Connector users of CRM cannot directly access the RDLs in SSRS – all management of reports must be done through the CRM reporting UI; users connecting to the SSRS Report Manager will get an access denied message if they try to browse Reports.

Choosing a deployment type is up to you and of course there are pros and cons either way. The following table describes some of those (if you have others throw them in the comments).

Capability

SQL Server Reporting Services Data Connector

Kerberos Authentication

Works with Internet Facing Deployments

Yes

No

Schedule reports using the Report Scheduling wizard in CRM

Yes

No

Uses NT credentials to connect to SQL Views

No

Yes

Access CRM reports outside of CRM

No

Yes

Use the CRM Report Wizard

Yes

Yes

Keeps CRM data secure

Yes!

Yes!

The table speaks for itself and I think that for most organizations the Connector is probably the right way to go. But let me point out one item that is near and dear to me: “Access CRM reports outside of CRM”. One of the great things about SSRS is its direct URL access to reports; along with that are the ability the embed reports into Microsoft Office SharePoint sites, in Performance Point dashboards, on your own ASPX pages using the ASP.Net control or my favorite: embedded with forms of the CRM application itself. If you use the connector you won’t be able to use URL access for reports; this is so useful though that we made sure to give you a work around.

clip_image001If you have the “Add Reporting Services Reports” privilege you’ll see a command on the Action menu of the Report form titled “Publish Report for External Use”. This command will publish your report and any child reports to a directory in SSRS that is open to all CRM users. You can embed the URL to that report, along with any arguments on the query string, within CRM or the Report Viewer controls.

You won’t get any feedback that this worked so you’ll just have to trust but verify that it did. Doing this multiple times will also overwrite any existing report with the same name in the target directory so this isn’t the most… elegant… solution but there isn’t a demo environment that I have that doesn’t take advantage of it.

 

Cheers,

Barry Givens

  • PingBack from http://wordnew.acne-reveiw.info/?p=10540

  • Is there any way to make the CRM reports subscribable in SSRS?  I would like to make the Sales Pipeline report automatically email itself to the sales manager but there is no easy way to do that that I've seen.  The current solution requires going in and reworking the datasources for the boxed reports due SSRS saved credentials requirement for email subscriptions.  Any suggestions on a workaround?  

  • I'm curious as we are getting ready to bring in CRM 4.0, and since the is a semi-recent thread....Any gotchas with respect to reporting? Performance hits or anything else that would lead me to deploy a little differently than the standard install such as a dedicated report server? As we've never had CRM, I'm not sure what to be wary of or how big (and fast) that this may grow. I'm hoping to disallow all but the most savvy users from creating reports (if possible) to prevent runaway joins and the like and potentially killing our server or performance. Any suggestions? All will be appreciated.

  • I need to add existing sql report to the CRM. The report is running from other sql server.

    But while running the report i am getting error.Report cannot be displayed.

  • can anyone tell me if crm 4.0 can be installed on SBS 2003 that is running SQL 2000?  client does not want to upgrade to SQL 2005 at this time.

  • Hi all,

    I want to create one report in CRM 4.0 using SQL Reporting services with the following specification. Report should list out all Service activity (Resource person wise). But there is no link between the tables Service Activity and Resource Base. In CRM when creating service activity, it is possible to lookup the resource field and retrieves it. But in Back End (In SQL Server 2008), not able to find out the table relation. I want to know how to relate these two tables.

  • Regarding Vrinda's question - how to list resources by service activity. You'll have to use the FilteredActivityParty view to access the resources on a service activity.

    CRM activities have a special lookup type which can contain multiple items. That lookup type is an Activity Party lookup. It can contain multiple accounts, systemusers, contacts or facilities. To report on actvity paties data you'll have to pull the data out as an array. To get a grouping by activity I'm thinking that your SQL statement would need to create a temp table for the party members and then join it to the service activity table. Not a simple operation but not the most complex either.

  • I use windows authentication and do what you told, the Unauthorized error still occur.

    why?

  • Okay, but I still don't know what the connector actually IS, can anybody explain? It's not an application. Is it a script that works over my AD when I run it(like to know that sort of little details), or is it a new IIS site? Is it a bird? A plane? What?

  • Okay, but I still don't know what the connector actually IS, can anybody explain? It's not an application. Is it a script that works over my AD when I run it(like to know that sort of little details), or is it a new IIS site? Is it a bird? A plane? What?

  • Okay, but I still don't know what the connector actually IS, can anybody explain? It's not an application. Is it a script that works over my AD when I run it(like to know that sort of little details), or is it a new IIS site? Is it a bird? A plane? What?

  • I need a reference (manual, guide, etc.) to point to as to the requirement to install SSRS Data Connector for Microsoft Dynamics CRM 4.0. CRM is on one terver DEV4, SQL & SSRS on another server DEV3. We want use integrated SSRS (rdl’s) reports from with in CRM such as for Quote. So if a user is in a Quote they select from the menu bar “Run on Current Record”. Come to find out that SSRS Data Connector has not been installed on any of our servers. It is my opiion #1 that it needs to be installed on at least one server. #2, and that SSRS Data Connector to be installed in our environment to be installed on the SQL / SSRS server. I have to prove to the DBA who is over the SQL / SSRS Server that the SSRS data connector for CRM is required.

    Please point me to a link / reference, please and thank you. And please provide thoughts.

  • Hi Everyone

    I create a report, then Publish report for external use and add the URL to IFrame. Next, I add it to your dashboard IFrame. When I run DashBoard, DashBoard just fine but It request Login. Although i have Publish report for External Use. I using CRM Dynamic 2011. I need your help.

    Thank all

Page 1 of 1 (13 items)
Leave a Comment
  • Please add 2 and 6 and type the answer here:
  • Post