Getting Started with CRM 2011 Online Fetch XML Reporting

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:

image

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:

image

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

image

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:

image

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

image

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:

image

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

image

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

image

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:

image  image

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)…

image

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

image  

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:

image

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

image  image

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

image

 

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
About these ads

39 thoughts on “Getting Started with CRM 2011 Online Fetch XML Reporting

  1. Gareth,

    Thanks for the awesome site. An amaxing collection of answers and insights into some of the more advanced (for me) issues I’m grappling with in trying to move to the next level of service for my CRM clients.

    Doug Allinger

  2. Pingback: CRM 2011 – Creating Custom Reports with Bids « Hosk's Dynamic CRM 2011 Blog
  3. Hello Gareth,
    Thanks a lot for your blog, very helpful.
    I was wondering about this fetchxml reporting , do you know if there is a way to retrieve a report (.rdl) from CRM 2011 online and get it in fetchxml?
    I would like to add a company logo to the “quote” report and move some fields.
    Thanks.

    • Yes you can. Select the Report, click the Edit button on the Ribbon. Then from the Actions menu select Download Report. That will give you the RDL file which you can then open in Visual Studio to make your edits.

      • Awesome timing, I was just wondering exactly the same question. Thanks for the assistance Gareth.

  4. Great help :-)
    Do you also know how to get the guid of the current record that the report is run from and use that parameter in your FetchXML?

    • Hi Don
      Did you ever get an answer to this as I am trying to do the same thing now. I would like the report to run on the current record of my form only.
      Great articles Gareth,
      Thanks Caroline

  5. HI Gaereth,

    I am trying to create a Sales Order report where the report only runs for the form the user is on. How would I filter that in my Advanced Find query?

  6. Pingback: Dynamics CRM: On-demand or on-premises? Consider your reporting requirements | Pedro Innecco
  7. Hi Gareth,

    Thanks for this great tutorial, it’s helped me get started on FetchXML reports.

    I’ve hit a sticking point: Trying to format a currency value. In a SQL report, I’d normally use crm_moneyformatstring which comes from a SQL function. Any idea if there’s an alternate to this for Fetch? SDK doesn’t have any info on it.

    Cheers!

  8. Pingback: Building a Better User Account Report | MsCrm King
  9. Hi Gareth,
    Thank you for this great post (and blog), it helped me so much get started on crm.

    I have a problem with reports and subreports in CRM 2011 online and hope you can help me with it (I’ve searched over the internet and in forums of social.microsoft.com and I didn’t got response).

    I’ve created two reports what works fine separately after uploaded to crm 2011 online but when I link second report as subreport of first one and upload it, primary report works fine but the section where subreport had to show I see the message: “Error: Subreport could not be shown.”

    I need to pass parameters to subreport and I try it with parameters and prefiltering but when I make this changes subreport fails, if I delete the changes subreport shows but the data is wrong (because of filter)

    Thanks.

  10. Pingback: FetchXML Reporting in Microsoft Dynamics CRM 2011–Part 1 - CRMConsult.info - CRM Technical Blogs - Microsoft Dynamics Community
  11. Pingback: CRM & SSRS -Kevin's Mocha
  12. Gareth. If I’m not mistaken, fetchXML will return more than 5000 records. I did some testing with a client that has well over 5000 records using CRM online and it did return the number of records they had in excess of 5000. Is it certain that fetch will really only return that many records?

  13. Pingback: SSRS + Fetch - elblogdeDynamicsCRM.com (Spanish) - CRM Technical Blogs - Microsoft Dynamics Community
  14. Pingback: SSRS y Fetch - elblogdeDynamicsCRM.com (Spanish) - CRM Technical Blogs - Microsoft Dynamics Community
  15. Pingback: CRM Reports - ABTO Software
  16. Pingback: SSRS + Fetch « MSExpertos
  17. FetchXML will return up to 50k rows in CRM Online – check it out yourself – the 5k is a limit on what is displayed on views, but the rows are still there – up to 50k. The 5k limit appears to be an urban legend.

  18. Pingback: Create a Simple CRM Online Report in 30 Minutes or Less « Donna Edwards – MSCRM Dynamics
  19. Pingback: Create a Simple CRM Online Report in 30 Minutes or Less - Donna Edwards – MSCRM Dynamics - CRM Technical Blogs - Microsoft Dynamics Community
  20. Pingback: CRM Online Reporting with FetchXML (Intro) | Stoneridge Software
  21. Hi Gareth,

    Thanks for this really comprehensive description of how to build a fetchxml report. I just have one question left… when making SQL-reports it was possible to use CRMAF_ to use CRM’s dynamic filtering as parameters for the report. Is such a thing at all possible with fetchxml-reports? It doesn’t look that way,but I just want to make sure before heading down a wrong track.

    Maria

  22. Pingback: Dynamics CRM 2011 Online default Reports not editable - EMEA Dynamics CRM Support - Site Home - MSDN Blogs
  23. Pingback: On Dynamics CRM Online Default Reports Not Editable - EMEA Dynamics CRM Support - Site Home - MSDN Blogs
  24. Pingback: Working with Price List Items in Dynamics CRM | Surviving CRM
  25. Pingback: Working with Price List Items in Dynamics CRM - Surviving CRM - Microsoft Dynamics CRM - Microsoft Dynamics Community

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s