Use SQL and Filtered Views to Retrieve Data for Reports

  • Article

[Applies to: Microsoft Dynamics CRM 2011]

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 installation. 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 sample SQL code returns all columns from the filtered view for an 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 that 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 Server Explorer to connect to the server where Microsoft Dynamics CRM is installed. Expand the SQL Server node and the target SQL Server instance node. The filtered views can be accessed under the Views node of the <organization_name>_MSCRM database. 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, click Settings, and then click Customizations. Next, click Customize the System, expand Entities, and double-click an entity name to view its fields (attributes) and relationships.

    Fields shows all the attributes that include the display name and a description for each attribute. To see the dependencies for the attributes, select an attribute, click More Actions, and then click Show Dependencies.

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

  • Use the 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. For more information about Microsoft Dynamics CRM business objects and their attributes, see Model Your Business Data.

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.

See Also

Reference

AttributeMetadata

Concepts

Filtered Views in Microsoft Dynamics CRM
Sample: Limit the Number of Items Displayed in a Chart
Sample: Display the Top X Values
Sample: Make a Report Context-Sensitive

Other Resources

Create Reports for Microsoft Dynamics CRM Using SQL Server Reporting Services

Microsoft Dynamics CRM 2011
Send comments about this topic to Microsoft.
© 2013 Microsoft Corporation. All rights reserved.