Chapter 16. Reporting and Dashboards
Microsoft Dynamics CRM continues to improve the reporting and dashboard capabilities. Reports are more dynamic and flexible, and the Reporting Wizard that is included enables users to create basic reports on-the-fly and share them with the organization.
In addition, reporting options are extremely flexible with CRM Online. As with the previous version of Dynamics CRM, the ability to upload reports remains, but Microsoft has increased the flexibility of FetchXML (a requirement for online reports) from previous versions of Dynamics CRM to include left outer joins. What this means is that you can now define criteria that will find matches of nonexistence, which was nearly impossible before. For example, you might query the system to find all contacts that don’t have any opportunities associated with them.
For more information about these capabilities, SEE the “Fetch-Based Reports” and “Left Outer Joins” sections, later in this chapter.
Note
Some limitations still apply with CRM Online. See the “Report Wizard” section, later in this chapter, to learn about SQL versus Fetch limitations and how to address and overcome them.
When discussing reporting, we often dig a little deeper to understand exactly what is being asked because the most common answer might not be the right one. This means that although there exists an enterprise reporting service within Dynamics CRM (SQL Server Reporting Services [SSRS]), when most people ask about reports, they are usually looking for ways to view their data; this can be more (or less) than full enterprise reports. Therefore, we usually discuss reporting falling into one of these categories:
SSRS (full reporting services)
Charts and dashboards
Advanced Find functionality
Custom/personalized views
Exporting to Excel
All these categories can deliver aggregate data to end users and, hence, “report” on what customers want to see. Therefore, it is important to understand what is being asked when discussing reporting. This chapter explains the first two options: SSRS and charts and dashboards.
SEE CHAPTER 22 “Customizing the System,” to learn more about the other options listed.
Reporting
Whether you’re using Online or On-Premises, Microsoft Dynamics CRM uses SSRS as the engine for creating and rendering reports. SSRS is a separate application that you can install on a different server than SQL Server or even the Microsoft Dynamics CRM server, if desired. Microsoft Dynamics CRM then connects to SSRS by using the reporting services URL, as specified during an On-Premises installation. There are two differences between online and on-premise usage of SSRS:
Online usage—Reporting usage through CRM Online does not require configuration. It simply works out of the box, and the following sections are specific only for On-Premises installations, where configurations are required between the reporting service and the Dynamics CRM service.
On-Premises usage—The CRM Reporting Extensions are required during installation. The Reporting Extensions are twofold:
SQL elements for scheduling reports
Fetch elements required for creating, running, and scheduling Fetch reports
To install the CRM Reporting Extensions for SSRS, follow the steps outlined in CHAPTER 29, “On-Premises Deployments.”
After the Reporting Extensions are installed, you can access reports in Microsoft Dynamics CRM from the top navigation menu for all major areas (Sales, Service, or Marketing; under the Tools group, see Figure 16.1) or directly from various entities in the system (Account, Contact, and so on). The Available Reports page is shown when the Reports option is selected (see Figure 16.2).
Caution
When viewing reports in the system, you can see all available reports; however, you might or might not be able to display the underlying data, depending on your permissions.
When working with entity records, such as the Contact records shown in Figure 16.3, you can run specific entity reports without needing to access the main Reports area.
Although some reports are already configured to run directly from the entity, you can easily configure a report so that it is available as part of the entity if you desire, as shown in Figure 16.3. To do so, follow these steps:
1. Select (by clicking) a report from the main Reports interface located on the navigation bar (as shown in Figure 16.1) and then select Edit.
2. When the report definition window opens, select Related Record Types from the Categorization section and then click the ellipsis (...) button. Add something from the Available Values selection box to the Selected Values box so that you will be able to run that report directly from the selected entity (see Figure 16.4).
3. Use the Display In option to configure where you want to have the reports available. You can set the following values for this property:
Forms for Related Record Types
Lists for Related Record Types
Reports Area
You can choose any of these values to display the report.
Report Filters
All reports in CRM have a special feature that enables you to prefilter the underlying data when you run a report. You can configure this feature on the first screen when you double-click a report (see Figure 16.5).
Here you see the report criteria that were defined as part of the report definition during initial report creation. Although you can manually change these values and properties every time you run a report, you can also change the default filtering criteria by editing the report definition. To do so, go to the main Reports option located on the navigation bar under Sales, Service, or Marketing; click the report you want to modify; and then select Edit Default Filter from the ribbon (see Figure 16.6).
Make any necessary changes and then click Save Default Filter to save your changes.
Caution
Keep in mind that changes made with Edit Default Filter affect all users running the report.
Categories
Reports are divided into categories so that they can be easily grouped. This is especially useful when an organization is working with many custom reports.
By default, only the following four categories are created in the system, but you can easily create more as necessary:
Administrative reports
Marketing reports
Sales reports
Service reports
Each category has a predefined view to filter and is easily accessible, as shown in Figure 16.7.
To set a category on a report, select the report from the Reports interface and then click Edit on the ribbon. Click the ... button under the last section, Categorization (next to the field Categories), and then select the required categories for the report and click the >> button (see Figure 16.8).
To create new report categories, navigate to Settings > System > Administration and click System Settings. Select the Reporting tab to edit the categories, as shown in Figure 16.9.
Click the Add button to create a new category, enter a label for the new category, and then click OK twice to close the dialogs.
Note
Creating a new category doesn’t mean that a view is also created.
Tip
To use the new category in the shortcut list (refer to Figure 16.8) as part of the report, you must create the view with the new category manually.
For details about how to create custom views, SEE CHAPTER 22.
You can use the shortcut menu that becomes available when you select the drop-down arrow next to Reports on the navigation bar to access recently used reports, as shown in Figure 16.10.
Administration
When editing a report (by selecting it and then selecting Edit from the ribbon), you see two tabs: General and Administration. The Administration option is used to configure the administrative options for the report. By using the options on the tab, you can set the owner of the report and whether the report should be viewed by the user or by the entire organization (see Figure 16.11).
Tip
You can also change the report owner by selecting Assign from the Actions drop-down.
Report Wizard
The Report Wizard provides an easy-to-use interface that enables users to build basic reports without development knowledge. With Microsoft Dynamics CRM 2016, reports are generated using native Fetch-based queries through the data source (called MSCRM_FetchDataSource) found in the Report Manager and available to support Fetch queries.
In addition, the final reports are actually Microsoft SSRS reports, or .rdl files, which you can manipulate by using more advanced editing tools such as Visual Studio, provided that you have Report Authoring Extension installed.
Note
As with earlier versions of Dynamics CRM, reports built with the Report Wizard can be edited using Visual Studio. This is usually done to increase performance, layout, and report complexity. To do so, you need to download and install a Visual Studio 2010 or 2012 plug-in called Microsoft Dynamics CRM 2016 Report Authoring Extension (CRM2016-Bids-ENU-i386.exe). (You can find this Visual Studio plug-in by searching for “CRM 2016 Report Authoring Extension” at Microsoft.com or by going directly to www.microsoft.com/en-us/download/details.aspx?id=50375.) Once this is installed, you can use the required Fetch data source type to parse the Fetch queries in the report’s data source generated by the Report Wizard. You will learn how to install this component later in this chapter.
When working with reports, remember that although you can continue to have the Data Source Type set to SQL (typically to query older reporting versions), for security reasons, CRM Online does not support SQL queries, and the Fetch extension must be used instead.
If you are unfamiliar with the FetchXML query schema, you can create any query with the Advanced Find tool and easily download or view the FetchXML generated by this tool for reuse on custom reports.
An example of a FetchXML query is as follows:
<fetch>
<entity name="account" enableprefiltering="1" >
<attribute name="name" />
</entity>
</fetch>
This code would be equivalent to the following SQL query:
select name from FilteredAccount
Notice that the enableprefiltering attribute in the entity node allows the user to query prefilters.
Be sure to review the “Fetch-Based Reports” section, later in this chapter, for more information about Fetch.
Note
If you are new to Advanced Find, refer to the section “Fetch-Based Reports,” later in this chapter. FetchXML is an advanced topic.
To use the Report Wizard, follow these steps:
1. Navigate to Reports from the Sales, Service, or Marketing navigation bar. Select New to open the dialog shown in Figure 16.12. The Report Type option defaults to Report Wizard Report, which is the one used in this example. If you had created an external report, such as one using Visual Studio, you would select Existing File and upload the file directly. Link to a Web Page enables you to link to a web page that contains a report on a custom web page, such as what you could do for a custom report in HTML5.
2. Click the Report Wizard button to start the wizard. After it starts, you have two options for creating a new report (see Figure 16.13):
Start a New Report
Start from an Existing Report
The second option only enables you to create a new report from a report that was previously generated through this wizard and to make edits to an existing report through the wizard. For this example, create a new report by selecting the first option and clicking Next to continue.
3. In the Report Properties dialog that appears, enter a name for the report and select an option from the Primary Record Type drop-down. The primary record type must be one of the entities available in the system, such as Account, Contact, and so on (see Figure 16.14). Click Next to continue.
Note
The Related Record Type option values depend on the entity you select for Primary Record Type. Related Record Type shows all the entities related to the primary entity selected. For example, you can select Contacts for Related Record Type so that you can create a report of the contacts by account.
4. Select the default report filters for the primary or related record types selected in step 3 (see Figure 16.15). You can use a previously used view or create a new one. To add a new filter, click the Select link and select the property you want to use. For this example, select the property Owner with the criteria Equals Current User. Click Next to continue.
5. In the Lay Out Fields dialog that appears, define the properties you want to have displayed on the report (see Figure 16.16).
6. Select Click Here to Add a Column to add the fields you want to see in the report. You can choose the record type, which is limited to the entities you selected for Primary Record Type and Related Record Type in earlier steps.
7. After selecting the record type, select the column, which will be any of the properties of the record type you previously selected. The data type and name are displayed only for informational purposes, and you can’t change them from this interface. The only thing you can change is the column width, in pixels.
The last option in the Add Column dialog, Summary Type, is available for only some data types, such as money and numeric data types. Figure 16.17 shows the available options that you can select for Summary Type. (They are similar to SQL aggregate functions.)
8. If any of the Summary Type options were used, configure the grouping by selecting Click Here to Add a Grouping (refer to Figure 16.16). Figure 16.18 shows the grouping options.
The columns that you can select in the Add Grouping dialog are based on any of the entities previously selected. You can create up to three different groupings that will be nested among themselves. Click Next to continue.
Note
The Time Interval option is available only for fields that have datetime as their data type. This option enables grouping by day, week, month, year, fiscal period, and fiscal year.
9. Now that you have defined which columns to display and how they should be grouped, specify their format. You can select the basic format of the report, which includes charting. In this case, select Table Only because you’re building a basic report for this example. Click Next to continue.
10. In the last step of the wizard, which provides the report summary, click Next to continue. The report is generated, and the results are displayed. Note that a delay might occur while the report is being generated.
11. Click Finish to close the wizard. If necessary, you can now select and edit the properties of the report. Notice that the wizard has automatically populated the name of the report and the categorization fields.
12. To test the new report, select the report from the Reports interface and either select Run Report from the top menu or double-click the report name. The report runs, as shown in the example in Figure 16.19.
Modifying a Report
If you want to modify the report you just created, you can easily run the Report Wizard again. For example, suppose that you want to change the selected format from a table to a graphical chart representation. Follow these steps:
1. Click the Report Wizard button, and the wizard starts with the starting point selected for this report, with the option to overwrite it (see Figure 16.20).
2. Continue through the Report Wizard steps, changing the format from Table to Chart and Table and selecting the Chart Type (see Figure 16.21).
Tip
The pie chart type might be disabled if you didn’t select the Sum or Percent of Total option in the Summary columns.
3. Click Next to customize the chart format. You can select the labels of the x-axis and y-axis, as well as the fields for them, as shown in Figure 16.22.
4. Click Next to go to the summary screen for review and then click Next to modify the report.
Now if you run the report, it will look as shown in Figure 16.23.
Scheduling Reports
SSRS has many advanced features that are closely integrated in this version of Microsoft Dynamics CRM. For example, you can schedule report execution to receive a copy of a report automatically.
Tip
This option is limited to On-Premises users of Microsoft Dynamics CRM only.
This is a great feature that many organizations use to proactively receive data. For example, a CFO usually wants to see all orders entered into the system for the week; SSRS can generate a report for this automatically every Friday morning and then email the report to the CFO, with no user intervention, so that when the CFO arrives in the office in the morning, there is an email from CRM with the report attached.
To access this feature, select Reports > Schedule Report (see Figure 16.24). The Report Scheduling Wizard starts and presents two options, discussed in the following sections:
On Demand
On a Schedule
By default, only administrators can schedule reports. To give this feature to a lower-privileged role, an administrator must grant permission in the Add Reporting Services Reports security option, under Miscellaneous Privileges in the Core Records tab of the security role configuration interface. If you are an administrator, you can access this area from the Administration section of the Settings area. You then click Security Roles and then double-click the role you want to customize. Figure 16.25 shows this permission added to the Customer Service Representative role.
Running a Report on Demand
An on-demand report generates a snapshot as soon as you finish the wizard. In this example, you’re going to work with the Activities report.
Depending on the report selected, you must specify values for the report parameters. You can also edit the default filters by clicking the Edit Filter button.
After you click Next, you can choose whether to generate the report snapshot right away or just save the report snapshot definition for later use (see Figure 16.26). If you choose the first option and then click Save, the new snapshot is generated, and the overview is detailed.
The Completing the Report Scheduling Wizard dialog that appears next contains all the necessary instructions to access and view the snapshot report that was just created. Click Finish to close the wizard. You then see the snapshot shown in Figure 16.27.
Running a Report on a Schedule
Selecting On a Schedule in the Report Scheduling Wizard enables you to select the frequency of the report execution, as well as the time when you want the report to execute (see Figure 16.28). The available options for the frequency are as follows:
Once
Hourly
Daily
Weekly
Monthly
The options that appear here depend on the frequency selected. For example, if you select Once, you can select only the start time. But if you select Hourly, you can select the number of hours and minutes you want the report to be run. Similarly, the options change for Daily, Weekly, and Monthly. With Once selected, click Next, then on the next dialog, set the start date and the end date for the report.
Tip
If you want the report to be generated forever, do not specify an end date.
After you set the starting and ending dates, you can define the report parameters and edit the default filters for the report. These interfaces are similar to the ones for the On Demand option, explained previously. When you have defined the parameters, click Next. An overview is presented so that you can review the scheduled report settings.
If errors occur when you’re trying to schedule a report, the SSRS server most likely is not configured properly. To check or change your configuration, start the Reporting Services Configuration Manager application that is inside the Microsoft SQL Server programs group and inside the configuration tools (see Figure 16.29).
Exporting Reports
You can export all Microsoft Dynamics CRM reports and report data in the following formats:
XML file with report data
CSV (comma delimited)
Acrobat (PDF) file
MHTML (web archive)
Excel
TIFF file
Word
You find these options for a report by clicking the Save icon in the navigation bar (see Figure 16.30). Microsoft Dynamics CRM reports are be completely portable, and the various export options enable you to easily manipulate the data.
To export the report definition (not just the report format or data), select the report you want to export from the Reports interface and select Report > Edit from the command bar. When the report properties window appears, select Actions > Download Report, as shown in Figure 16.31.
You can download the report in its Report Definition Language (RDL) format. This is the standard SSRS extension, based on XML, that you can edit using an editor such as Visual Studio, as mentioned earlier in this chapter.
SEE the section “Building Custom Reports with SSRS,” later in this chapter, for information on advanced report customization.
Tip
Reports can also belong to a solution, and you can easily move a solution from one deployment to another. For more information about solutions and solution management, see Chapter 22.
Advanced Features
In addition to running, creating, editing, or downloading reports, you can perform other actions when working with reports, as detailed next.
Sharing Reports
Any reports that you create are available to you. If you want to share a custom report with a user who has a lower-privilege role, select the report from the main Reports interface and then select ... > Share (see Figure 16.32). From the interface that appears, you can give the following permissions to users:
Read—Enables the user to run the report.
Write—Enables the user to modify the report definition and to change the properties and default filters.
Delete—Enables the user to delete the report.
Append—Not applicable.
Assign—Enables the user to change the owner of the report. This setting also gives write permission to the user for the report.
Share—Enables the user to share the report with other users.
Note that if you add only read permissions for a user, that user can share the report with other users even though you didn’t select the Share option. This is because the default permissions are set to allow sharing between users. Of course, the user can give only read permissions to the other users, but you should carefully consider the implications of each permission before you set them. Figure 16.33 illustrates the sharing options available with reports.
Exposing Reports to SSRS
As with earlier versions of Microsoft CRM, Dynamics CRM 2016 doesn’t expose every report in the SSRS Report Manager application. If you want to use a report from another application, it must be published in Microsoft CRM. For example, if you go to the Report Manager application of SSRS (by navigating to http://<<srs server name>>/reports), you see a folder created for the organization; however, it is empty (see Figure 16.34).
All the predefined reports are now on a different common folder for all the organizations in the system; this folder is called SharedReports/8.0.xxx. This folder contains all the out-of-the-box reports created (see Figure 16.35).
Every custom report you create using the Report Wizard and every report snapshot is placed in SharedReports/8.0.xxx folder. (Dynamics CRM 2015 used the folder SharedReports/7.0.xxx, and 2013 used SharedReports/6.0.xxx.)
If you need to use any of these reports or expose them to another application, go to the CRM interface, select the report you want to expose, and select Edit Report > Actions > Publish Report for External Use. After you select this option, no confirmation or message states that the operation is complete. In fact, the only way to verify that it was published is to go to the Report Manager web application and navigate to your SharedReports/8.0.xxx folder, where you should see the report available.
Fetch-based Reports
Microsoft Dynamics CRM uses a proprietary type of query language known as FetchXML (which is Fetch based). It supports similar query capabilities to query expressions and is based on a schema that describes the capabilities of the language. You can find the schema in the SDKSchemasfetch.xsd file in the SDK package.
To create a query using Fetch, you must build an XML query string. Then you can execute the query string.
Consider the following when constructing the query string:
It must conform to the schema definition for the FetchXML language.
The resultant records are only those that the querying user has permission to read.
Be cautious to limit the attributes requested in a query, as requesting a lot of attributes may result in long-running queries.
There is a limitation of 50,000 records per page.
The following is an example of a FetchXML query that retrieves all contacts:
<fetch mapping="logical">
<entity name="contact">
<attribute name="fullname">
</entity>
</fetch>
FetchXML allows complex querying and filtering. The following is an example of a FetchXML query that retrieves all contacts that were created in the past 30 days, are active, and are owned by the current user:
<fetch distinct="false" mapping="logical">
<entity name="contact">
<attribute name="fullname">
<filter type="and">
<condition attribute="ownerid" operator="eq-userid"/>
<condition attribute="statecode" operator="eq" value="0"/>
<condition attribute="createdon" operator="last-x-days"
value="30"/>
</filter>
</entity>
</fetch>
This code would get translated into C# using the FetchExpression and using the EntityCollection holder.
Tip
An easy way to learn Fetch is to consult the software development kit (SDK), as noted previously, and at the same time run some Advanced Finds in Dynamics CRM and then view the FetchXML directly from the Advanced Find.
Figure 16.36 shows the preceding query, and when you click the Download Fetch XML button, the query is displayed as shown in Figure 16.37.
Requirements
When you work with Fetch-based reports, the following components are required:
Microsoft Visual Studio 2010 or 2012
Business Intelligence Development Studio or SQL Server Data Tools
Microsoft Dynamics CRM 2016 Reporting Authoring Extension
These components are explained earlier in this chapter, as well has how to configure and install them.
Left Outer Joins
With the addition of left outer joins, users can perform queries on data and show where a negative condition might exist. For example, to show all contacts without any opportunities, you can easily write a FetchXML statement that has the following new properties:
Link type
Operator
Here is an example of this query for contacts without any opportunities:
<fetch mapping="logical">
<entity name="contact">
<attribute name="fullname">
<link-entity name="opportunity" from="opportunityid" to="contactid"
link-type="outer"/>
<filter operator="and">
<condition entityname="opportunity" attribute="opportunityid"
operator="null"/>
</filter>
</entity>
</fetch>
Charts and Dashboards
The first part of this section covers general charts, which play a significant part in dashboards. Therefore, the discussion now turns to dashboards.
Charts allow you to show data graphically as the following types:
Column
Bar
Line
Pie
Funnel
The core entities come with predefined charts that you can display by clicking the entity from the sidebar list (for example, Account or Contact) and then clicking the collapsed pane labeled Charts on the right side of the grid, as shown in Figure 16.38.
The charts are displayed on the right side of the grid view. For example, Figure 16.39 shows a bar chart of active accounts by owner.
Any system or custom view is also tied to charts and affects the results displayed not only on the grid but also on the chart. So, if you change the view to see the inactive accounts, you also update the selected chart result. To change the view, you can click the current view name and select a different view from the drop-down list. For this example, select Active Accounts, as shown in Figure 16.40.
In addition, you can drill down in the chart bars. When you click any bar, the underlying data of the grid changes to display the new data represented by the chart.
Because the chart results are tied to the results displayed on the grid, you can also use the Filter feature to modify the chart results. Because it is easy to use with the predefined views, this helpful new feature in some cases replaces Advanced Find. To use the Filter feature, find the small filter icon next to the chart and click it. You now have filter controls added to each of the column headers of the grid so that you can add specific or custom filters (see Figure 16.41).
After you apply a filter, a Refresh Chart button appears in the middle of the chart. You can click it to refresh the chart (see Figure 16.42).
Working with Charts
There are two main types of charts: system charts and personal charts. System charts can be created with customizations and are available to all users. Personal charts are created by users (as explained in this chapter) and are visible only to the user who created them.
To learn more about system charts, SEE CHAPTER 22.
Note
Check the CRM 2016 SDK for types of charts other than the ones available to the user though the CRM 2016 web user interface. You can also include web resources as charts, as explained later in this chapter; that is also documented in the CRM 2016 SDK.
Chart Tools
To create a new personal chart, click the + at the top of the chart, as shown in Figure 16.43. The Chart Designer is displayed, and you can start creating your new chart.
When creating a new chart, you must specify a name for the chart and choose a legend, an aggregate function, and a category. Optionally, you can add a description to the chart.
If you are working with the core entities, it is important to review the current charts before creating a new one; after all, a chart that fits your needs might already exist.
Tip
When creating a new chart, you can skip the Name field, which is autocompleted with the concatenation of the fields selected—for example, “Legend by Category” when you select the Legend and Category fields. For example, if you select the legend Credit Limit and the category Owner, the name is autocompleted as Credit Limit by Owner.
The Aggregate option changes depending on the type of field you selected for the legend. If the type of this field is numeric, you can use Sum, Average, Count: All, Count: Non-empty, Max, and Min for the aggregation type.
Figure 16.44 shows a custom chart that shows the Credit Limit by Owner of the Account entity.
Each chart also has some advanced options. To access them, you just click the grid icon at the far right of the series row (known as Top/Bottom rules), and you can then limit the X or Y items that will be displayed if the chart is hard to read because it contains a large number of series (see Figure 16.59).
The advanced option allowing the top X and bottom Y options (as shown in Figure 16.45) is a great feature that allows you to represent data quickly and accurately.
Click the save icon at the top of the Chart Designer to save your chart.
Personal charts, which users create from entity views, can be shared with other users in different ways. If the user you want to share a chart with belongs to the same organization, you must close the Chart Designer by selecting the right-pointing arrow (>) and then selecting the ... and the Share option, as shown in Figure 16.46.
The sharing options for a chart are the same as the sharing options for a report, described earlier in this chapter (refer to Figure 16.33).
You can change the chart layout by selecting Chart Pane, which changes the position of the chart (see Figure 16.47). Selecting Off completely removes the chart from the main view, leaving the grid option only. To go back to the original layout, click ... > Chart Pane and select the preferred option.
Exporting Charts
If the user you want to share a chart with is in another organization, you can export the chart to an XML file by selecting ... > Export Chart.
The exported XML file can then be easily imported by another organization, as long as the other organization contains the same entities and fields used by the organization where the chart was created.
The following is an example of the XML generated for a chart after it is exported and opened:
<visualization>
<visualizationid>{EC32AA46-6155-E311-BFC9-00155D002B01}</visualizationid>
<name>Credit Limit by Owner</name>
<primaryentitytypecode>account</primaryentitytypecode>
...
</Chart>
</presentationdescription>
<isdefault>false</isdefault>
</visualization>
Tip
You can edit the XML by using any editing tool of your choice (such as Visual Studio 2015) and then easily import it back by using the Import Chart option.
With only a little knowledge of XML, you can change the font type of the chart fonts and colors by changing the Font attribute of the AxisY node. The following syntax makes the axis text bigger (see Figure 16.48):
<AxisY LabelAutoFitMinFontSize="8" TitleForeColor="255, 0, 0"
TitleFont="{0}, 64px" IntervalAutoMode="VariableCount">
You can edit the XML file with any editor, such as Notepad, and then import the modified XML file to CRM. When importing the XML file, you are asked whether you want to replace the existing chart (if one exists) or create a new copy.
Visualizations
The CRM SDK refers to charts as visualizations. The reason for this is that there is a tricky way to add other kinds of charts by writing a little code. You can have charts that are actually web resources that can be displayed side-by-side with the grid. For example, you could show a Bing Map control in the visualization area instead of a chart. To do this, you need to create a simple XML file to which you assign a name and a description, identify the name of the web resource you want to use and the primary entity where you want the visualization to be displayed, and specify whether it will be the default visualization for the entity. Here is an example of the XML file you would need to create for this:
<visualization>
<name>BingMaps</name>
<description>Bing Map</description>
<webresourcename>webforti_map.htm</webresourcename>
<primaryentitytypecode>account</primaryentitytypecode>
<isdefault>true</isdefault>
</visualization>
Then you save this file with the .xml extension (for example, Customchart.xml), and you can import it just as you would any other chart, as explained earlier in this chapter. The result, however, is what you have put on the web resource, which can be an image, an HTML file, and so forth.
Dashboards
Dashboards enable the user to see more than one chart from different entities on a single page. In addition to adding charts (as discussed earlier in this chapter), you can add grids, IFRAMEs, and web resources to a dashboard.
Grids are representations of underlying CRM data (for example, activities that relate to an account or leads that relate to a region), IFRAMEs are pass-through connections to other web pages (either externally or internally, or even self-referencing), and web resources are files that can be images, Silverlight applications, HTML pages, or script files.
The CRM system comes with 16 predefined system dashboards you can use as models:
Customer Service Manager Dashboard
Customer Service Operations Dashboard
Customer Service Performance Dashboard
Customer Service Performance Dashboard (original version)
Customer Service Representative Dashboard
Customer Service Representative Social Dashboard
Customer Service Representative Social Dashboard (original version)
Marketing Dashboard
Marketing Social Dashboard
Microsoft Dynamics CRM Overview
Microsoft Dynamics CRM Social Overview
Sales Activity Dashboard
Sales Activity Social Dashboard
Sales Dashboard
Sales Performance Dashboard
Server-Side Synchronization Performance
As with charts, you can use two main types of dashboards: the system dashboards that are available to all users in the organization and the personal dashboards that a user can create. In addition, dashboards can be assigned by roles, if desired.
Figure 16.49 shows an example of the Microsoft Dynamics CRM Overview Dashboard.
Creating a New Dashboard
To create a new personal dashboard, follow these steps:
1. Go to the Dashboard area from within the Sales, Service, or Marketing navigation bar and then click Dashboards.
2. Click the New button, and the Choose Layout dialog appears.
3. Select a layout from the predefined layouts on the left side of the Choose Layout dialog. Sample charts on the right show you how the layouts will look.
4. Click the Create button. The Dashboard Editor window appears (see Figure 16.50).
5. To add a chart, click the chart icon that is inside of the first component. Select the entity record type (for example, Account), the view (for example, My Active Accounts), and the chart you want to display (for example, Accounts by Industry).
6. Click Add to add the chart, and the chart is added to the dashboard.
7. Move to the next component and click the list icon. Select the entity and view you want to display.
8. Click Add to add the list component, and the list component is added to the dashboard.
9. Move to the next component and click the IFRAME icon. In the Add an IFRAME dialog that appears (see Figure 16.51), enter a name and the URL of the page you want to display. In this case, you don’t want to pass parameters to the URL, so you need to confirm that the Pass Record Object-Type Code and Unique Identifier as Parameters check box is unchecked. (You would check this box, however, if you entered a URL of a custom application you created that needs to know the record type and ID of who is calling it, for example.) Also notice that a new addition to Dynamics CRM 2016 allows the selection of whether you want the IFRAME to be visible in the client for tablets such as iPads.
10. Click OK to add the IFRAME, and the IFRAME is added to the dashboard. Note, however, that the URL is not rendered in the design view (see Figure 16.52).
11. Move to the next component and click the web resource icon. Select a web resource (such as a PNG image that displays your company logo).
12. Click OK to add the web resource. You see only the name of the web resource added to the dashboard, and the image is not rendered in the design mode (see Figure 16.53).
13. Drag and drop the components around as necessary and then finish this dashboard by entering the dashboard name and clicking Save.
14. To clean the empty space, select the component where you added the image and click ... > Increase Width.
15. Click Save to save the work and then click Close. The dashboard opens in a new window, showing the rendered content (see Figure 16.54).
Dashboard Features
Unfortunately, there is no intuitive way to add new components to a dashboard. If you start from one of the predefined templates, you can add from four to six components, depending on the layout selected. However, if you want to have a dashboard with more components, you need to click the section first and then click any of the Chart, List, IFRAME, or Web Resource buttons on the command bar (under ...). By clicking any of those buttons you can add a new component to a dashboard, but if you have an empty component selected first (instead of the entire section), by clicking a button you replace the empty component space with the selected component.
Tip
You cannot change the component type once you select it; for example, you cannot change from Chart to IFRAME. To change the type you must remove the component and create a new one.
You can share dashboards with other users within the same organization, and you can also assign them to other users. However, unlike charts, they cannot be easily exported or imported. If you want to export a dashboard to be used in a different organization, you must create a solution and include the dashboard inside that solution. (However, personal dashboards cannot be added to solutions for export.)
To learn more about solutions, SEE CHAPTER 22.
When working with the charts on a dashboard, you can use some cool features. When you drag your mouse over to a chart, three icons appear at the top-right corner of the component.
The first icon refreshes the chart because the underlying data might have been changed; you have to manually click this icon to get an updated version of the chart. If you have more than one chart on the dashboard, you can click the Refresh All button on the command bar to update all charts and components used in the dashboard.
The second icon, the grid, enables you to see the list of records used by the chart. You can see the details of the view used to generate the chart, as shown in Figure 16.55.
The last icon is an enlargement image you can click to enlarge the chart to occupy the entire dashboard.
Note
You cannot print charts except by taking a screenshot. If you want printable charts, you should use SSRS reports, as described in the next section.
Introduction to SSRS
Microsoft SQL Server Reporting Services (SSRS) is a client/server reporting platform installed within Microsoft SQL Server. It is based on a service-oriented architecture (SOA), so it can be used as a service. SSRS was first introduced within Microsoft Dynamics CRM 3.0, using Microsoft SQL Server 2000 (before that, CRM used Crystal Reporting), and it has progressed with every version of CRM and SQL since then.
SSRS consists of the following components:
Report Manager—This web application acts as a user interface application for managing and deploying the reports in the platform, as well as managing the security access for each report. You can usually find it from the server that has SSRS loaded, via http://localhost/reports.
Report Server—This web service provides a common interface and entry point for all applications (including Report Manager and Microsoft Dynamics CRM) to interact with Report Server. You can usually find it from the server that has SSRS loaded, via http://localhost/reportsserver.
Report database—SSRS uses two databases stored in SQL Server as the repository of the deployed reports, usually named ReportServer and ReportServerTempDB.
SRSS (using the Microsoft SQL Server instance name)—This Windows service is responsible for processing related functions, such as report scheduling.
Other components—The other components include configuration tools, Visual Studio project templates for report authoring, .NET controls to render and display reports in Windows, and custom web applications and API documentation for extensibility and development.
In addition to these components, SSRS has the following features:
Support for report snapshot creation
Support for scheduling of automated snapshot reports
Alerts so you can be notified via email when a report is created
Capability for all reports to be exported in the following formats:
Microsoft Excel
Acrobat PDF
MSHTML (web archive)
TIFF images
Microsoft Word
XML file with report data
CSV (comma delimited)
Capability to build reports with the open standard Report Definition Language (RDL), which is based on the XML standard (Reports can then be built not only with Visual Studio but also with other third-party tools.)
Capability for each report to manage different data sources (This means that it is not necessarily tied to Microsoft SQL Server data. Reports can show data from any .NET-compatible data provider or from any OLEDB data provider.)
Capability to display data in either tabular, matrix, or graphical forms, as well as to use expressions to format the data properly
Microsoft Dynamics CRM handles all the reports through SSRS, so you have access to all its benefits.
Tip
Unlike in previous versions of SSRS, SSRS with SQL Server 2012 or 2014 now includes the ability to add HTML into a report. This was a limitation that resulted in challenges with report rendering but has now been resolved.
Note
SSRS is a complex platform and providing complete details about it is beyond the scope of this book. For more information about SSRS, see Microsoft SQL Server books online or similar topics by Sams Publishing. To understand this chapter, you need only a basic understanding of SSRS and SQL.
Custom Reports
Custom reports are reports written with an external tool such as Visual Studio. As explained earlier in this chapter, you can easily build new basic custom reports with the Report Wizard in Microsoft Dynamics CRM; however, reports created using the wizard are not so flexible. In some cases, you need to write more complex reports and have a more flexible page layout and design. In those cases, you need to use a tool such as Visual Studio to create and build your custom reports.
When Are Custom Reports Recommended?
You may need custom reports for a variety of reasons, including the following:
You might want special or custom designs such as those with complex layouts that can’t be done with the Report Wizard.
You might need to have a report with mixed data from data sources other than Microsoft CRM. For example, say that you control the inventory counts of your products on a separate system that uses an Oracle database, and you want to have one report that shows the CRM orders with their product details from Oracle and their inventory counts.
You can build custom reports with SSRS or with any other report application, such as Crystal Reports, or even with a custom application built in ASP.NET. The range of applications that you can use to build custom reports is beyond the scope of this book.
Installing CRM 2016 Report Authoring Extension
If you want to modify the reports generated by the Report Wizard or if you want to use Fetch queries in a report, you must install the CRM 2016 Report Authoring Extension, which you do by following these steps:
1. Download the Microsoft Dynamics CRM 2016 Report Authoring Extension from http://download.microsoft.com.
2. Run the file CRM2016-Bids-ENU-i386.exe, and a welcome screen appears.
3. Click Next to have the Setup Wizard check for updates and then click Next to continue to the next step and accept the licensing agreement. Check the I Accept the License Agreement check box and click I Accept to continue.
4. Install any necessary components, as required, and then click Next.
5. Setup checks the software components necessary to run this setup again. If everything is okay with your system, click Next and then install CRM Report Authoring Extension by clicking Install (see Figure 16.56).
6. When Setup finishes, click Finish.
After installing the CRM 2016 Report Authoring Extension, you can work with any report generated by the Report Wizard by opening it with Visual Studio. You can see that the data source connection type is Microsoft Dynamics CRM Fetch, and the connection string points to the CRM 2016 organization URL. Figure 16.57 shows the Data Source Properties dialog with the report opened in Visual Studio 2012.
Tip
It is very important that the connection string have the URL and the organization name separated with a semicolon (as shown in Figure 16.57).
As shown in the Dataset Properties dialog, the query is written in Fetch (as shown in Figure 16.58).
Note
The Fetch query shown in Figure 16.86 might seem daunting the first time you work with it. As mentioned earlier in this chapter, learning the syntax and language of (and how to write) Fetch queries can take some time. If you are unfamiliar with the Fetch language, we recommend working with the CRM 2016 Advanced Find tool and then downloading via the Download Fetch XML button. Doing so will help you better understand the Fetch language.
Building Custom Reports with SSRS
When you install Microsoft SQL Server Data Tools, you automatically also install Visual Studio and project templates that you can use to build SSRS reports. You can find these project templates under the Business Intelligence group (see Figure 16.59).
Note
If you do not have Visual Studio installed, SQL Server Setup installs a limited version of Visual Studio (called SQL Server Data Tools, using the 2012 version), which only allows you to build reports and use the Business Intelligence projects. You cannot create Windows Forms applications or custom websites with this version.
The Microsoft Dynamics CRM SDK comes with detailed documentation about custom reports development. Refer to the Developers Guide to Reports for Microsoft Dynamics CRM in the SDK.
Even though you can create a new report in Visual Studio from scratch, we recommend using one of the existing preinstalled reports as a template. (For Dynamics CRM Online, you should start with the Report Wizard.) Follow these steps to use a preinstalled report as a template:
1. Go to Reports in the navigation bar, under Sales, Service, or Marketing.
2. Select the report you want to use as a template. Generally, you want to select a report that is similar to the one you want to create. For example, if you want to create a custom sales report, select one of the existing sales reports. Then click the Edit button on the navigation bar.
3. Select Actions > Download Report and then select the report (in this case, use the Account Distribution report).
4. Save the report on your local machine.
5. Rename the file with the new name of the report you want to build. In this example, rename the report to Contacts Report.rdl.
6. Start Visual Studio and select New > Project.
7. Click Business Intelligence in the Project Templates area and select the Report Server Project template.
8. In the Solution Explorer, right-click the Reports folder and click Add > Existing Item.
9. Navigate to the folder where you stored the Contacts Report.rdl report in step 4 and click the Add button.
When you open the report in Visual Studio, it should look similar to the one in Figure 16.60.
Developing and Testing Reports in Visual Studio
After you create a report project and add the report in Visual Studio, as explained earlier, you are ready to start modifying the report. To test a report quickly, you must fix the dataset’s connection strings to point to your CRM server database. To do this, follow these steps:
1. Open the report you want to edit, go to the Report Data Explorer, and expand the Data Sources node, where you should see a data source named CRM, as shown in Figure 16.61.
2. Double-click the CRM data source to open the Data Source Properties dialog, as shown in Figure 16.62.
3. Select the server name in the Server Name field and locate the CRM database and change its name to <<Your Organization Name>>_MSCRM (see Figure 16.63).
4. Click the Test Connection button to ensure that it connects successfully.
5. Click OK three times to close the dialogs.
Caution
The reports running on the SSRS server use a shared data source, so you should not deploy this data source.
As shown in Figure 16.64, Microsoft Dynamics CRM has three different types of custom reports:
Report Wizard Report—This option is explained earlier in this chapter.
Existing File—This option is described later in this chapter.
Link to Webpage—This option is used only to link to an existing web page. It is explained later in this chapter.
To take advantage of the benefits of the CRM prefiltering feature, you need to keep some important considerations in mind when writing custom reports in Visual Studio, as described in the following section.
Filtered Views
Although you can build SQL queries by using the tables directly from the CRM SQL Server dataset, doing so is not recommended. The CRM SQL Server tables are shown in Figure 16.65.
Instead of using these tables directly for SQL queries, you can use predefined views and thus make life a lot easier because you don’t have to spend time trying to understand the complexity of the tables by studying an entity-relationship diagram (ERD).
A lot of views have names similar to the CRM entity names. The views you should use with your reports are the ones with the Filtered prefix. If you want to create a report that shows all the contact names, your underlying query will look similar to this:
select fullname from FilteredContact
One advantage of using these views is that they are updated automatically every time you add a custom attribute to an entity from the CRM customization interface. In addition, when a custom entity is created in CRM, it automatically creates a filtered view for the new custom entity. In the preceding example, the database view name would be dbo.FilteredContact.
Most importantly, filtered views provide security based on the user record permissions, so they show only the data that the user who is running the report has permission to see as well as the fields the user has permission to see if field-level security configurations are deployed.
Note
The custom properties and entities also have the prefix shown in the schema name. By default, this prefix is New_. However, you can change it in the Publisher setting of each solution. If you create a custom entity with the name Event, for example, the filtered view created is dbo.FilteredNew_event by default.
To learn more about customizing solution settings, refer to CHAPTER 22.
Deployment
To deploy a report in SSRS, you normally use the Report Manager web application. However, you should not use this option when working with custom CRM reports because you won’t be able to see the report in CRM if you do.
You can deploy reports from either the web or Outlook client interfaces. To deploy a report for CRM, you must use the CRM web client interface and follow these steps:
1. Go to the Reports area from the navigation bar, under either Sales, Service, or Marketing.
2. Click the New button.
3. Select Existing File in the Report Type dropdown (see Figure 16.66).
4. Under File Location, enter the full path of the report you built.
5. Optionally, change the name of the report, which by default is autopopulated with the name of the report file, and the two don’t need to be the same.
6. Optionally, select the categories, related record types, and display in the options.
7. Click Save and Close.
Report Parameters
To see the predefined report parameters from Visual Studio, open the report you are authoring and expand the Parameters node on the Report Data window (see Figure 16.67).
The parameters include the following:
CRM_FilterText—This parameter is used to display the Filter Summary text box (located in the report header), which displays the prefilters selected by the user.
CRM_URL—This parameter provides drill-through capabilities on the CRM reports. You can use this to supply quick links to edit the instances of entities that you display on your report, for example.
CRM_FilteredEntity—This parameter is used to set up the default prefilters on reports. You can add as many parameters as you like on the same report; you might have parameters such as CRM_FilteredContact and CRM_FilteredAccount, for example. When you deploy and run the report, you see the prefilters.
Caution
If you don’t have any of these parameters defined, the report runs automatically without having the user select the prefilters. Also, users can’t add prefilters by modifying the default report filters from the CRM reports area through the web or Outlook client interfaces.
CRM_NumberLanguageCode—This parameter determines the language of the user running the report. It is useful for multilanguage report implementations. This parameter must have a default value from a query using the fn_GetFormatStrings() function and using NumberLanguageCode for the Value field, as shown in Figure 16.68. For example, this function returns a value of en-US for English language in the United States.
Note
When adding these parameters, be sure to set them as internal and set a default value of distinct or null. This default value can be a nonqueried value or might come from a query.
After you add a query to the dataset, click the Refresh button so that you can start using the fields on your report; otherwise, you get errors when you try to test and deploy it.
Note
It is always recommended to use the filtered views. With these types of reports, if you use Windows authentication in your application, the security will be in place.
Building Custom Reports with ASP.NET
In addition to basing a new report on an existing one, another way to create a custom report is to create an ASP.NET web application and then use the Link to Web Page option to deploy it, as explained earlier. When you use this method, you don’t get all the benefits inherent to SSRS (including the capability to pass parameters to the reports and also use prefiltering), but you can extend a report with other features that are not possible with SSRS but easy to do with a custom ASP.NET application. Just keep in mind that if you need to create a report using this method, you must handle the filtering options in your application manually.
This following steps walk you through an example of a custom report built in ASP.NET 4.6 that takes advantage of LINQ and Visual Studio 2015 (although it would also work with Visual Studio 2013):
1. Open Visual Studio 2015 and create a new project by going to File > New Website.
2. In the New Website window that appears (see Figure 16.69), select ASP.NET Empty Website and enter the location address for your website. For the language in this example, use C# and the 4.6 Framework. Then click OK to create the website.
3. In the Solution Explorer, right-click your website URL and select Add > Add New Item.
4. In the window that appears, select LINQ to SQL Classes, as shown in Figure 16.70, and click Add.
5. If you get a warning telling you to place the file in the app_code folder, click Yes.
6. In the Server Explorer on the left, right-click Data Connections and select the Add Connection option to add a new connection, as shown in Figure 16.71.
7. In the Add Connection dialog that appears, select your data source type and provider and then select Microsoft SQL Server followed by your CRM database server and then your organization database name (which ends in _MSCRM), as shown in Figure 16.72. Click Test Connection to ensure that you have access, and then click OK.
8. Expand the data connection you just created in the Server Explorer and expand the Views folder.
9. Locate the FilteredContact view and drag and drop it into the Object Relational Designer, as shown in Figure 16.73.
10. Build your solution within Visual Studio and check and correct any errors you might have.
11. Go to the Solution Explorer, right-click your website URL, and select Add > Add New Item > Web Form and enter Default.aspx for the name. Click Add and drag and drop a GridView control, as shown in Figure 16.74.
12. Under GridView Tasks, choose a data source and select New Data Source.
13. Select LINQ and click OK, as shown in Figure 16.75.
14. Leave the option that is displayed as the context object, as shown in Figure 16.76, and click Next.
15. Select the fields you want to display in the GridView control. Figure 16.77 shows fullname and jobtitle selected. Click Finish.
To test the application you created, press Ctrl+F5. You should get a page similar to the one displayed in Figure 16.78.
Custom Reports with ASP.NET Deployment
To create custom reports with an ASP.NET deployment, you can use the CRM client interface and follow these steps:
Note
You can deploy reports from either the web or Outlook client interfaces.
1. Go to Reports in the navigation bar under either Sales, Service, or Marketing.
2. Click New.
3. Select Link to Webpage in the Report Type field. Under Webpage URL, enter the URL of the custom application you built and enter a name for the report. Optionally, you can select the Categories, Related Record Types, and Display In options (see Figure 16.79).
Note
This example uses localhost. You may need to change this reference when you deploy to a public web server.
4. Click Save and Close.
Now you see the report added in the Reports area and shown in the list. When you double-click the report, a new Internet Explorer window opens to the custom application page, as shown in Figure 16.80.
Summary
This chapter describes how Microsoft Dynamics CRM addresses the concept of reporting. Keep in mind that reports include all of the following:
SSRS reports
Charts/visualizations/dashboards
Custom reports
This chapter covers how to quickly and easily create new reports either by using the Report Wizard or via custom development. It also reviews the report scheduling feature and how to export reports for backups or redeployment.
In addition, this chapter discusses how to work with charts and their associated components, as well as how to work with dashboards. It also looks at different options for building custom reports with SSRS using Visual Studio and the Business Intelligence projects or by using Visual Studio for custom web application development.