CRM Report Filtering

Roshan Mehta, 03 June 2010

I have been working on a few complex reports for different clients over the past few weeks and have run into a few problems with getting the report to display the correct data. For example, I had to create a custom Account Overview report which was intended to be run against a single Account, and display information about the account itself, it’s primary and related contacts, open opportunities, active quotes, unresolved cases, open activities, the last three interactions with the account, as well as data from a couple of custom entities in the system.

This report was by far the most difficult report I’ve ever had to produce, but gave me good practice with applying automatic filtering using the CRMAF_<FilteredViewName> alias. This alias is recognized by the CRM system so that it knows to only run the report on the selected record, not all records in the system. In most cases, this automatic filtering will work without any problems, but with this report containing many datasets and multiple joins, there were bound to be some challenges.

The first major challenge was writing the SQL correctly to make sure that I was retrieving the correct data. This required the use of multiple datasets and report tables, as well as multiple joins between filtered views. The second challenge was the actual design of the report. The report consists of data from many different tables, so it was important to display the data in a consistent and readable format.
The biggest challenge occurred when it had come to the time to test the report. I realised that the report wasn’t filtering on the selected Account, and instead, it was pulling through all primary and related contacts, open opportunities, etc from the system. The reason for this was the complexity of the report, so automatic filtering was not going to work. Instead, we needed to use what is called explicit filtering. The MSDN has a nice article on report filtering which you can find here http://msdn.microsoft.com/en-us/library/aa685875.aspx.

I then re-tested the report and found that it still wasn’t behaving correctly. This is because CRM applies a default filter to the report so it only returns records that were modified within the last 30 days, which is not the behaviour that we want, so this filter needs to be removed. This is an important step that can be easily over-looked. To remove the default filter, follow the steps below:

1. Navigate to the Reports area and select (but do not open) the report that you want to modify.
2. Click on More Actions.
3. Click on Edit Default Filter. A new window will pop up.
   
4. Modify the default filter as you like. I used the “Clear” button to remove all default filters.
5. Click on Save Default Filter.

The report now behaves exactly as expected, returning data from multiple entities related to a single account. I look forward to putting these techniques into practice again with some more complex reports.