Wednesday, November 16, 2011

Filtered Fetch XML Reports for CRM 2011 On-line

I found very few blogs that explained how to create a Filtered Report for CRM 2011 using Fetch XML. My approach had been to use Advanced Find to create the Fetch XML and then copy and paste that into a Report in Business Intelligence Development Studio.  Now that works fine but when you publish the report to CRM it will only run on all records of the entity, it does not work on selected rows or on a single entity record.  The report just does not show up as selectable on the entity view of on the entity form. 

The missing attributes are on the the entity element of the Fetch XML.  Mine is a custom entity called new_identifiedneed and so I need to change
<entity name="new_identifiedneed">
<entity="new_identifiedneed" enableprefiltering="1" prefilterparametername="CRM_Filterednew_identifiedneed">

Now what is not made clear is that setting the correct prefilterparametername is crucial. It needs to be the name of the Filtered View for the entity prefixed by CRM_  (not CRMAF_ as for SQL based reports).

If you do this on the query  you will find that it will automatically add a parameter to your dataset. When you run the Query you will get this prompt for the parameter.

Don't bother with entering a value, just click OK and you should get unfiltered results. 
Check out the parameters on the DataSet which should look like this:

If that is present you're home free. Make the report look pretty, build it to create the rdl and then load it into CRM.  When you add the report be sure to set the Related Records (Identified Needs in my case) and select the Display in properties to include both the Form and the List.
Now go to the entity view and select on or more records. Click on the Run Report icon on the ribbon and you should see your report. When you run it on Selected Records you should see what you wanted.  The same applies when you open a form on a selected entity record. 

Happy Reporting!
BTW I found this out by using the Report Wizard for ad hoc reports in CRM. It will not only create the appropriate Fetch XML but gives you an RDL file that you can open up and play with in Business Intelligence Development Studio. 

No comments: