Reporting and Analysis

Filtered Views

At this point, you've heard us warn you many times about not interacting with the SQL Server database directly. Now we'll tell you about the one (just one) time it's permitted to directly retrieve data from the SQL Server database.

If you were to browse the Microsoft CRM SQL database (with a tool such as SQL Server Enterprise Manager or SQL Query Analyzer), you might notice multiple data objects related to Accounts, including:

1. AccountBase table

2. AccountExtensionBase table

3. AccountLeads table
4. Account view

5. FilteredAccount view

When you want to write your own custom report about Accounts, you might wonder which of these database objects includes the information you're looking for. In addition to the Account entity, the Microsoft CRM database has a similar setup for all of its entity data. The CRM database stores all of the data in highly normalized and efficient layout, but that doesn't necessarily simplify your reporting and analysis needs.

Fortunately, instead of forcing you to spend hours investigating what types of entity data Microsoft CRM stores in these various database objects, Microsoft CRM greatly simplifies reporting and analysis by offering you filtered views. Filtered views perform the cumbersome task of denormalizing multiple tables and relationships into a streamlined view of entity and system data. In addition, filtered views respect the Microsoft CRM security settings so that users who query filtered views (or run reports that query filtered views) will see only the data that they're allowed to see. Also, filtered views translate lookup fields and picklist values, and they calculate all datetime values in both Coordinated Universal Time (UTC) and the user's localized value. For example, the createdon field will display the user's local time and the createdon field will display the field with the Coordinated Universal Time.

When you create reports that leverage integrated Windows authentication (such as SQL Server Reporting Services), the filtered views will automatically filter the data that the report displays to users based on their logged on credentials, their business units, and their security roles. Two different users viewing the same report might see entirely different results depending on the Microsoft CRM security settings. This feature will save you hours by trying to manually determine the security and data settings of each custom report.

Important Filtered views simplify the complex Microsoft CRM data model for use with reporting and analysis while maintaining user security and access to data. All of your custom reports should read data from the database filtered views exclusively. You should never write reports that query any other database table or view.

You can easily recognize filtered views in the database because their name always starts with the text "Filtered." Usually you can also determine which entity each filtered view relates to by simply looking at its name. Every entity has a filtered view, but Microsoft CRM also includes some filtered views that do not map directly to an entity. Table 7-2 shows some examples of filtered views.

Table 7-2: Sample Filtered View Names
Open table as spreadsheet

Filtered view name	Entity name	Entity schema name
FilteredAccount	Account	account
FilteredActivityPointer	Activity	activitypointer
FilteredIncident	Case	incident
FilteredAnnualFiscalCalendar	n/a	n/a
FilteredAccountLeads	n/a	n/a

More Info The Report Writer's Guide in the Microsoft CRM software development kit (SDK) lists all of the filtered views and the type of data stored in each view.

As you customize your system by adding custom attributes to the system entities, Microsoft CRM automatically updates the filtered views for you. It also creates entirely new filtered views for each custom entity that you add to your installation.

Another important note about filtered views is that Microsoft CRM automatically configures all of the filtered view permissions to allow only SELECT (read-only) operations against them. Even though it's technically possible for a database administrator to change the default permissions, you should never change the filtered view permissions to allow INSERT, DELETE, or UPDATE operations. Attempting to perform any non-SELECT operation against a filtered view might cause irrevocable damage to your Microsoft CRM database.