Microsoft Dynamics CRM provides some great tools for out-of-the-box reporting. It is not uncommon, however, to find the need to create a custom report using BIDS, and if this report needs to be context-sensitive (meaning run from a form for a single record, or from a grid where multiple records are selected) then the approach becomes less clear.

Luckily, CRM provides an easy way to achieve this using what is called “pre-filtering.” The implementation will depend on whether the query for your report is written in SQL or in FetchXML, but the end result is the same.

For the purposes of this example, our query will retrieve the Name, AccountNumber, PostalCode and Telephone1 of the primary address for any selected accounts. You start by selecting the fields from the Filtered view for the Account entity:

To indicate to CRM that you would like to enable pre-filtering for this query, simply alias the FilteredAccount view with the prefix ‘CRMAF_’, so that your query now looks like this:

Now, depending on from where this report is being run, the accounts returned will only be relevant to the selected records (e.g., if the report is run from an Account’s form, this information will only be returned for that Account).

If instead of SQL you decide to use FetchXML, pre-filtering is still an option. The original query written in FetchXML will look like this:

To enable pre-filtering in this situation, add the ‘enableprefiltering’ attribute to the entity node and set its value to ‘1’. Doing so will result in the pre-filtered query looking like this:

Like this post? Share it!