Preparing a SQL Server Reporting Services (SSRS) Report to Use Filtering and Fetch XML in CRM 2011

Sooner or later, you will come across a more complicated report than can be handled in the report wizard or with custom views in CRM 2011. No problem, just write one in reporting services using Business Intelligence Developer Studio, right? But what if you want to use filtering in CRM to allow users the flexibility in selecting records? There are a few simple rules to follow in creating your report that will make publishing the report back to CRM painless and very functional.

The first key is to make sure your development environment is configured to connect directly to CRM.

If you’re new to reporting services, by far the easiest way to get started is by downloading and installing SQL Server Express 2008 with Advanced Services. You can download it here.

Once you’ve done this, make sure you know what URL and organization name to use in creating your connection. You can find this by logging into your CRM instance, and going to Settings, Customizations, Developer Resources.

When you do, take note of the Organization Name.

You will need the CRM URL as well as the organization name to create the connection for the report.

Next, create a reporting project inside of BIDS (Visual Studio 2008 has a reporting services component, so if you already have the full 2008 installer, you’re good to go).

Now add a new report. When you get to the connections page, you should have an option for using “Microsoft Dynamics CRM Fetch” as a data source type.

Select Microsoft Dynamics CRM Fetch as your connection type, then type in the URL for your CRM implementation, followed by a semicolon, then the organization name. If you don’t provide an organization name, the default organization will be used. I always use the organization name for clarity.

Next, click on “Credentials” and provide your connection login information.

Click on “OK”, then “Next”, and you should be on the “Design the Query” form of the wizard.

Here, you have a few options on how to proceed. CRM uses Fetch XML queries to provide filtering for reports. You can learn the syntax yourself, and write the queries from scratch (examples can be found here). There are also tools available online that will build the XML for you. The other way, and the way I prefer, is to let CRM do the work for you. First, build your starting query using Advanced Find in CRM.

In the example below, I will use Advanced find to build a simple query of active Accounts. Make the query as simple or as complex as you like. Add columns, filters. View your results and confirm that the columns are a good starting point for your report.

Next, choose “Download Fetch XML”. This will save an XML file in the format you need. Open the XML file with notepad or the editor of your choice and copy the XML.

Back in BIDS, click on the Query Builder button.

Then paste the XML into the text section of the Query Builder and click on the button with the Exclamation Point.

When the query is complete, you should see columns of data below.

Now that we have a connection, and a base query to start working from, there are a few tweaks we need to make to the Fetch XML to make it useful inside of CRM.

CRM Looks at the entity definitions to determine how to apply filtering.

<entity name="account" enableprefiltering="1" prefilterparametername="CRM_Filteredaccount">

First, add: enableprefiltering="1" to the entity XML definition.
Then, add: prefilterparametername="CRM_FilteredX"> where “X” is the entity name. The prefix of CRM_Filtered tells CRM that the entity is to be filtered based off of user-provided filtering definitions (the filters applied at the time the report is run, or the default filters set on the report in CRM). Also important is the capitalization. The entity name should be the entity name, exactly as it appears in the entity properties screen:

Keep in mind that when you provide a “filter” XML tag in the query itself, the conditions will apply in addition to what the user provides. From a user-friendly perspective, you may want to remove the filter all together, unless it is significant to the report functioning as you desire.

When you’ve made the change to the entity tag, press the exclamation point button again. Now when the query is run, you should be prompted for an input parameter.

For now, leave the Parameter Value “<Blank>” and the query will run as if there was no filter set from the user. Your results grid should update.

If you would like all columns from an entity to be available in the report, you can also remove all the attribute tags and use “<all-attributes/>” instead.

Here’s how my FetchXML looks after the changes mentioned here:

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<entity name="account" enableprefiltering="1" prefilterparametername="CRM_Filteredaccount">
<all-attributes/>
<link-entity name="contact" from="contactid" to="primarycontactid" visible="false" link-type="outer" alias="accountprimarycontactidcontactcontactid">
<attribute name="emailaddress1" />
</link-entity>
</entity>
</fetch>

From this point, the data results are treated like any other data source in reporting services. Format your report as needed, and save the report.

 

Once you have the report running to your satisfaction (Use the Preview!), you’re ready to publish to CRM.

Go to the Reports view, and choose “New”.

Choose the Report Type of “Existing”, then click on “Browse” and select the report .RDL (Report Definition) file.

The form will prefill with the Name of the report. Select what areas you would like to display the report in, and set any default filtering for the report.

Your report should now run with filters that the user selects, or that you specify in the report definitions in CRM.

Post by: Michael Quattlebaum, Customer Effective