Best Practice for Building CRM Queries in an SSRS Custom Report

When writing a custom SSRS report for CRM, one of the greatest features is the ability to include pre-filtering in your reports.  By adding the CRMAF_ prefix to the alias assigned to the table you are querying, it will pass your selected CRM records as the context for your report. E.g.:

SELECT * FROM FilteredAccount AS CRMAF_Account

Using the pre-filter though, requires you to query from the filtered view. Now this is helpful in that in using the filtered view, you are getting the benefits which filtered views provide, such as security enforcement and visible picklist option labels. The picklist labels are provided in the view, as well as the value, since the view contains all fields from the entity base and extended tables. The security enforcement uses the Dynamics Security model to restrict access to records based on the security roles and field security profiles which a User has in CRM. This is great in that users who shouldn’t have access to a record cannot stumble across it through a report. Having the additional security in the report can become a bit cumbersome though, when multiple views are joined together within your query. Each view queried will be run against the security model to verify security role permissions, and will run the field security read, create, edit permissions for each field on the entity. This can end up taking a lot of processing time and slowing down the execution of the report.

As a best practice when generating your query, build it out referencing the entity base tables for the additional joined tables you need to pull fields from when possible. You will still need to use the filtered view for your primary entity, as it is required for the pre-filtering and also for any entities where you are pulling the picklist label to be returned, as the base table will not provide this. Another option would be to build a data set using just the pre-filter and filtered view to gather the record ID. From here, you can pass this along as a parameter to other data sets where you can build your query without field security or security roles taking away processing time.

I don’t use these myself all the time based on the requirements of the report, but I try to whenever possible to make sure my report can run as efficiently as I can make it.