Enable pre filtering in SQL Based SSRS reports in CRM

Hello everyone,

In this article I am going to talk about how to enable pre filtering in SQL Based SSRS reports in CRM.

With the help of pre filtering in SQL based SSRS reports in CRM we can use reports as we desired without filtering in CRM or passing parameters to the SSRS report.

You can use pre-filtering with these conditions according to what MSDN says:

You can use data pre-filtering to:

  • Make reports context-sensitive by narrowing the scope of a report to return more relevant data.

  • Retrieve and display a result set faster because only more relevant data is returned.

  • Allow the report to be filtered using the Advanced Find feature.

First of all you need to work with Filtered views, not the base tables and use the alias CRMAF_<FilteredView>

For example:

 select * from FilteredIncident as CRMAF_FilteredIncident 

Here pre-filtering will apply the filtering, you do not need to do anything else. But do not forget to not to use a separate parameter for the Id of corresponding Entity view.

Remarks: If you work on a Turkish language installed CRM and working with pre filtered SSRS reports your reports may not work properly and pre-filtering may not work as expected. If you face a situation like this please check your data source’s connection string and make sure that you do not use capitalized “I” letter. Instead of using “I” use “i”. This problem is that in Turkish language lower case of letter “I” ise “ı” and upper case of “i” is “İ“. If you change your connection string of your report your reports should work fine on your prod server too. I wrote this remark because I faced  this problem and you may have as well 🙂

For more information about pre-filtering you can visit the page in msdn.

I hope you find this tutorial useful.

See you 🙂

 

Leave a Reply

Your email address will not be published. Required fields are marked *