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

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

  1. Doug Allinger

    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

    Reply
  2. Pingback: CRM 2011 – Creating Custom Reports with Bids « Hosk's Dynamic CRM 2011 Blog

  3. jia

    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.

    Reply
    1. Gareth Tucker Post author

      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.

      Reply
      1. Scott

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

  4. Don

    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?

    Reply
    1. Caroline

      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

      Reply
  5. James Bamford

    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?

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

  7. Gareth Williams

    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!

    Reply
  8. Pingback: Building a Better User Account Report | MsCrm King

  9. Oscar Iglesias

    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.

    Reply
  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. Tom

    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?

    Reply
    1. Chris

      the 5000 record limitation is in the BIDS development environment. actual reports can pull more records…

      Reply
  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. Joe

    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.

    Reply
  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. Reading Bliss

    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

    Reply
  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

  26. Shaun Harrison

    Hi I have a fetch xml query that is using the entity Products, the xml is getting the selected products names from a grid, but I can’t find a way of getting the selected product quantities, Any suggestions?

    Reply
  27. Pingback: Know how to Modify Reports with XML Part 2

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