Dynamics 365 reproting note, Study Guides, Projects, Research of Customer Relationship Management (CRM)

Download Dynamics 365 reproting note and more Study Guides, Projects, Research Customer Relationship Management (CRM) in PDF only on Docsity!

Reporting Guide for Dynamics 365

Version: 8.

View the latest version of this document online.

Contents

• Reporting Guide for Dynamics
• Report & Analytics with Dynamics
  • In This Section
  • Related Sections
• Get started writing reports
  • In This Section
  • Related Sections
• Report writing environment using SQL Server Data Tools
  • Required tools
  • Required privileges
  • Report development process
  • See Also
• Create a new report using SQL Server Data Tools
  • In This Topic
  • Create a custom Fetch-based report (Dynamics 365 (online) and Dynamics 365 on-premises)
  • Create a custom SQL-based report (Dynamics 365 on-premises only)
  • See Also
• Working with Microsoft Dynamics 365 reports
  • In This Section
  • See Also
• Format report content
  • Use formatting values in reports...........................................................................................................
  • Date and time values
  • Number values
  • Base currency value
  • Transaction currency
  • See Also
• Add report navigation
  • Dynamic drill through to Microsoft Dynamics
  • See Also
• Categorize and display reports in different languages
  • See Also
• Use parameters in reports
  • In This Topic
  • Adding parameters
  • Hidden parameters
  • See Also
• Working with SQL-based reports (Dynamics 365 on-premises only)
  • In This Section
  • See Also
• Modify an existing SQL-based report using SQL Server Data Tools
  • In this topic
  • Work with complex SQL queries
  • Modify an RDL file
  • Add elements by using the Report Designer
  • Test the report
  • See Also
• Use SQL and filtered views to retrieve data for reports
  • In this topic
  • Custom and customized entities
  • Entity schemas for creating custom SQL-based reports
  • Naming conventions in the Microsoft Dynamics 365 database
  • In this section
  • See Also
• Filtered views in Microsoft Dynamics
  • See Also
• Test and troubleshoot reports
  • Test a report
  • Suggestions for testing a report
  • Report drill-through fails in Visual Studio Report Viewer
  • See Also
• Publish reports
  • Publish a report in Microsoft Dynamics 365 by using the web application
  • Determine where the report will appear
  • Define a default filter for the report
  • See Also
• Report considerations and best practices
  • In This Section
  • See Also
• Best practices for reports
  • General best practices
  • SQL-based reports
  • See Also
• Improve performance of reports
  • General
  • SQL-based Reports
  • See Also
• Improve report performance by using filters
  • In this topic
  • Enabling data pre-filtering in Fetch-based reports
  • Enabling data pre-filtering in SQL-based reports (Dynamics 365 on-premises only)
  • Passing filters in the filter summary
  • Default filters.........................................................................................................................................
  • See Also
• Microsoft Dynamics 365 (online) reporting considerations
  • In this topic
  • Tips and solutions for reporting
  • Third-party Microsoft Dynamics 365 adapters for SSIS
  • ETL tools
  • See Also
• RDL sandboxing for Microsoft Dynamics 365 (online)
  • In this topic
  • Limits of the array result length and string result length
  • Allowed types and denied members
  • Common denied members
  • See Also
• Example reports
  • Create a report using an example
  • In This Section
  • See Also
• Example: Limit the number of items displayed in a chart
  • Example................................................................................................................................................
  • Example................................................................................................................................................
  • See Also
• Example: Display the top X values
  • Example................................................................................................................................................
  • See Also
• Example: Make a report context-sensitive...............................................................................................
  • Create and configure a context-sensitive report
  • See Also
• Copy reports between Microsoft Dynamics 365 (on-premises) deployments
  • In this topic
  • Fix the type code for a custom entity used in a report
  • Copy a report between deployments
  • See Also
• Customize Microsoft Dynamics 365 Power BI content packs
  • In this topic
  • Do this before you customize a Dynamics 365 content pack for Power BI reports
  • Customize a Dynamics 365 content pack
  • Add a custom field to a report for the Account entity
  • Add a custom option set field to a report
  • Increase the number of rows queried
  • Publish your report to the Power BI service
  • See Also
• Copyright..................................................................................................................................................

report snapshots at certain intervals. This feature is currently available only with Dynamics 365 (on- premises). For more information about creating and editing reports in Microsoft Dynamics 365, see Customize and organize reports.

 The reporting infrastructure in Microsoft Dynamics 365 (online) has RDL sandboxing enabled.

Therefore, custom code in report definitions will not work. RDL sandboxing for Microsoft Dynamics 365 (online)

In This Section

Get started writing reports

Working with Microsoft Dynamics 365 reports

Working with SQL-based reports (Dynamics 365 on-premises only)

Test and troubleshoot reports

Publish reports

Report considerations and best practices

Example reports

Copy reports between Microsoft Dynamics 365 (on-premises) deployments

Customize Microsoft Dynamics 365 Power BI content packs

Related Sections

Referenced topic 'd0d49a86-6297-4431-8b30-1f477bca2bad' is only available online.

Referenced topic '9f201f30-245a-458e-b15f-961a9d049ea7' is only available online.

Referenced topic '633e9a2d-cba1-4700-ba18-01487767109c' is only available online.

Referenced topic '605bb886-116a-4275-83fe-e9fdc83d2f2f' is only available online.

© 2016 Microsoft. All rights reserved. Copyright

Get started writing reports

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

Microsoft Dynamics 365 uses Microsoft SQL Server Reporting Services report definition language (RDL) reports to query Dynamics 365 data and return refined results back to the report user. For more information about RDL, see TechNet: Report Definition Language (SSRS).

To create or modify existing RDL reports that can be used with Microsoft Dynamics 365, use either T- SQL or FetchXML, which is then converted to RDL by using report authoring tools. The following table lists the differences between SQL-based and Fetch-based reports in Microsoft Dynamics 365.

Area SQL-based report Fetch-based report

Supported Microsoft

Dynamics 365 (on-premises) Microsoft Dynamics 365 (online) and Dynamics 365 (on-premises)

Area SQL-based report Fetch-based report

Dynamics 365 Versions

Report Query Language

Uses Transact-SQL (T-SQL)—a set of programming extensions that provide comprehensive transaction control by using Structured Query Language (SQL). More information: TechNet: Transact-SQL Reference (Database Engine)

Uses FetchXML—an extensible markup language (XML) designed specifically for Microsoft Dynamics 365 queries. More information: MSDN: FetchXML schema

Requires Report Authoring Extension?

No Yes

.RDL file Data Provider

The element value in the .rdl file is set to SQL. For example:

SQL

The element value in the .rdl file is set to MSCRMFETCH. For example:

MSCRMFETCH

.RDL file Report Query

The query specified for retrieving data is in the sub- element under the element in the report definition (.rdl file) and is a SQL query. For example, the query for retrieving all account names for a SQL-based report will be:

SELECT name FROM FilteredAccount;

The query specified for retrieving data is in the sub-element under the element in the report definition (.rdl file) and is a FetchXML query. For example, the query for retrieving all account names for a Fetch-based report will be:

<fetch version="1.0" output-format="xml-platform" mapping="logical"> <entity name="account"> <attribute name="name" /> </entity></fetch>

If you want, you can use a third-party tool, SQL2FetchXML, to convert your SQL scripts to FetchXML, and then use the FetchXML query in your Fetch-based reports. More information: SQL2FetchXML Help

In This Section

This section covers what you need to create a new Microsoft Dynamics 365 report.

Report writing environment using SQL Server Data Tools

Create a new report using SQL Server Data Tools

Related Sections

Report & Analytics with Dynamics 365

reports. Microsoft Dynamics 365 Report Authoring Extension must be installed on the computer where Microsoft Visual Studio and SQL Server Data Tools are installed. Download: CRM 2016 Report Authoring Extension. For installation instructions, see Install Microsoft Dynamics CRM Report Authoring Extension.

