Use SQL and filtered views to retrieve data for reports

 

Updated: November 28, 2016

Applies To: Dynamics 365 (on-premises), Dynamics CRM 2016

Microsoft Dynamics 365 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 Dynamics 365 (on-premises) deployment. SQL-based reports in Microsoft Dynamics 365 use the filtered views provided for each entity to retrieve data for the reports. Filtered views are fully compliant with the Microsoft Dynamics 365 security model. When you run a report that obtains data from filtered views, the Microsoft Dynamics 365 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 365 entities, including custom entities. Your custom SQL-based reports cannot read data directly from the Microsoft Dynamics 365 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 365 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 365.

When you create a new custom entity in the Microsoft Dynamics 365 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.

To find schema information about any filtered view, entity, or attribute in the Microsoft Dynamics 365 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 365 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 365 Web application by using an account that has the System Administrator security role. In Microsoft Dynamics 365, 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.

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

Although field names in Microsoft Dynamics 365 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.

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).

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.

© 2016 Microsoft. All rights reserved. Copyright

Community Additions

ADD
Show: