Grid Filters

Grids in Dynamics 365 provide valuable information. That is, in every area of the application all the records in the system can be viewed via grids. In the grid columns, you will see an icon that looks like a funnel indicating a filter can be applied to the column. Click the icon, and a small pop-up window will appear where you can specify the filter. As shown in Figure 7-1, you even have the option to combine two filters. End users have the option utilize these filters to extract the data they want.

In addition to the grid filters, each grid is equipped with index filtering. This feature is available at the bottom of the grid, as shown in Figure 7-2.

As you can see, the arrow on each column indicates that the column can be sorted. In the previous example, the results are filtered in ascending order on the account name, identified by the arrow directing upward. The other columns show two arrows; one is pointing up, and the other is pointing down, meaning the column is not sorted yet.

All these features can be considered as the simplest form of reporting. Let’s take reporting to the next level by looking at Advanced Find views.

Advanced Find Views

Advanced Find views are the simplest form of reporting in Dynamics 365. They are useful when customers or users come to you and ask for a certain set of data based on some given criteria. The majority of these queries are for supporting daily tasks and can be implemented with Advanced Find views easily. This section will demonstrate how to create an Advanced Find view and share it with users. Let’s assume a scenario where, in our sample solution, the membership manager wants to know the upcoming subscriptions where the membership type is Silver and the subscription status is Pending. You can easily extract this information using Advanced Find.

To create the view, navigate to the Member Subscription entity and click the filter button on the main title bar of Dynamics 365. This displays the Advanced Find editor. Here you can add the conditions to filter the data. See Figure 7-3.

For this scenario, you must use the conditions shown in Figure 7-4. You simply pick them up from the drop-down lists available.

When you click the lookup button next to the filter, you will be directed to the Look Up Records window to select a specific value. See Figure 7-5.

It’s the same for the option set values; in other words, when you click the button next to the value field of the filter, you will be taken to a Select Value pop-up to select the option set values. After configuring the filters, the next step is to add the required columns. If there are any requirements to use multiple filters, then right-click the row and click the Select Row option. See Figure 7-6.

After that, you can group your conditions using either the Group AND or Group OR option available on the ribbon. Figure 7-7 illustrates the two conditions combined with Group OR. Figure 7-8 shows XXX.

Based on the content of the column, the default length may not be enough to view the content, meaning that when the query is executed, the content will be truncated. To increase the column width, click Change Properties. See Figure 7-9.

For Advanced Find views, you also can add the columns from the related entities that you have used in the query. The related entities are listed by record type. To view the output of the report, click the Results button on the ribbon. See Figure 7-10.

The next task is to share the view with stakeholders. After creating the view, you must save it. Click the Save As button in the ribbon and give a meaningful name to the view so that the users can easily locate it. Once the view is saved, the saved view will appear in the Use Saved View drop-down, as illustrated in Figure 7-11.

Click Saved Views in the ribbon, and you will be directed to list of saved views. Select the view you want share, click the Share button in the ribbon, and click Add User/Team on the left pane of the Share Saved View window. The list of users will be displayed, and you can share the view with target users. See Figure 7-12.

Now that the view is shared with the target users, they can view it in the list of views. These Advanced Find views are capable of querying related entities as well. As shown in Figure 7-13, you can build the query by drilling down to the related records.

In the next section, we will discuss how to create reports with the Dynamics 365 Report Wizard.

Creating Reports with the Dynamics 365 Report Wizard

When it comes to report security, all reports from the filtered views will filter the data based on the user’s security role. If the person executing the report does not have permission to view the report, then the user can’t view the report. By default, when the report is created, it can be used only by the owner of the report, unless it is shared. There are two settings available; the report is visible either to the organization or to the individual. Organization means that any user who is authorized to see reports within the organization can view the report, and individual means the report will be visible based on the user’s security role. For instance, if user A belongs to Business Unit A and the scope of the report is set to Individual, then any user in Business Unit A who has read privileges to the report would be able to view the report. But someone from Business Unit B who has the same privileges would not be able to see the report.

The other cool thing about reports is that they can be included in a solution, which extends the visualization capabilities of Dynamics 365. Keep in mind that the reports owned by an organization can be included in a solution. In this section, you will learn how to create a simple report for the SBMA system. The stakeholders want to view the credit card payments that are paid for a given period.

So, let’s navigate to Reports in the Marketing section of Dynamics 365. See Figure 7-14.

