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.
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).
SQL Server Reporting Services Data Connector
Works with Internet Facing Deployments
Schedule reports using the Report Scheduling wizard in CRM
Uses NT credentials to connect to SQL Views
Access CRM reports outside of CRM
Use the CRM Report Wizard
Keeps CRM data secure
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.
If 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.
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.
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.
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.
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.