Note

Microsoft Dynamics 365 Report Authoring Extension is available only in a 32-bit version.

Required privileges

To deploy custom reports to Microsoft Dynamics 365, you must have a Microsoft Dynamics 365 account and a security role assigned to you that includes the PublishReport privilege. By default, the System Customizer and System Administrator security roles include these privileges.

Report development process

The following lists the steps for developing custom Microsoft Dynamics 365 reports. You may have to repeat some steps while you develop a report:

1. Develop a report concept or specification based on what business information is to be displayed.
2. Decide on the type of report you want to create: Fetch-based or SQL-based. Microsoft Dynamics

365 (online) users can only create custom Fetch-based reports. More information: Report & Analytics with Dynamics 365

3. Create a custom report or use an existing report to modify using SQL Server Data Tools in

Microsoft Visual Studio.

 Create a new (custom) report. More information: Create a new report using SQL Server Data Tools

 Download an existing Microsoft Dynamics 365 report definition language (.rdl) file. You can do this in the Microsoft Dynamics 365 web application. More information: Modify an RDL file Alternatively, for Dynamics 365 (on-premises), reports are located in the C:\Program Files\Microsoft Dynamics 365 Reporting Extensions\LangPacks<lcid>\Reports\MSCRM\ folder where Microsoft Dynamics 365 Reporting Extensions is installed. More information: Modify an existing SQL-based report using SQL Server Data Tools

4. Create basic report parameters. More information: Use parameters in reports
5. Specify datasets and filtering criteria for retrieving data:

 For SQL-based reports, create datasets that contain Microsoft Dynamics 365 data obtained from the filtered views. More information: Filtered views in Microsoft Dynamics 365

 Enable pre-filtering on the primary entities. More information: Improve report performance by using filters

6. Define the basic layout of the report, including headers and footers.
7. Add report items as required based on the report specification. More information: Add report

navigation

8. Preview the report in Microsoft Visual Studio, and resolve any errors. More information: Test and

troubleshoot reports

9. Deploy the report to the reporting server by using Microsoft Dynamics 365. More information:

Publish reports

10. Run the deployed report to verify.

See Also

Use SQL and filtered views to retrieve data for reports Report & Analytics with Dynamics 365 Create a new report using SQL Server Data Tools Getting Started with Custom Reports in the Cloud Business Intelligence Development Studio Report Designer and Business Intelligence Development Studio

© 2016 Microsoft. All rights reserved. Copyright

Create a new report using SQL Server Data

Tools

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

SQL Server Data Tools is a report authoring environment that lets you create or edit Microsoft SQL Server Reporting Services reports in Microsoft Visual Studio. The end result is a report definition .rdl file that contains the report definition that you can publish in Microsoft Dynamics 365 to view reports.

You can also author reports by using a common text editor. To reduce the effort to create a custom report, modify an existing .rdl file that provides most of the desired functionality. For more information about the format of the XML elements in an .rdl file, see Report Definition Language Reference. The modified report XML can be verified by using the specified XML schema. Reporting Services will also verify the report definition and reject a report if the definition is invalid when you try to upload the report in Microsoft Dynamics 365.

Note

If the .rdl file contains a FetchXML query, the query in the RDL is validated by Microsoft Dynamics 365 Report Authoring Extension, which internally validates it against the FetchXML schema. For more information, see MSDN: Fetch XML Schema.

In This Topic

Create a custom Fetch-based report (Dynamics 365 (online) and Dynamics 365 on-premises)

Create a custom SQL-based report (Dynamics 365 on-premises only)

More information: MSDN: Build queries with FetchXML Click Next.

7. Verify the fields that will be included in the report, and then click Next.
8. Select a style to apply to the report, and then click Next.
9. Verify the fields that will be included in the report and enter a name for the report, such as Accounts

With More Than 5,000 Employees. Click Finish.

