CRM 2011 Online Reports with FetchXML

One of the great benifits of CRM 2011 is the ability to create custom reports in SSRS using FetchXML. You have much more control over formatting and grouping. Additionally, you can add custom logos to your report! Who woudn't want that! :)

Before you start, you need to have BIDS installed. BIDS is available as part of SQL Server Install, and is not available free.
Secondly, install Microsoft Dynamics CRM 2011 Report Authoring Extension.


Once you have the above completed, you are ready for building your custom report. The use case is to create a report for an entity that tracks orders that have been shipped in the last x months, and greater than X dollars. We want to group it by Account, and by revenue.

Here are the steps:


1. Use advanced find to create a query that will be used by the report. Choose all the colums that you need in the report. Then select "Download FetchXML" button on the advanced find ribbon. Save the xml file.

2. Go to Settings/ Customizations/ Developer Resources and note down the Organization unique name

 3. Launch BIDS and create a new Report Server Project.

4. In Solution Explorer, right click on reports and select "Add New Report". Give a name for the data source, and select "Microsoft Dynamics CRM Fetch" as the Type. The connection string is in this format:
CRM online url;org_name;home realm url


Home Realm URL is the Identity Provider used by your organization and is needed when your organization uses Federation for identity management. For our setup, I am giving the server url and org name only.

Do not make it a shared data source!

5. Under credentials, provide your live id and password to log into crm online.

6. Create a new data set for the data source created. Select Query Type as "Text". You can now import the FetchXML that was saved in step 1 into the Query area. Choose the Query Designer, and execute the query to make sure you return results that are the same as from the advanced find.

If you want to parameterize the search, replace the value="3" (for last 3 months) with value="@months". Notice I have done the same for total cost.

7. Create the report. From the figure, you will see that I have grouped the report first by Account Name, and I have grouped it further by total revenue for each account and for the grand total.

8. Save the rdl. In crm Online, if you want the report to be part of a solution, go to settings/solution, open/ create a new solution, go to components/ reports and add the new report from file. Choose to display it in the reports section.

9. Run the report from Workplace/ Reports. For this report, I have selected all orders that have shipped in the last 4 months, and have revenue of more than 10,000$.