Use SQL and filtered views to retrieve data for reports

  • Article

 

Applies To: Dynamics CRM 2015

Microsoft Dynamics CRM data and metadata are stored in a Microsoft SQL Server database named <organization_name>_MSCRM on the server that is running Microsoft SQL Server in the Microsoft Dynamics CRM (on-premises) deployment. SQL-based reports in Microsoft Dynamics CRM use the filtered views provided for each entity to retrieve data for the reports. Filtered views are fully compliant with the Microsoft Dynamics CRM security model. When you run a report that obtains data from filtered views, the Microsoft Dynamics CRM security role determines what data you can view in the report. Data in filtered views is restricted at these levels: the organization, the business unit, the owner, and at the field level.

Filtered views exist for all Microsoft Dynamics CRM entities, including custom entities. Your custom SQL-based reports cannot read data directly from the Microsoft Dynamics CRM database tables. Instead, you must use the filtered views to retrieve data for your custom SQL-based reports.

The following SQL query returns all columns from the filtered view for the Account entity:

SELECT * FROM dbo.FilteredAccount

Filtered views also provide a way to pull Microsoft Dynamics CRM report data into Microsoft Office applications, such as Microsoft Office Excel and Microsoft Access. For a complete listing of all the standard filtered views organized by product area, see Filtered views in Microsoft Dynamics CRM.

In this topic

Custom and customized entities

Entity schemas for creating custom SQL-based reports

Naming conventions in the Microsoft Dynamics CRM database

Custom and customized entities

When you create a new custom entity in the Microsoft Dynamics CRM database, a new filtered view for the entity is automatically created. Further, if you add or change an attribute in a custom entity or customizable system entity, the change is automatically included in the associated filtered view.

Entity schemas for creating custom SQL-based reports

To find schema information about any filtered view, entity, or attribute in the Microsoft Dynamics CRM database, use one of the following methods:

  • In Microsoft Visual Studio, use SQL Server Object Explorer to connect to the SQL Server where the Microsoft Dynamics CRM organization database is located. Expand the Databases node and then expand the <organization_name>_MSCRM database. The filtered views, such as FilteredContact, can be accessed under the Views node. Right-clicking the filtered view displays a shortcut menu that enables you to explore the design of the filtered view and the data it returns.

  • Log on to Microsoft Dynamics CRM Web application by using an account that has the System Administrator security role. In Microsoft Dynamics CRM, choose Settings, and then select Customizations. Next, choose Customize the System, expand Entities, and double-click an entity name to view its fields (attributes) and relationships.

    • Choose Fields to show all the attributes that include the display name and a description for each attribute. To see the dependencies for the attributes, select an attribute, choose More Actions, and then select Show Dependencies.

    • Choose 1:N Relationships, N:1 Relationships, and N:N Relationships to show the entities that have a relationship with the current entity, and the attributes that are used to define the relationships.

  • Use Microsoft SQL Server Management Studio to view the database contents directly.

All the methods that are listed here let you access schema information for custom or customized entities and attributes.

Naming conventions in the Microsoft Dynamics CRM database

The following are the attribute naming conventions in the Microsoft Dynamics CRM database.

Attributes obtained through filtered views

Although field names in Microsoft Dynamics CRM are case-sensitive and in mixed case, the attribute names obtained through filtered views are in lowercase.

All drop-down lists (option sets) have two associated fields for every string in the list. For each string, there is a value (code) field and a label (name) field, such as, leadsource and leadsourcename. For example, the filtered view for Leads returns two fields related to the LeadSource attribute of type Picklist: LeadSource = 1 and LeadSourceName =“Advertisement”. Reports display the label field and use the value field for numeric comparisons.

DateTime attributes

The DateTime attributes are represented by two fields in the filtered view: DateTime and UTC DateTime. The first field contains the date and time value for the appropriate time zone and the second field contains the date and time value in Coordinated Universal Time (UTC).

Entity field

For an entity table in the database, the primary key field is in the name format EntityId, for example, AccountId. Each EntityId field has an associated field that contains the value that should be displayed in reports. For example, for the account entity, it is the Name field that contains the name of the account.

In this section

Filtered views in Microsoft Dynamics CRM

Improve report performance by using filters

See Also

Working with SQL-based reports (CRM on-premises only)
Format report content

© 2016 Microsoft Corporation. All rights reserved. Copyright