10. If you’d like to see how the report will appear when it’s run, click the Preview tab.

This generates an .rdl file with the specified report name. You can use this file to publish your custom report in Microsoft Dynamics 365 (online) using the Report Wizard. More information: Publish reports

Create a custom SQL-based report (Dynamics 365

on-premises only)

To create a custom SQL-based report using SQL Server Data Tools:

1. Make sure that you have a supported version of Microsoft Visual Studio,SQL Server Data Tools,

and the necessary privileges. More information: Report writing environment using SQL Server Data Tools

2. Open Microsoft Visual Studio, and then create a report server project.
3. In Solution Explorer, right-click the Reports folder, and then click Add New Report.
4. Click Next.
5. On the Select the Data Source page, click New data source , and then specify the following

details:

 Name : Type a name for the data source.

 Type : Select Microsoft SQL Server.

 Connection String : Specify the connection string to connect to the instance of the Microsoft SQL Server database. To build the connection string, and click Edit to type the SQL Server name and organizationName _MSCRM database. To supply credentials, select Credentials. Click Next.

6. On the Design the Query page, type the SQL query to use for the report, and then click Next. For

example, to create a report that displays all accounts with 5,000 or more employees, where OrgName _MSCRM is the name of the organization database, use this query.

Use OrgName_MSCRM Select Name, AccountNumber, NumberofEmployees from AccountBase where NumberofEmployees > 5000 order by NumberofEmployees desc Or you can design a query by choosing Query Builder.

7. On the Select the Report Type page, select a Tabular report or a Matrix report, and then click

Next.

8. Verify the fields that will be included in the report, and thenclick Next.
9. Select a style to apply to the report, and then click Next.
10. Verify the fields that will be included in the report and enter a name for the report, such as Accounts

With More Than 5,000 Employees. Click Finish.

11. If you’d like to see how the report will appear when it’s run, click the Preview tab.

This generates an .rdl file with the specified report name. You can use the .rdl file to publish your custom report in Microsoft Dynamics 365. More information: Publish reports

For more information about how to create a report by using the Report Designer, see Create a Basic Table Report (SSRS Tutorial).

See Also

Report writing environment using SQL Server Data Tools Modify an existing SQL-based report using SQL Server Data Tools Blog: Getting Started With Custom Reports In The Cloud

© 2016 Microsoft. All rights reserved. Copyright

Working with Microsoft Dynamics 365 reports

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

This section describes the different features that are available to determine how reports display in Microsoft Dynamics 365.

In This Section

Format report content

Add report navigation

Categorize and display reports in different languages

Use parameters in reports

See Also

Report & Analytics with Dynamics 365 Working with SQL-based reports (Dynamics 365 on-premises only)

Number values

The fn_GetFormatStrings function returns the number of format strings with precision values between 0 and 5. You can specify a precision by putting a decimal value between the underscore characters in the field name.

Formatting string Report item Format property value

Integer =First(Fields!NumberFormat_0_Precision.Value, "DSNumandCurrency")

Decimal with 2-decimal points precision =First(Fields!NumberFormat_2_Precision.Value, "DSNumandCurrency")

Base currency value

The fn_GetFormatStrings function returns base currency format strings with precision values between 0 and 5. You can specify a precision by putting a decimal value between the underscore characters in the field name.

Formatting string Report item Format property value

Base currency with 2-decimal points precision =First(Fields!CurrencyFormat_2_Precision.Value, "DSNumandCurrency")

Transaction currency

When you create a report on an entity with the transaction currency information, you can retrieve the transaction currency format string from the crm_moneyformatstring column of the filtered view for an entity. After a column has been added to the dataset, you can reference the column on the Format property of the report. For information about how to add columns to a dataset, see MSDN: How to: Add, Edit, or Delete a Field in the Report Data Pane. For example, to retrieve a price unit and the transaction currency formatting information from the quote detail filtered view, use the following SELECT statement.

SELECT priceperunit, crm_moneyformatstring FROM FilteredQuoteDetail

