Friday, June 28, 2013

SSRS Prefiltering for Microsoft Dynamics CRM 2011 On-Premise in a Snap, The Simple Scenario That Most People Want.

I feel like I had to poke around way too hard to get the easy answer of how to prefilter SSRS reports the way I see it in the system.  This example uses SQL-Based FetchXML based reporting on CRM 2011 On-Premise

This allows you to:

  • Run a report on just the record you are in from the entity form
  • Run the report from a view on just the records in the view or just selected records within that view
  • Run Advanced Find style filtering on your report if just ran from the reports section.
There are two steps:

1. You need to modify your query to include a specific prefixed alias.  This signals CRM to pass in the context of the records and filtering to apply by parsing in a subquery.

An example query without pre-filtering:

SELECT accountid, name FROM FilteredAccount

The same query with pre-filtering enabled:

SELECT accountid, name FROM FilteredAccount AS CRMAF_FilteredAccount

So that's all you need to do at the report level to enable pre-filtering

2. When uploading the report, you need to choose the "Related Record Types"  and add Forms for related record types, lists for related record types to the "Display In"  section.

-I hope this helps!

No comments:

Post a Comment