Dynamic CRM 2013 Learning Notes (15) Getting Started with reports, development tools, and considerations

This article is about the introduction to CRM 2013 report development, including the use of development tools, and the considerations that are different from common reporting service.

I. Introduction of CRM Report

There are two types of reports, sql-based reports and fetch-based reports, which differ as follows:

Regional	Sql-based Report	Fetch-based Report
Data Provider	The value of the <DataProvider> element is set to SQL. Example below:<dataprovider>sql</dataprovider>	The value of the <DataProvider> element in the report RDL file is set to Mscrmfetch. Examples are as follows: <dataprovider>mscrmfetch</d Taprovider>
report query	Reports in the RDL file <Query> under <CommandText> sub-element gets the data query statement is a SQL query statement. For example, the following SQL statement is the name column that queries all customers: <commandtext>select name from Filteredaccount;</commandtext>	In the report RDL file, under the <Query> element <CommandText> child element, the query statement that gets the data is a SQL query statement. For example, the following query statement is the name column for all customers: <commandtext>&lt;fetch version= "1.0" output-format= "Xml-platform" mapping= " Logical "&GT;     &lt;entity name= "account" &gt;         &lt;attribute name= "name"/&gt;     &lt;/entity&gt; &lt;/fetch&gt;</commandtext>
Deployment scope	Can only be deployed to on-premises Microsoft Dynamics CRM	Can deploy to Microsoft Dynamics CRM Online and on-premises Microsoft Dynamics CRM

Creating a sql-based report can only use filtered views in the system, and custom entities will automatically create and update their corresponding filtered View, and the general naming convention is filtered plus the schema name of the entity. For example, the client account entity corresponding to the filtered view is FilteredAccount. You can go directly to the view node of a database named <organization_name>_mscrm to see the definition, data, and so on. Filtered views automatically applies security, viewing data from this view to see only the data you can see. An explanation of filtered views is as follows:

1. All view column names are lowercase.

2. For the data type is the column of the option set, Filtered views will have two columns corresponding to the column, a column is the value of this option set, and a column is the display text of this option set. For example, lead leads this entity's attribute Leadsource data type is an option set, Filtered views will have Leadsource column values for the Mayors itemsets, leadsourcename column display text for Mayors itemsets.

3. For a column with a datetime data type. Filtered views will have two columns corresponding to the column, one column corresponds to the datetime that is appropriate for your time zone, and the other column corresponds to UTC time, which is usually added in UTC. For example, the record creation Time field CreatedOn is the time which is suitable for you to see, we default is the East eight district, but Createdonutc is the UTC time, is the East eight district time minus 8 hours.

ii. Development Tools

1. We used to develop reports for business Intelligence Development Studio, but SQL Server 2012 does not have this, instead SQL Server Data Tools, which is actually developed in VS:

2. Can I also use Microsoft? SQL Server? 2012 Report Builder to develop reports:

third, the matters needing attention

Here's a simple example of what to look for when developing a CRM report:

1. First create a data source:

2. Create a DataSet:

3. Precautions:

  1:select TOP 1
  2:         U.fullname,
  3:         mad.new_rate
  4:from    dbo.  Filteredcrmaf_ma
  5:         JOIN dbo.  Filterednew_material_apply_details mad on crmaf_ma.new_material_apply_orderid = Mad.new_material_apply_order
  6: Left         JOIN dbo.  Filteredsystemuser u on Crmaf_ma. CreatedBy = U.systemuserid
  

• The SQL statement here uses filtered view, so it's all starting with filtered, where the previous code is red.
• Where the main view alias is the beginning of the crmaf_, marked blue, this means that only the current data row
• Using the SQL statement above, you will find no data, because you do not specify which user, in order to test, we can before this SQL, add the following statement to specify which user:

  1:declare @binUserGuid VARBINARY (128)
  2:declare @userGuid uniqueidentifier
  3:select  @userGuid = (SELECT TOP 1
  4:                                 Systemuserid
  5:                       from      systemuser
  6:                       WHERE     ' crm\123456 '
  7:                     )
  
  

Pay attention to replace the above crm\123456 with the user name you want, and after the test is done, remember to remove it and post it to the CRM system.

Dynamic CRM 2013 Learning Notes Series Rollup

Dynamic CRM 2013 Learning Notes (15) Getting Started with reports, development tools, and considerations