CRM 2015 – Why filtered views are useful

If you have been a CRM developer for a length of time then you will certainly come across filtered views and they are useful and definitely something you should learn more about, particularly if you are going to be writing reports or accessing the CRM database directly.

The CRM database can be viewed and the data used but never changed directly – Hosk

This quote means you should never change data directly on the SQL table, below are a few key reasons why
  • Direct changes to the SQL table are unsupported, this will result in Microsoft offering no or limited support if your CRM installation goes wrong.  You can read more about why you should not create unsupported changes here
  • When you use the CRM SDK to change a value it could update other fields and values, direct changes to the SQL will not
  • There is no validation on the SQL tables,  you could easily put in an incorrect value
  • If you write SQL stored procedure directly against the CRM database tables this could stop working when a rollup or service patch is applied to your CRM organisation

You should be of the opinion directly changing values on the Microsoft CRM database is a big no no

Best practice is to avoid accessing fields directly from CRM tables, the database structure, tables and fields could all change in future releases, rollups and service packs.

So what is the alternative to accessing CRM data using SQL if you shouldn’t access the data directly?

Microsoft have created a filtered view for each database table and Microsoft will make sure these work even after future releases, rollups and services packs.

Start with the CRM database

The database has an odd status for CRM developers, we all know it’s there and in many ways Microsoft Dynamics CRM is at its core just a database for customers to store and use their precious data.
CRM developers know there is an SQL server database there and you may have installed CRM or written some SSRS reports using the database.
CRM developers are taught all interaction with the CRM data should be done using the CRM SDK or OData/Rest queries if using Javascript.
Basically no direct access to the database, this is highlight if you look at this great article on MSDN – Microsoft Dynamics CRM Extensibility Architecture, below I have taken the diagram.
Microsoft avoid tight coupling and unmanaged complexity (one of the deadly sins of complex code – read more The problems with complex code and complex CRM Customizations) by limiting access to the database to
  • Data Access Platform
  • Filtered SQL Views
CRM online doesn’t allow any access to filtered views, the reason (obvious when you think about it) is because you do not have access to the CRM database, which is quite a limitation to the reporting options for CRM online deployments because they will all have to be fetchXML.
In the (good) old days (warning Hosk you are starting to sound like an OLD CRM man) of CRM 2011 and versions of CRM below the CRM database use to have a base and an extended table for entity.  The base table had the out of the box fields and the extended table had all the new custom fields added.
so for the account entity
In CRM 2013 (optional)/CRM 2015 (mandatory) the two database table structure was changed and merged into one database table.  I’m not sure what happens when you upgrade a database but I’m guessing the scripts in the background might merge the database tables, which is another warning why you shouldn’t write SQL directly against the database tables.

What are filtered views?

Filtered views have been around since the days of CRM 3, I’m happy to say I have never had to use a version of CRM below version 4.  I found this article written in 2006
The best and first place you should start looking for information is the CRM SDK (the Truth). The link below shows the names of the filtered views in CRM.
Entity schema name Entity type code Report view name Description
Account 1 FilteredAccount Business that represents a customer or potential customer. The company that is billed in business transactions.
AccountLeads 16 FilteredAccountLeads Represents the intersect table for the accountleads_associationrelationship.
  • Filtered views have the same name as the CRM table but with the word Filtered prefixing it.  Not all Entities have filtered views because some entities are system entities
  • Filtered views will be automatically created for any custom entities you create.
  • In CRM 2011 the filtered view contained all the fields in the base and the extended table.
  • CRM filtered views exist for individual activity types e.g. (email, task, letter, etc)

Why are Filtered views cool

You are probably wondering what is so special about filtered views, it sounds just like a view of the table.
Microsoft has created a create article called Use SQL and filtered views to retrieve data for reports, this has many of the benefits of Filtered views listed below

The benefits of Filtered views are

  • Filtered views won’t break after patches, services releases and maybe upgrades
  • Security is embedded in the view
  • All the column names are in lower case
  • Filtered views are automatically created and updated for custom entities
  • Datetime fields are held in datetime and UTC
  • Drop down values are held in filtered views

Filtered views won’t break after patches, services releases and maybe upgrades

Microsoft will support filtered views in future versions, rollups, service patches, upgrades.   If you write reports against a filtered view it won’t suddenly break when a rollup or service patch is applied, the report should work if you upgrade the version of CRM (that is a big if).  Microsoft can change CRM tables but when they do they update the filtered view to work with the changes.

Security is embedded in the view

Filtered views are created for CRM developers to use in reports.  Filtered views use the Microsoft Dynamics CRM security model, so data shown in a report using a filtered view would only contain the data the users can view in CRM.

Filtered views take uses security levels e.g. organisation, business unit, owner and field level and these only show the levels each individual user can see.

If you created a view created directly on the Microsoft Dynamics CRM tables then it would show all the data to all users.

Adam summarises this excellently in his comment

Filtered views enforce  security

All the column names are in lower case

All the column names in a filtered view are in lower case, so you do not have any problems with case sensitivity

Filtered views are automatically created and updated for custom entities

Microsoft is busy in the background and when you create a new entity or add fields to a custom entity or System entity, Microsoft automatically creates a new filtered view for custom entities and adds the new fields to existing filtered views.

Datetime fields are held in datetime and UTC

Filtered views have two datetime fields for every date time field you add.  One DateTime field and one UTC DateTime field.

The DateTime field holds the date time for the users time zone

DateTime UTC holds the time in UTC

Drop down values are held in filtered views

Option sets have two fields in a filtered view, one holds the value the other holds the string representation

One field holds the string value of the option set

Another value holds the number

If you had an option set field accountrating with an option set value set of Gold – 1.

accountrating = 1

accountratingname = “Gold”

Primary Key Field

The primary key field for each entity has  a field in the filtered view.
e.g.  contact filtered view would have the field contactid
My current dealing/problem
Filtered view has more fields it has a date field and date field UTC


Thanks to CRM MVP Adam Vero for pointing this benefit out, he has an excellent blog which  you should read here.
CRM has added a few neat tricks and columns to the filtered view which makes them more useful than a view which will be supported in future upgrades/releases of CRM.
Mentioned above they included UTC field dates so we know they are already adding value to filtered view by having the same data but already converted, which saves the developer from doing this.
Adam mentions the filtered view has denormalization the data in the view but what does this mean, here is a wiki definition of denormalization
In computing, denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data.[1][2] In some cases, denormalization is a means of addressing performance or scalability in relational database software.
This means for lookup fields the filtered view contains two fields
  • Guid
  • Name
In most reports you will often want to display the name or the title of the related entity, this usually means you have to retrieve the extra data but in the filtered views they add the name field.

Problems with Filtered Views

One of the main problems with filtered views can be performance and they have been known to run slowly.
One of the reasons for the slow speed of filtered views is you cannot apply indexes to the tables. Another reason for poor performance is the filter view connects a number of tables together.
The security model can have significant impact on general CRM peformance and the filtered views performance will be slower if you have a large and/or complex security model.  A complex security model could include
  • Lots of Teams in different BU’s
  • Lots of sharing
You will quickly know if speed is going to be an issue with the report but the security advantages are definitely worth using filtered views to initially create the report.
If you cannot get your reports with filtered views working this blog has a potential solution
 This forum discusses how to speed up filtered views (there isn’t really anyway)


Filtered views are a great way to create reports in a supported fashion which won’t break in future releases.  The advantage of the security being applied to the report means your reports won’t show data the user isn’t mean to see.
The major downside is a common problem to all reports e.g. SPEED.  It’s common for reports to try aggregate lots of data and these reports can run slowly when using Filtered views.
The UTC date can be very useful and save you formatting the date
 My advice is to try the filtered view first because the advantages are great but if the report is too slow you have have to consider your options.

16 thoughts on “CRM 2015 – Why filtered views are useful

  1. Alexandre Kohn March 13, 2015 / 11:38 am

    Great post. A thank you from Brazil.


  2. fuat tatar March 13, 2015 / 1:10 pm

    Great article. Every crm developer should read. Thanks.


  3. ukcrmguru March 13, 2015 / 2:30 pm

    Filtered views enforce security. If your security model is huge and complicated (and especially if it involves lots of teams in different BUs, and/or lots of sharing), then this will be slower. So make sure to get your security model as streamlined as possible, for performance benefits in all areas.

    You forgot to mention that filtered views are very denormalised, so for example in the FilteredAccounts view, the Primary Contact uses two columns – one for the GUID and one for the name. If all you need is the name, you don’t need an extra join (although of course that join has already been done in the background – this is not a performance benefit, just a simplicity of querying/reporting one). Likewise option sets, bit fields show values and labels.

    This denormalisation involves lots of joins, especially on “big” entities such as Account and Contact with lots of lookups, addresses and so on. So if you are not using those lookup fields, but just want data from the base or extension tables, it can be tempting to ditch the overhead of those joins and go to the tables rather than the views. But that is probably a bad idea. Better ideas include mirroring data to a separate DB to do complex reporting without impacting the transactional DB.

    The base and extension tables are merged if you do an in-place upgrade to CRM 2013, or when you do a migration and import a CRM 2011 database to 2013. If you do a migration you can set a registry key to defer this table merging, and then do them one (or several) entities at a time until they are all done.
    When you upgrade to 2015, the tables are merged if they have not been done already – so do them already in 2013 to take out some risk, and speed up the process.
    The filteredviews are unaffected by this change. Any reports using the extension tables directly will break when they are merged.
    Merging the tables gives some significant performance gains (I am told, and it makes sense that it reduces numbers of joins).


  4. Hosk March 13, 2015 / 3:42 pm

    Thanks for you detailed reply, I hadn’t fully considered the effects of the security model but I did have an inkling this was the main cause of any speed issues with Filtered Views.

    I mentioned the two tables because filtered views use to merge them. It also shows the danger of writing reports directly against the CRM tables because they can change or disappear!

    Thanks for clearing up the merging of tables in CRM 2015

    Your comment reminds me you should definitely write more blog posts, you have added a lot detail and knowledge to this blog post


  5. Sanjaya Prakash Pradhan July 18, 2015 / 7:12 pm

    The line is wrong :You can read more about why you should create unsupported changes here.

    It should be: You can read more about why you should not create unsupported changes here


    • Hosk July 20, 2015 / 12:50 pm

      thanks for spotting that, it has been changed


  6. nb0512 December 14, 2015 / 1:01 pm

    RE: “In CRM 2013 (optional)/CRM 2015 (mandatory) the two database table structure was changed and merged into one database table.”
    In an excel based report I was using some VBA code to retrieve attributes from a few Filtered tables in CRM4.0. This still works in our CRM2015, however, the custom attributes are not returned, only the built-in attributes. Do you have any idea why?


    • Hosk December 14, 2015 / 2:36 pm

      The filtered views should be the same because its a view maintained by Microsoft, changes in the underlying database shouldn’t matter.

      Filtered views do use the security of the user so check the privileges for the user are ok


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s