CRM 2011 Reporting Options - On-line and On-Premise

Most users are familiar with the out of the box reporting tools (Advanced Find, Report Wizard and CRM Dashboards leveraging system views) in Microsoft Dynamics CRM, but there are other custom reporting tools available for when the out of the box methods don’t provide the results you want. This post will describe your CRM 2011 Reporting Options, the differences between them, and the environments for which they are best suited.

CRM 2011 Reporting Options Overview

Reporting options for CRM 2011: CRM 2011 On-line now supports custom reports using FetchXML based reports. CRM 2011 On-Premise continues to provide custom reporting capabilities using SQL based reports and filtered views.

Reports extend the capabilities of the built-in features such as Advanced Find and Charts as well as more advanced calculations, grouping, and visualizations such as gauges and spark lines.

SQL Reporting Services Reports are the standard reporting for CRM 2011 as they have been since version 3.0. The development environment utilizes traditional SQL reporting tools however with CRM 2011 on-line we can now access the data using FetchXML and the Report Authoring Extension.

FetchXML Defined

Fetch is a proprietary query language that is used in CRM and supports similar query capabilities as query expression. FetchXL supports all the features of QueryExpression plus aggregates and grouping. Queries are built as XML statements that conform to the schema definition for the FetchXML language. Fetch has always been used in Microsoft Dynamics CRM but with 2011 support is provided for reporting using the Report Authoring Extension.

The Development tools

Challenges and Limitations of CRM On-line Reporting

There are challenges with FetchXML reporting including direct SQL access and accessing other data sources. For report writers new to the FetchXML syntax there will be a learning curve to adjust to the elements required and differences from SQL functionality. The best starting point is to create an advanced find and download the Fetch XML created by the advanced find query. This can then be used as the query to begin the report with further refinements for filtering, parameters, and aggregations.

In addition to Data Access requiring FetchXML, potential limitations of using CRM on-line reporting include:

On-Line

On-Premise

Everything you ever wanted to know about CRM 2011 reporting options and more. Feel free to reach out with any of your CRM reporting needs. 

Happy CRM'ing!

Going On-Premise: A CRM Post-Upgrade Checklist for SSRS

Upgrading CRM On-Premise has become an almost routine procedure over the past few years. Despite well-managed compatibility both forward and backwards, sometimes On-Premise's uncommonly used entities fall through the cracks during the process, causing potential for significant problems in production environments. To save you from searching through multiple help pages, we've complied a checklist of common issues and fixes in today's blog!

Let's go through an example. Say you just upgraded and a report is scheduled to run on the first day of the next month and then be left alone. All is good and well but then for some reason the scheduled report snapshots are not generated. What's going on?

Further investigation leads to the discovery of three issues that could be considered residual of the systems upgrade. Solutions are found by sifting through similar cases and exceptions posted on different help sites on the internet. This blog combines a few of these issues into a check-list that can supplement every CRM upgrade and configuration procedure.

Permission to the SSRS Execution Account

The first exception we needed to tackle was related to permissions. Specifically, the SSRS execution account must be able to execute MSCRM_CONFIG.dbo.p_GetCrmUserId stored procedure. It does not matter how you grant this permission, but it is worth checking to make sure it is in place. If it is not, you will see some deceiving reports behavior and inconclusive diagnostics in the SSRS logs.

Note: Check if the SSRS execution account is granted 'Execute' permission on MSCRM_CONFIG.dbo.p_GetCrmUserId and grant it as needed.

Snapshot Isolation Mode of SSRS Database

The next exception was even more deceiving because it was generic and showed only on a few scheduled report executions. No direct correlation was found, but it cleared after the following patch was run on the SSRS database:

ALTER DATABASE reportserver$crm

SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE reportserver$crmtempdb

SET ALLOW_SNAPSHOT_ISOLATION OFF;

ALTER DATABASE reportserver$crm

SET READ_COMMITTED_SNAPSHOT OFF;

ALTER DATABASE reportserver$crmtempdb

SET READ_COMMITTED_SNAPSHOT OFF;

Essentially, after the SQL upgrade, the new SSRS database had the snapshot isolation mode turned on. If you know the specific reasons for keeping it this way then you probably do not need to read this post. Otherwise, this patch allowed me to proceed to the next exception.

Note: Switch your SSRS server snapshot isolation mode OFF.

ReportingServicesService.exe.config

The last exception was related to a simple incompatibility of the .NET binding. SSRS web service has configuration stored in web.config while the Reporting Service stores it in a different file named ReportingServicesService.exe.config. Both configurations must have the following part in runtime.assemblyBinding section:

<dependentAssembly>

<assemblyIdentity name="Microsoft.Crm.Reporting.RdlHelper" publicKeyToken="31bf3856ad364e35" culture="neutral" />

<bindingRedirect oldVersion="5.0.0.0" newVersion="8.0.0.0" />

<bindingRedirect oldVersion="6.0.0.0" newVersion="8.0.0.0" />

<bindingRedirect oldVersion="7.0.0.0" newVersion="8.0.0.0" />

</dependentAssembly>

This will ensure compatibility of the reports compiled with the previous versions of RdlHelper with the latest one installed with the update.*

*This assumes the correct target version of the installed DLL which increments along with the version of CRM and SSRS.

Note: Check the proper binding redirects for RdlHelper in service and web application configs.

Overall, applying and verifying these suggested changes can help you avoid some common misconfigurations. These fixes do not replace the diligent testing needed for each report in the system, but they may help you substantially decrease the time it takes to investigate unknown issues you may have while performing CRM upgrades.

To learn more, tune in for our upcoming webinar series on CRM of Dynamics 365:

CRM for Dynamics 365: What's New Overview 
Thursday, January 5, 1:00-1:30 pm

Service CRM for Dynamics 365: What's New in Field Service
Tuesday, January 10, 1:00-1:30 pm

CRM for Dynamics 365: App for Outlook 
Thursday, January 12, 1:00-1:30 pm

CRM for Dynamics 365: Relationship Insights
Tuesday, January 17, 1:00-1:30 pm

CRM for Dynamics 365: Mobile Features
Thursday, January 19, 1:00-1:30 pm

CRM for Dynamics 365: What's New in Portals
Tuesday, January 24, 1:00-1:30 pm

CRM for Dynamics 365: Designing the User Experience
Thursday, January 26, 1:00-1:30 pm

CRM for Dynamics 365: Learning Paths
Tuesday, January 31, 1:00-1:30 pm

CRM for Dynamics 365: What's New in Project Service
Thursday, February 2, 1:00-1:30 pm

Happy CRM'ing!