Reporting in CRM Online 2013/2015 – Part 1

Introduction

As CRM Online does not allow direct access to the SQL database, customer reports in CRM Online are based on a combination of FetchXML, basic .rdl reports and customization in Visual Studio.

FetchXML is a proprietary query language that is used in Microsoft Dynamics CRM 2013 and Microsoft Dynamics CRM Online. It’s based on a schema that describes the capabilities of the language. The FetchXML language supports similar query capabilities as query expressions. In addition, it’s used as a serialized form of query, used to save a query as a user-owned saved view in the userquery entity and as an organization-owned saved view in the savedquery entity.

General Steps

  1. Look for a report in CRM Online which contains less or more the information you want.
  2. Export the report (.rdl) file and import it in a (new) project in Visual Studio
  3. Customize the .rdl report in VS
  4. Add data from FetchXML query
  5. Save and reimport the customized report in CRM Online

Prerequisites

CRM 2013

Microsoft Dynamics CRM 2013 Report Authoring Extension is required to author custom Fetch-based reports for Microsoft Dynamics CRM by using Business Intelligence Development Studio or SQL Server Data Tools (Short: BIDS extension): http://www.microsoft.com/en-US/download/details.aspx?id=40343

CRM 2015

Microsoft Dynamics CRM 2015 Report Authoring Extension is required to author custom Fetch-based reports for Microsoft Dynamics CRM by using Microsoft SQL Server Data Tools Business Intelligence (BI): https://www.microsoft.com/en-us/download/details.aspx?id=45013

How to

Look for a report in CRM Online which contains less or more the information you want.

Standard reports can be found in Sales – Reports.
Do not run the report if you want to export it.
Edit the report and then look for “actions” – “Download Report”.
Report quote

You will then receive a .rdl file

Report quote rdl file

Export the report (.rdl) file and import it in a new project in Visual Studio.

Make a new Project or
Visual Studio Project 1

open and old project in VS.
Visual Studio Project 2

Add a data source to the project (or share it so you can use it for multiple reports) in the Solution Explorer.
Data source 1

FYI: You can also do this the other way around; start from a report with a data source and convert this data source to a shared data source.
Data source 2

For an online data source the properties are as this:
Data source Properties 1

Data source Properties 2

You have now made the data source for the project. This does not mean that a report under this project has automatically a data source, you will still need to add a data source to each report.

Next, you add the .rdl report you exported from CRM Online to the project:
Adding the rdl

Importing this .rdl report will fill in 2 things:

  • The data source (you will still need to add the right credentials)
  • The data set: this will contain the columns from the report

You are  now set to go and start customizing the report.

We will do this in a second post.

2 Comments

  1. What version of Visual Studio is needed for these steps to work. I not that Visual Studio 2012 is no longer supported by Microsoft. At least can’t seem to find where to get it except in 30 day trial version form.

Leave a Reply

Your email address will not be published. Required fields are marked *