Tip #120: Use the filtered views

When you are writing reports for Microsoft Dynamics CRM, a common temptation is to use the base tables or non filtered views. it can be tempting to do this to make your report run faster. Before going this route, please consider what you are giving up:

  1. You have to grant the CRM service account a higher level of database access, which is not supported.
  2. You create a security back door–any report written against the base tables and views will not be constrained by CRM security. Users will have access to records outside of their normal record access levels.
  3. You give up the ability to use prefiltering.
  4. The report will not reflect user time zone or language settings.
  5. An upgrade may break your reports–just ask CRM 2011 users who had reports reading from the extension base tables.

Before opening up the barn door, please be sure you have exhausted all other optimization options:

  • Eliminate unnecessary joins
  • Use prefiltering, and set the filter to the desired data subset.
  • Move your query to a stored procedure*
  • Create a custom view*

* These options will require modification of database permissions to grant the CRM service account permission for the stored procedure or view; however, they are safer options than granting all users read access over all base tables or views.


One thought on “Tip #120: Use the filtered views

  1. […] seems to be the week of unsupported tips. I know, we said it before: Swim between the flags, and Use filtered views even if you use stored […]

Leave a Reply

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