POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

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.

crm2011

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 <link-entity> 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!

Avatar for JoeCRM

JoeCRM

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