Monday, October 19, 2009

CRM Reporting Service PreFiltering

Today I was given task to enable PreFiltering on our internal CRM 4.0 using Reporting Services.
So I started by creating a very basic report in 'SQL Server BI Development Studio' on a random 'ContactList' table, just to see if I can enable preFiltering on it. After deployment to CRM I was unable to find 'Report Filtering Criteria' dialog box. Moreover I got the message when selecting "More Actions-->Edit Default Filter ..." from toolbar: 

"This report cannot have a default filter."

 After some googling I found out that preFiltering can only be enabled on FilteredEntityViews meaning in your CRM database under 'Views' you will find views pre-pended by 'Filtered' like FilteredEmail, FilteredLeads etc. For such views you can easily enable preFiltering. You just need to pre-pend 'CRMAF_' to the filtered view you are selecting and preFiltering would be available in CRM along with "Edit Default Filter" dialog box.
I think there is a way to created FilteredEntityViews in CRM but, unfortunately, that is out of the scope of this post.

Things I learnt:
1. PreFiltering cannot be applied to any table.
2. Use .
3. Make sure you include CRMAF_ alias name on all fields in query.
Helpful links for the day:
1. Context Sensitive Reporting
2. Inside Report Prefiltering
3. Reporting PreFilter

No comments:

Post a Comment