You can reference a new column in the Format property of the report item as follows:

=Fields.crm_moneyformatstring.Value

See Also

Working with SQL-based reports (Dynamics 365 on-premises only) Add report navigation

© 2016 Microsoft. All rights reserved. Copyright

Add report navigation

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

Report navigation enables a dynamic and interactive reporting experience. By using various types of actions, reports let the user navigate to detailed reports, Microsoft Dynamics 365 records, or other websites.

Note

For more information about report navigation, see Drillthrough, Drilldown, Subreports, and Nested Data Regions (Report Builder and SSRS) in the Microsoft SQL Server documentation. This topic describes report navigation that is specific to Microsoft Dynamics 365 reports.

Dynamic drill through to Microsoft Dynamics 365

You can drill through a report to navigate to a Microsoft Dynamics 365 web form. A drill-through report is implemented in the following steps:

1. An image or value item (such as a text box) is added to a report. The Value property of this item

contains code that builds a URL by using the base address of Microsoft Dynamics 365 plus parameters that refer to a specific record.

2. When the user selects the report item, a new browser window is opened by using the constructed

URL passed as the target web address.

3. Microsoft Dynamics 365 loads the information for the specified entity into a web form that is

displayed in the browser window.

To set up a drill-through report in Microsoft Dynamics 365

1. Create a hidden parameter of type string in the report that has the name CRM_URL. For more

information about adding parameters, see Use parameters in reports. When the report is run, this parameter is automatically set to the web address of Microsoft Dynamics 365.

2. Add a report item, such as a Textbox.
3. Right-click the drill-through report item and select Properties from the shortcut menu.
4. Click Advanced.
5. In the Navigation tab, click Jump to URL and enter an expression in the following format:

= Parameters!CRM_URL.Value & "?ID={"& GUID &"}&LogicalName=entity logical name" The entity GUID and entity logical name have to be added to the URL to be able to drill through. For example:

= Parameters!CRM_URL.Value & "?ID={"&Fields!Opportunityid.Value.ToString()&"}&LogicalName=opportunity"

6. Click OK.

See Also

Report & Analytics with Dynamics 365 Use parameters in reports

© 2016 Microsoft. All rights reserved. Copyright

Use parameters in reports

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

You use parameters in reports to control the data retrieved by prompting the user for a value or a set of values when the user runs the report. The dataset query retrieves only the data that is requested by the user. You can also add hidden and special parameters in the reports that do not prompt the user for input, but can be used for operations such as data filtering and dynamic drill-through.

Note

The maximum length of the parameter values that are passed in from Microsoft Dynamics 365 is 2, characters. For example, if you run a report and create a data filter through the Advanced Find user interface, the resulting filter expression that is passed to a filter parameter cannot exceed 2, characters. There is no maximum limit on the number of parameters that you can specify. However, you might have to limit the length of the string on the URL line and number of parameters to meet the requirements of a particular browser.

In This Topic

Adding parameters

Hidden parameters

Adding parameters

You can add parameters to a report to define a report’s individual parameters, pass information through a query, or provide access to user settings, such as CRM_CurrencySymbol and CRM_CurrencyPositivePattern parameters.

The is an element in the report definition (RDL) file that is used to describe an individual parameter in the report. The contains information about an individual parameter that is passed to the data source as part of a query. The following XML code taken from the Account Summary report's RDL file demonstrates how to use the ReportParameter and QueryParameter parameters.

<

ReportParameter

Name

="CRM_FilteredAccount"

>

String

true

select * from FilteredAccount

true

CRM_FilteredAccount

<

Query

>

<rd:UseGenericDesigner>true</rd:UseGenericDesigner>

declare @sql as nVarchar(max)

set @sql = '

SELECT top 10 CAST(accountid as nvarchar(100)) as AccountID,

name, '''' as None

FROM (' + @FilteredAccount + ') as fa'

exec(@sql)

=Parameters!FilteredAccount.Value

CRM