Filtered Views in CRM 3.0

Did you know that apart from FetchXML (v 1.2 carry on) and Query Expression (v 3.0 faster, powerful approach) we also have FilteredViews in CRM 3.0 by which we can query the CRM database.

FetchXML and QueryExpression give you information from CRM database via CRM web service but FilteredViews allow you to retrieve data from CRM directly hitting the SQL. Microsoft does not recommend you to access the CRM - SQL database directly for any manipulations but if you want to just retrieve(read) the records from CRM entities(built in + custom) it's absolutely fine to do so using Filtered Views.

One of the reason we would like to use FilteredViews is because we needed information from multiple entities. You cannot use QueryExpression for that in CRM 3.0 but sure can use FetchXML.
See my previous post

The problem with FetchXML is the flat structure it returns (as i mentioned in my prev posts). With FilteredViews you have the liberty to work the way you did in SQL server. You get the power of SQL DML usage and ADO.net (can also use features like GroupBy, multiple joins easily etc.)

However do NOT attempt to insert/update records directly into CRM database.

Here's an explanation on FilteredViews -

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.

Fortunately, instead of forcing you to spend hours investigating what types of entity data CRM stores and what relationship exists between these objects, CRM has simplified reporting for us by offering "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 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.

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.

These objects (as listed above) are also created for all custom entities that we create in CRM.

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.

HTP, Cheers - Dipesh