Advanced Reporting in Dynamics 365
Microsoft Dynamics 365 offers several advanced reporting options for online and on-premise solutions. Advanced reports are often necessary when business requirements demand complex calculations, returning multiple data sets, grouping large sets of data based on rules, and retrieving data from other databases. Before creating an advanced report, consider using Advanced Find, Dashboards and Charts, or the Report Wizard.
Tip! Power BI is an excellent tool for reporting and analytics. Click to see how Power BI can bring powerful insights with ease.
This chapter covers advanced reporting options for Dynamics 365, including a high-level overview on each. Jump to the following sections for more information:
When building a report within Dynamics 365, data must be retrieved from the database. Data Sources and DataSets are functions within the various reporting tools which allow you to connect the report to the database. Data Sources control the connection to the database, and the DataSet controls the actual information retrieved.
The Data Source, within the context of CRM, will be a Microsoft SQL Server. By editing the connection, the database can be referenced. Entering the server details and credentials will give the report a connection to the server.
Once the report can reach a database, the DataSet has to be configured and set up. The reporting system will allow data to come from Tables, Views, or Stored Procedures within the database. The compiled database objects can give their results to the report. In other cases, a query can be built within the report itself to give specific results.
When building a data-set, it is very important to keep in mind that the basic Dynamics 365 tables do not focus on security; if you choose to use tables, the report will return all data, regardless of who is running the report. In order to avoid incorrectly revealing information which security roles should be preventing, you should use Filtered Views.
Filtered Views exist for all entities in Dynamics 365. By connecting your report to these views, you take advantage of the efforts put into the security model within Dynamics 365. These views check the security privileges of the user running the report and compare each record in the entity to those settings.
There are other considerations when deciding to use the Filtered Views. Filtered views can affect the performance, and speed at which a report is generated. The filtered views are not always able to take advantage of indexing, but there are several extra tables connected within the views which have an increased cost in processing. For most applications, these trade-offs are balanced by the benefits. A report that has specific requirements for speed or size may alter that balance, at which point it is a business decision to ignore security roles.
When an advanced report is necessary, there are several toolsets which can be utilized to develop the report. The most common choices are variants of Visual Studio. Note: BIDS and SSDT are the same add-ons to Visual Studio. BIDS is for SQL 2008 and below. SSDT is for SQL 2012.
With Report Builder, you can build the reports with minimal programming and development knowledge. The tool contains wizards to easily construct or modify reports. It’s also possible to open report files created by other people or tools and make minor changes without requiring the entire development suite. You can currently download the Report Builder here.
Business Intelligence Development Studio
The most common tool used to build reports is the BIDS (Business Intelligence Development Studio) suite. Visual Studio uses Projects to save development and programs. There are several project types which are not automatically included with Visual Studio. One of these types is reports. Once you create a report project, you can link to various data sources, allowing you to retrieve information from various databases. BIDS includes a wide variety of tools to build your report: specific formatting, sectioning, sub reports, drill down capabilities, and charts can be constructed with the tools included. Any installation of SQL Server has the BIDS suite included as one of the client tools on server installation; you just need to install it.
SQL Server Data Tools with Microsoft Dynamics 365 Report Authoring Extension
The final of the common report builder tools for Dynamics 365 is an extension that is applied to the BIDS suite. The primary purpose of this tool is to allow a report to use an extra data source: Fetch XML. Fetch XML will be discussed later in this chapter. Currently, this extension can be found here.
Custom SSRS reports can be created for CRM online using FetchXML queries. Microsoft does not support using SQL queries to write custom reports for Dynamics 365 online. This is because the SQL server where the CRM database is stored is not exposed to the developers. So we can’t really create a connection.
FetchXML is the only option to write queries when it comes to Dynamics 365 online custom reporting. You can still use visual studio (BIDs/SQL Server Data Tool) for creating FetchXML based reports. You need Visual Studio 2008 Service Pack 1 or higher and need to install a plugin (extension) that enables FetchXML based reporting. It is called Microsoft Dynamics CRM 2011 Report Authoring Extension.
Once the Report Authoring Extension is installed, you will see the Microsoft Dynamics 365 Fetch options on the connection type when creating a data source.
The step by step instructions for creating a FetchXML based report can be found on the PowerObjects blog here.
[callout align=”center” width=”90%”]Hint: The connection string above, consists of the CRM URL and the unique organization name.[/callout]
Since FetchXML is new and still evolving, it has some limitations over SQL based reporting. Here are some of the challenges/limitations of FetchXML based reporting:
- Can’t utilize SQL features like temp tables, table variables, CTEs, Pivot, Ranking, grouping or partitioning.
- If the reporting need is too complex, developing SSRS report with FetchXML becomes impossible.
- It does not support all types of joins.
- Cannot be used on non-CRM databases.
- It has limitations on the number of entities that you can link in the queries.
Reporting requirements beyond Fetch XML
You may encounter reporting requirements that are beyond what can be done with Fetch XML. If your Dynamics 365 is on-premise or partner hosted, then one option is to write a report using SQL queries. If your organization is online, then you might consider writing integration packages that replicate the Dynamics 365 online data needed for reporting to a local SQL server instance and you can write SSRS reports locally using SQL queries. ScribeSoft has a product called Scribe Replication Services that can accomplish this if you ever have a need to replicate Dynamics 365 online data to local SQL server. You can also use SSIS to write packages that perform the replication.
The reports written locally can be accessed from Dynamics 365 reports area making it appear to the users that they are running the reports from within Dynamics 365 by hiding all the details behind the scene. This makes complex reporting possible even with Dynamics 365 online.
There are a few features of SSRS that are only fully available to Dynamics 365 when it’s an on-premise installation.
One of the more useful features with on-premise implementations are complex parameters. By adding a combination of parameters in the report, the same report can function for a variety of different roles. The parameters chosen can even be used to drive the availability of further parameters, in a nested cascade. For example, a time card report could be run for an entire organization. Adding a Department parameter and a User parameter would allow you to run the report for a specific employee within the Billing department.
Sometimes, users want the report to be automatically emailed to them as attachments, or email reports to a user who does not have access to CRM or display reports in a user’s dashboard.
These can be achieved by scheduling reports on the report server. For a report to be scheduled, it has to run under a specific user account hard coded in the report server there are some pros and cons of this approach:
Hint: Any custom reports that are subscribed and that do not utilize the MSCRM_DataSource need to be outside the CustomReports folder. Creating a subscribed report within the CustomReports folder is not supported by Microsoft. In the screenshot below, highlighted in yellow are the reports and data-set that are used for subscription and are outside the CustomReports folder.
Once you have built the report (for online or on-premise), you need to deploy it to your Dynamics 365 instance. First, save your report in a .rdl format, then navigate to the Reports section in Dynamics 365. Click on New in the Ribbon.
As you can see, there’s a button to browse to the completed report file. There are several fields to fill out for the report. The name is important and does not have to be the same as the Reports SSRS file name. However, it is recommended to make them similar so you are able to find the right file if maintenance is required.
Dynamics 365 reports can have Parent reports, just like in SSRS. Categories can be assigned for the report; by default, there are five categories: Administrative, Marketing, PSA, Sales, and Service.
Related record types define which entities the report relates to. A Work Time report might relate to Users, while a Pending Sales report might relate to Opportunities as well as the Users who own the opportunities.
The final option is the Display In box. This sets all of the places that this report is visible, and by default has 3 options: Reports area, Forms for Related Record Types, Lists for Related Record Types. If you mark the report as Related to Accounts, you can choose if your report can be run from an Account Form, from a List of Accounts such as a search, or only from the main Reports area. For display in List for Related Record Types and Forms for Related Record Types to work, the report has to be written with pre-filtering turned on. More information on advanced pre-filtering can be found here.
Once you’re all done with getting the selections just the way you want, Save and Close the report. By refreshing the list of reports, the newly added report should appear. You can edit it almost exactly the same way, including uploading a new .rdl file to replace the existing one in Dynamics 365.