In the New Report window, make sure that Report Wizard Report is selected from the “Report type” drop-down. Under this field, click the Report Wizard button. You will be directed to the Get Started page where you must select the Start New Report option and click Next. In the Report Properties window, give the report a name, select the primary record type and the related record type, and click Next. Figure 7-15 shows the screen for adding the filters to select the required records.

As you can see, we have grouped the conditions using AND. First the query filters the payments and then it selects them by the related subscription due date. Once the filters are completed, on the next wizard page you can add the required columns to the report. As per the example, you must add the columns from the member payments first (see Figure 7-16).

Next add the columns from the Member Subscription entity. You can find the entity in the “Record type” drop-down. See Figure 7-17.

After adding the columns, the next step is to group the records. On the same screen, click the “Click here to add a grouping.” A pop-up to define the grouping will be displayed. See Figure 7-18.

As per the requirements, the stakeholders want to group the results by the type of membership. Once the grouping is completed, the settings will look like Figure 7-19.

Click the Next button to move to the next step to decide on the format of the report. For this example, we will be using the “Table only” format. See Figure 7-20.

Click the Next button, and the report summary will be displayed. Click the Next button, and the report will be created. The system will display a notification if the report is successfully created. Click Finish to complete the configuration, and the report details will be displayed. See Figure 7-21.

You can click the Run Report button with the lightning icon on the toolbar of the window, and the final output will be displayed, as shown in Figure 7-22.

If the stakeholders require any charts, it is just a matter of selecting the format of the report. Similar to the steps explained earlier, create the report, and for the step where the wizard asks you to select the report format, select the “Chart and table” option. You can see that this option is available only if your query has a summary field defined. For instance, if you want to show the sum of the amount due, then you can define the summary type at the point where you add the column, as shown in Figure 7-23.

As you can see, there are two options: “Show table below chart on the same page” and “Show chart.” To view data for a chart region, click the chart again. See Figure 7-24.

Like any other chart wizard, in the next step, select the chart type. For this example, a pie chart is selected. See Figure 7-25.

The final page is for specifying the slices, values, and labels. After specifying them, click the Next button. See Figure 7-26.

The subsequent wizard page shows the progress of the report. Finally, the output will look like Figure 7-27.

As per the initial settings, since the report is configured to display both the chart and the report, the data will be listed on the second page. See Figure 7-28.

Creating Fetch XML Reports

Sometimes users may request advanced reports that the out-of-the-box report capabilities do not provide. SQL Server Report is the preferred option to create such advanced reports. Previous versions of CRM supported SQL Server reports with SQL Queries. But on Dynamics 365 online, you must use Fetch XML as the queries for the data sets, because with online version, querying the SQL Database is not permitted. To create an advanced report, you must use SQL Server Data Tools, which provides the development environment in Visual Studio. As the first step, the SQL Server Report Authoring Extension must be installed on your development box to connect to Dynamics 365 as the data source.

This is where the issue pops up. That is, if you have Visual Studio 2017, then an error will display when installing the extension. The primary reason for this issue is that Visual Studio does not support the extension at the time of writing the book. The only way to overcome this issue is with Visual Studio 2015 as per the documentation: https://www.microsoft.com/en-us/download/details.aspx?id=50375 . In the System Requirements section, you can see the highest Visual Studio version is Visual Studio 2015. Therefore, install Visual Studio 2015, then Business Intelligent Development Studio, and finally install SQL Server Report Authoring Extension.

Let’s look at a quick example of listing members and subscriptions categorized under each membership type. Create a report project, and in the Reports folder of the solution, right-click the folder and click Add New Report. This example will be developed by using the Report Wizard, but you can create the report manually. See Figure 7-29.

To start with, create a report using the Dynamics 365 Report Wizard and download the RDL file. This is a best practice that will give you a good starting point. As shown in Figure 7-29, add the RDL file to the solution as an existing file after copying it to the solution folder. This approach will automatically set the data source and the data set. Open the Data Source Properties window by double-clicking the data source. On the General tab, make sure that Type is Microsoft Dynamics 365 and Fetch is selected for the “Embedded connection” option. The organization URL as the connection string is automatically set. Click the Credentials button on the left of the Connection String pane and provide the credentials to access the Dynamics 365 instance. This example is for Dynamics 365 online; therefore, select “Use a specific user name and password” and click OK.

Next open the Dataset Properties window by double-clicking the data set. In this window, you can update the Fetch query as per the requirements. See Figure 7-30.

