One trick veteran report writers like to use when writing SSRS reports for Dynamics CRM is to use a stored procedure for the report query. There are several appealing reasons to do this:
- Performance: Stored procedures are cached, which can give them a performance advantage when compared to a query in the report. This advantage is diminished in modern versions of SQL, but I have seen complex, long running reports where the query timed out when embedded in the report, but completed successfully when using a stored procedure.
- Separation of code: by separating the query in a stored procedure, it can sometimes be easier to troubleshoot/manage the query when it is separated from the RDL. We’ve all seen weird behavior when editing an existing CRM report–you upload it and the old version is cached, so you have to remove and republish the entire report. This can get very complicated if you have multiple subreports. By separating the query into a stored procedure, I can update the query and filters without having to republish the report.
- Reuse of code: Sometimes you need to use the same query for multiple reports. A stored procedure lets me have a single copy of the query used by multiple reports.
There are also some potential downsides to using stored procedures for CRM reports:
- It requires the report writer to have direct access to the CRM SQL database. When using embedded queries in reports, the report writer can author reports from SQL data tools without having direct access to the SQL database. Creating and editing of stored procedures requires a higher level of SQL database privileges than embedded queries.
- It is borderline unsupported. This falls into the same gray area that creating custom indexes does. It is not documented in the implementation guide or the SDK, so it is not officially supported. It also requires elevation of the default database permissions. However, it can be a safe thing to do, as long as your stored procedures work in a supported way: Don’t write directly to the database, don’t query base tables, use filtered views. If you do this, you can remain close to supported with minimal risk.
Enabling stored procedures for reports
So you weigh the pros and cons and decide that you are comfortable with using stored procedures in CRM reports. How do you make it work?
To enable stored procedures for reports, you need to understand how reporting in CRM works. This applies to CRM 2011-2015. When I run a report in CRM, the report is not actually being run by me. Via the SRS data connector, CRM runs the report under the context of the service account that SSRS is running as on the CRM SSRS server.
- Log on to the SSRS server used by your CRM deployment. Pull up services and find the SQL Server Reporting Services windows service. Go to the properties of the service and look at the “Log on” tab. Look for the service account that the SSRS service runs as.
- Log on to the SQL server used by your CRM deployment. Go to SQL Management Studio. Add the service account found in step 1 to SQL as a user.
- Grant the service account “execute” permission to each stored procedure used by your SSRS report.
I have to disagree with your bullet point . Creating and updating indices is supported. Sure, it is not risk free and not that easy to get right, but supported.
See bullet point 4 in documentation linked.
You are right, adding indexes is supported, but it can be done in a way that is unsupported. Your link confirms that stored procedures are officially unsupported.
Hi Guys I have created stored procedure and designed report in visual studio 2010 shell. I can run report in visual studio but when I deploy it on CRM it doesn’t filter results for current record, instead it shows all record. Any help is appreciated.
1. I have used filtered views from crm database.
2. My stored procedure is in custom database and granted permission to SQLAccessGroup,ReportingGroup and PrivReportingGroup
3.I can run report on CRM but does not filter results on current record.
4.Shows error when adding CRM_Filteredentity parameter
features like crm_* parameters, prefiltering, etc, only work for CRM reports reading CRM data. While it can work to join to external custom databases and grant permissions to the service accounts/groups to get them to read data, you generally lose the crm specific functionality for prefiltering, etc. This is also unsupported.
I have been able to get it to work (prefiltering external data sets) by doing a query just from the filtered views and then joining in the external database to the filtered results. Again, unsupported and your mileage may vary.
I have created report for CRM where I have used stored procedure created in custom database and designed in Visual Studio 2010 shell. My report shows results expected in visual studio but doesn’t filter results for current record when deploying on CRM 2015.
1. I have granted permission to SQLAccessGroup,PrivReportingGroup and ReportingGroup for stored Proc
2. It gives me error when try to add CRM_Filteredentity parameter
3. I have user filteredviews in stored procedure.
Any help will be appreciated.