Gareth Tucker's Microsoft CRM Blog

Here’s some hints on how to get up and running writing SSRS reports in Visual Studio for CRM 2011 Online.  Target audience is those folk who have written SQL query based SSRS reports for CRM 4.0 who need to up skill on the use of Fetch XML in their report development.

Getting Setup

Here’s what you need:

- Install SQL Server 2008 R2 Business Intelligent Development Studio from your SQL Server install media

- Install Microsoft Dynamics CRM 2011 Fetch Authoring Extension, which you can download from here.

This will give you the ability to create SSRS reports inside Visual Studio 2008 using the Microsoft Dynamics CRM Fetch data source.  If you didn’t already have Visual Studio installed the install of SQL Server Business Intelligent Development Studio will load it onto your computer (you get a cut down version of VS that just supports report development). 

Creating a report is then as per what you are used to except rather than choosing Microsoft SQL Server as your data source you chose Microsoft Dynamics CRM Fetch and you provide your CRM 2011 online URL, Organisation Unique Name and Windows Live credentials.

The connection string needs to be in this format:

The first bit you can just copy and paste from Internet Explorer.  The second bit is the unique identifier assigned to your Organisation. 

WARNING!: this is not the organisation alias included in your CRM Online URL, it is a different value that you have to go here to find by going to Settings –> Customisation –> Developer Resources:

The value you want is the one specified as the Organization Unique Name:

Now, you only need this if your windows live account is associated with more than one Organization but if you are writing custom reports I suspect you’ll be associated to more than one.

Let’s create our first report… 

Start in CRM 2011 in Advanced Find and create a query for your report, say – All Open Opportunities with an Estimated Close Date in the next 3 months.  Select columns to match the fields you will want available to you in the report designer:

Click the Download Fetch XML button and save the Fetch XML query to your desktop:

Now jump over to SQL Server Business Intelligence Studio, start a new Report Server project, add a new Report and configure the data source as described earlier:

Click Next, and then paste in your downloaded Fetch XML:

Now continue through the Report Wizard to define the report appearance and then run the report:

Tiding up the report appearance should be business as usual for you if you have built SSRS reports before so I won’t go into that here.

If you missed some fields you can either redo your Advanced Find and paste new Fetch XML over the existing Dataset’s query or just edit the query in the same manner that would have if you had used a SQL query:

Next, let’s add a parameter to the report.  The easiest way to do this is to start with a Fetch XML statement that has a hardcoded condition in it (like ours does)…

… and then swap out the hardcoded value for a parameter variable (anything you like so long as it starts with an “@”):

Run the report and you will see that the heavy lifting has been done for you and a (fairly rough looking) report parameter has been configured for you and it immediately works:

You can then tidy the parameter up as you normally would, be editing its prompting text and setting a default value, etc:

And it won’t be long before your report becomes reasonable looking and a bit more bit functional:

Happy report writing!

p.s. Fetch XML has a few limitations still which will quickly surface as you start building reports.  You need to be aware of these:

• You cannot specify left outer joins – e.g. The following is not supported: “Select all Accounts who do not have a related Completed Appointment record”
• You cannot specify group by / sum queries  – e.g. The following is not supported: “Select Account Name, count(*), sum(est. value) from Account Group By Account Name”  – You can only select the records in detail and then perform the aggregation in your report.  But…
• Fetch XML queries return a maximum of 5000 records.

If these are shot stoppers then your options are:

• Deploy CRM 2011 on premise rather than online
• Use SSIS (or similar) to retrieve CRM data each night to create a local CRM data source
• Use plug-ins (or similar) to populate aggregate fields / aggregate entities / left outer join indicator fields