The simplest way to create a proper Fetch XML is to use the Fetch XML Builder plugin in XrmToolBox. Once the query is created, paste it in the Query string box in the form shown in Figure 7-30 and click OK to save the data set settings. Next you can alter the grouping, as shown in Figure 7-31. Double-click the highlighted grouping to alter the grouping as per your requirements. For this example, we added the grouping when we first created the report in the report wizard.

When you update and save the Fetch XML to the data set, these parameters are added in the Parameters folder in the Report Data pane. So, when you execute the report, these two report parameters are available for the user to select the date range. Similarly, you can add parameters as you require. One thing to remember is that by default the parameter “Data type” is set to Text. You must change it to the appropriate data type; in this case, it should be Date/Time. See Figure 7-32.

Once all the configuration is completed, you can import the RDL file to the report in Dynamics 365, as shown in Figure 7-33. Since we have created a report as the starting point, you can go into edit mode and upload the RDL file to the report.

One of the powerful features of Dynamics 365 reporting is subreports. To keep it simple, this book will give a helicopter view of the feature. Let’s assume a scenario where SBMA wants to print a receipt for the members who consume different membership products of the club. So, the invoice would contain details such as the company logo, ID, member details, and so on. Plus, it will list all the membership products as the line items on the report. To accomplish this demand, you should use subreports. How it works is that the main report will have the logo, invoice ID, member details, and so on, and the subreport will contain all the membership products as the line items. First create the parent report and then create the subreport and remember to select the parent report in the Parent Report field. With this approach you can execute the report on single records as well as multiple records.

Applying prefilters makes your report perform well. Let’s take a quick look at how to apply the prefilters to your report.

Apply Prefilters

It is a known fact that reports that query larger data sets suffer from performance issues. This will continue over time since an organization’s data is growing on a daily basis. To improve the performance of the reports, you can use prefiltering in your reports, which will make your reports context sensitive by consolidating the report scope to query more relevant data. This will also make the report filtered using the Advanced Find feature.

For more information on prefiltering, please refer to https://docs.microsoft.com/en-us/dynamics365/customer-engagement/analytics/improve-report-performance-by-using-filters .

Dashboards

Among the out-of-the-box personalized reporting options, Dynamics 365 dashboards are the best because they can be developed without the help of the experts and are available to all users. The dashboards are a visual representation of the data in your Dynamics 365 instance. Primarily, a dashboard consists of views and charts, and these visualizations are interactive. That is, the end users can quickly jump into individual records from the dashboard. The primary benefit of dashboards is the aggregation of a wide array of data into a single page or a view. Plus, you have the option to control the access to the dashboards based on the end users’ security roles.

For instance, the owner of the dashboard can decide who will get to see it by just sharing it with specific users or teams. Also, you can create dashboards that suit different roles of the organization. For example, information requirements are different for the sales team and the customer services team. So, each team can have their own dashboards to carry out their daily duties. Let’s look at a quick example of a dashboard.

As with any other development work, the first step is to design the dashboard, which means draw a simple sketch of the dashboard and define where the user wants to see information and in what format, whether it is a chart or a view. Before creating the dashboard, you must first create the required views and charts. Once the foundation is set, you can start to create the dashboard. Open the solution and navigate to the dashboard under the solution components. Click the New button to add the dashboard. See Figure 7-34.

The next window is for selecting the appropriate layout for the dashboard. This is where the initial design is important because it will guide you to select the layout. See Figure 7-35.

Click Create, and the dashboard will be created. In the dashboard editing window, when you click the small icon in the middle of each component in the dashboard, you can select the view or the chart to add to each component. See Figure 7-36.

After setting up all the required components, save the dashboard by providing a meaningful name. The newly created dashboard will be listed with the other dashboards. See Figure 7-37.

Select the dashboard from the list, and you will be directed to the dashboard. See Figure 7-38.

These dashboards can be easily shared with other users. On the dashboard click Share Dashboard on the command bar, and the rest of the process is similar to sharing an Advanced Find view, which we discussed earlier in this chapter. One important thing to know is that there are two types of dashboards: system and user dashboards.

System dashboards are created by the users with system administrator or system customizer user roles. These are created in the Settings area and must be published to be visible. Although they can be visible to everyone in the organization, they can also be hidden using the security roles of the users. The system administrator or the system customizer who creates the dashboard can set it as a default dashboard, making it the one every user can see when Dynamics 365 loads. System dashboards are useful in scenarios where the information is required at a broader scope across the organization.

