Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


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

Post Author: Joe D365 |

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

  • Business Intelligence Design Studio
    • Primary tool for developing CRM reports
    • Manage report projects
      • Report aggregation
      • Code source control
  • Feature Install from SQL Server 2008
  • FetchXML requires the CRM 2011 Report Authoring Extension
    • Install BIDS (or Visual Studio with Reporting
    • Download and install extensions
    • Report Builder 3.0
      • Works with On-Premise
      • Free client-based download
      • Ideal for users new to SQL reporting

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:

  • Ability to specify left outer joins. For example you would not be able to create a report that lists all Contacts that do not have an activity in the last 30 days
  • The maximum number of returned records is 5000
  • Out-of-the-box reports for CRM On-line are SQL based
  • On-line Reports cannot use non-CRM online data sources
  • Data Warehousing and other Business Intelligence initiatives would require local data which could mean a local database and SSIS packages to retrieve CRM data

On-Line

  • Prerequisites
    • SQL Server Business Intelligence Development Studio
    • Microsoft Dynamics CRM 2011 Report Authoring Extension
  • Security – Windows LiveID
  • Key Elements
    • Dataset/Query
      • FetchXML
      • Design Advanced Find – Download FetchXML
      • Dataset embedded in report
    • Parameters - Pre-filtering supported
      • Enableprefiltering=”1”
      • Presents user with Advanced Find functionality
    • Grouping
      • Fetch XML schema uses the element
      • Filter is placed in the From clause
      • Aggregate function may be used; ex: Count
    • Design and Deploy
      • Pass parameters to sub-report
      • Design reports in BIDS as normal
      • Upload through CRM UI

On-Premise

  • Prerequisites
    • SQL Server Business Intelligence Development Studio  or
    • SQL Server Report Builder 3.0
  • Security – CRM user login for filtered view
  • Key Elements
    • Dataset/Query
      • Access to CRM database via Filtered Views or
      • FetchXML
      • Shared dataset supported
    • Parameters
      • Pre-filtering supported
        • CRMAF_  prefix in query
        • Presents user with Advanced Find functionality
        • Utilize special CRM hidden parameters
        • Supports standard SQL report parameters
    • Grouping
      • Standard syntax using SQL joins
      • Filter is placed in the Where clause
    • Design and Deploy
      • Design reports in BIDS as normal
      • Parent – Sub-Reports supported with BIDS
      • Upload through CRM UI

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!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

4 comments on “CRM 2011 Reporting Options - On-line and On-Premise”

  1. Hi - Great article! Thanks for the excellent resource. Another limitation of the FetchXML based query is that it won't allow the multi-party type information (Appointment Regarding etc.) to be returned, as in an 'array'.
    - Ian

    1. Can we use shared Dataset with SSRS? How can we use that in CRM 2011 On Premise environment? Do I have to create this in SSRS reporting server?

      1. Hi Sam - Yes, you can use a shared dataset with ssrs. You would do this within ssrs reporting services and build the report with visual studio.

  2. Hi Joe, I already have SSRS report created in BIDS. It using lot of joins and filter conditon, How do I connect it to CRM, and get GUID to pass into report as parameter.

PowerObjects Recommends