On the other hand, user dashboards are created by the user under the user’s work area such as Sales, Services, or Marketing. These are not required to be published to be visible, and they are visible only to the users who create them, although the owner can share them with other users. User dashboards are more suitable for quick information requirements within the team, and the users can create the dashboard and share it within the team or specific users.

The following link provides more details about dashboards: https://docs.microsoft.com/en-us/dynamics365/customer-engagement/customize/create-edit-dashboards .

In addition to these standard dashboards, there is new form of dashboards known as interactive dashboards . They provide an intuitive customer service experience that you will find within Dynamics 365 for Customer Engagement and the Customer Services hub. These dashboards can be configured to extract more relevant information for the customer service representatives to perform their job effectively and efficiently. Customer service representatives no longer have to navigate through so many pages to locate the information because interactive dashboards provide the information they required. These dashboards are fully configurable and security role sensitive, which always ensures they are exposing the relevant information to the right person.

Interactive dashboards come in two flavors: multistream and single-stream dashboards. As the name implies, the multistream dashboards present data from multiple data streams, whereas single-stream dashboards present data from a single data stream. This is just an introduction to the feature; for more information, please refer to https://docs.microsoft.com/en-us/dynamics365/customer-engagement/customize/configure-interactive-dashboards .

The next section is dedicated to creating more complex dashboards using Power BI.

Dynamics 365 and Power BI

Sometimes, the senior stakeholder, board of directors, external stakeholders, or even customers might require rich and sophisticated dashboards to use in the decision-making process. Microsoft Power BI is just the tool that can be utilized in such scenarios because it is the platform that can be easily integrated with Dynamics 365 and provide additional reporting capabilities to Dynamics 365. For more information, visit https://docs.microsoft.com/en-us/dynamics365/customer-engagement/analytics/reporting-analytics-with-dynamics-365 .

The following section covers just the tip of the iceberg of the Power BI space. It explains how to establish a connection with Dynamics 365 from Power BI, create a dashboard, and publish it to Dynamics 365. First you must sign in with Power BI. Use your global administrator credentials and sign up. During the process, you can nominate other users who might be using the platform to produce state-of-the-art dashboards. See Figure 7-39.

To establish the connection, you need to copy the link in Instance Web API URL. To develop the examples in this section, we will be using the Power BI desktop. On the ribbon, click Get Data and then More at the bottom of the pop-up menu; this will load the connection wizard, as shown in Figure 7-40.

In this window, select the Online Services option, and you will see all the online services available. From this list, click Dynamics 365 (online) and click Connect. This action will then ask you to enter the Web API URL. See Figure 7-41.

Once you’re successfully connected, the list of available entities will be displayed, as shown in Figure 7-42. Keep in mind that when you select the entity, all the columns of the entity will be selected as well. We will discuss how to limit the query to select only the required columns later in this section. You can see a subset of data on the screen.

After selecting the entities and clicking the Load button, the tool will load the entities, which will take a few minutes depending on the number of entities you have selected. By default, this will load all the columns, and you must edit the data set and select only the required columns.

The most important thing to remember here is to prefilter your data. If not, then by default, it will retrieve all the data in entities you have selected for the query. In a scenario where you are querying a large data set, you should filter the data for performance and security reasons. Generally, if the data set is large, then Power BI will take several minutes to pull all the required records, which is frustrating to the consumer. Therefore, you must always ensure to include the require data in your data set. For this book we are using a small data set. If you have a larger data set, the following URL explains how to use Fetch XML to create a query and filter data: https://crmchartguy.com/2017/09/30/use-fetchxml-in-power-bi-with-dynamics-365-customer-engagement/ . See Figure 7-43.

Once the entities are loaded, you can see the entities and the fields underneath each entity on the right side of the desktop tool. See Figure 7-44.

When you expand the tree, the columns are listed, and to edit the columns, you must open the Power Query Editor to add/remove the fields and apply any filters to the query. Select the entity you want to edit, and from the pop-up menu, select the Edit Query option to open the Power Query Editor. See Figure 7-45.

On the ribbon of this screen, click the Choose Columns button to select the columns that you want. As you can see, the column names have been renamed to a more readable format. You can do this just by right-clicking and selecting the Rename option from the pop-up menu. See Figure 7-46.

When there are related entities, by default the word Record is displayed, which is not useful for the end users. Next to the column header there is a button with two arrowheads, and when you click this button, the lookup entity columns are displayed. You can select the column that lists a meaningful name. For instance, instead of displaying a GUID, you could select the name, which will display the name of the member. See Figure 7-47.

As you can see in Figure 7-48, the query completed after adding all the changes.

Click the Close and Apply buttons in the ribbon, and your changes will be submitted to the query. It is now ready to be used in producing the dashboard. Just drag and drop the columns to the canvas. By default, the details are listed as a table. See Figure 7-49.

To apply the chart visualizations, you must first prepare the data. In the visualization shown in Figure 7-49, the pie chart displays the memberships based on the membership type. For this, you must go to the query that pulls the membership type data, and in the column that illustrates the relationship between the membership type and the members, click the button next to the column heading. In the pop-up menu, select the Aggregate option, and it will list all the available options. These options include Sum and Count options. For this example, select “#Count of accounted,” which will count the accounts under each type. After completing the query, then add the changes, add the table to the canvas, and select the type of visualization required. The changes will be applied immediately.

When it comes to querying option sets and status fields, by default it will display only the integer value stored. But if you use the Common Data Service, you do not have to write any additional queries to retrieve the label. The following URL will guide you through the process of creating such a connection and adding option sets: https://community.dynamics.com/crm/b/crminogic/archive/2018/10/19/connect-to-the-power-bi-using-common-data-service-cds . If you are not using the Common Data Service, then you can also follow the steps defined in this article: https://nishantrana.me/2018/10/06/dealing-with-optionset-inside-powerbi-in-dynamics-365-ce/ . See Figure 7-50.

As per the query editor, the member payments are grouped under Payment Type, but the issue is that the payment type is an option set, and the numbers will be displayed instead of the name values. The workaround used here is to change the data type of the payment method to text and replace the value with the appropriate text value.

Also, you can insert custom columns on the Add Column tab on the ribbon. In the editor window, you can combine the columns and develop the formula. The custom column is calculated by multiplying the subscription fee by the number of subscriptions under each subscription. See Figure 7-51.

As shown in Figure 7-52, you can add these to the canvas, and the final output contains all the components you have configured.

Next we will quickly publish our dashboard to Dynamics 365. First, you must enable Power BI visualizations for your organization. Under Settings ➤ Administration ➤ System Settings, click the Reporting tab, and select “Allow Power BI visualization embedding.” You must avoid adding Power BI visualizations to system dashboards because it is not supported at the time of writing this book.

Next publish the dashboard to the Power BI workspace. Select File ➤ Publish, and select Publish to Dashboard. See Figure 7-53.

Keep in mind that when you publish the dashboard, it will be published as a report. You must select the report on the Reports tab of the workspace and then click Pin to Live Page. There are two options, so select the one appropriate. See Figure 7-54.

The option will now be available in the Dashboard section, which means it can be reached from Dynamics 365. From the Dynamics 365 instance, click the New Dashboard icon and select the Power BI Dashboard option. It is amazing to see that the pop-up will give you a quick view of the dashboard you have selected. See Figure 7-55.

Once you save the selection, the dashboard will be rendered to Dynamics 365, as illustrated in Figure 7-56.

Even these visualizations can be added to Dynamics 365 dashboards. In the dashboard editor, click the Power BI icon to select the component on the dashboard. If you want to share the dashboards with the users within the team, you must configure the sharing options in both Power BI and Dynamics 365 for Customer Engagement. Also keep in mind that the user group must have the same access rights on both services. For more information about sharing, please refer to https://tjb2008.crm6.dynamics.com/main.aspx?forceClassic=1#579876531 .

You could also find more details about the security implications via https://docs.microsoft.com/en-us/dynamics365/customer-engagement/basics/add-edit-power-bi-visualizations-dashboard#privacy-notice . See Figure 7-57.

As mentioned earlier, this is just the tip of the iceberg of what you can do with Dynamics 365 and Power BI integrations. One such approach is to use the Data Export Services, which is a much faster approach when you have to query huge volumes of data. You can find additional information at https://docs.microsoft.com/en-us/power-bi/service-connect-to-microsoft-dynamics-crm .

Summary

This chapter covered the simplest data reporting to more advanced reporting options available in Dynamics 365. It started with Advanced Find views and gradually moved to the Dynamics 365 Report Wizard and then to Fetch XML reports. Finally, we covered the big beast, Power BI integration.