Transforming Data

In our demonstration, we’ll build a chart that shows a count of issues by property type. To produce this chart, the chart control requires us to provide aggregated data. Figure 19-3 shows the format of our source data and the target format that the chart control requires.

This example highlights the challenge we face when we attempt to build charts that are based on real-life, relational sets of data. Here, each issue record is associated with a property record, and each property record is associated with a property type record. To produce our target output, we need to build a query that spans three tables. How do we do this?

With SharePoint and Excel, this can be a challenging task. It is practically impossible to write a formula that aggregates data without hitting query delegation limits. This prevents us from retrieving accurate results when the source data exceeds 2000 rows. With these data sources, the easiest way to prepare a data source is to pre-aggregate the data into a separate list or spreadsheet, using Microsoft Power Automate. An alternative solution is to avoid the use of the native charting capabilities and to use Power BI instead.

SQL Server is the best data source for retrieving and aggregating data. This is because we can build a SQL Server view that joins multiple tables and produces our aggregate result. We can then use this view as the data source for our chart. SQL Server performs very quickly, especially if the source database is optimized and indexed correctly.

Dataverse doesn’t provide the same low-level access to data that we can accomplish with SQL Server. However, it does provide features that can help facilitate the construction of charts. These include calculated and rollup fields. It also offers great query delegation support, including support for aggregate operators such as the Sum, Min, Max, and Average functions.

In our example, we’ll use a Dataverse data source, and we’ll base our chart on our issue table. Because we want to count the issue records by property type, we need to make the property type description visible through the issue table. To accomplish this, we’ll create calculated fields in the property and issue tables (Figure 19-4). We can refer to Chapter 12 for additional help on how to carry this out.

Even with Dataverse, it can be tricky to transform data in the ways that we require. For example, calculated and rollup columns can span only one relationship. This is the reason why it’s necessary to create two calculated columns (one on the property table and one on the issue table), rather than one. In a formula, the GroupBy function cannot group data by a lookup column; and therefore, it isn’t possible to use this function to aggregate data across a relationship. In many cases, it can be much simpler or necessary to pre-aggregate data into a reporting entity for the purpose of building a chart. We can use Microsoft Automate or dataflows to carry out this task.

Building a Column Chart

To build a column chart, we use the insert menu to add a column chart control to a screen. All three chart controls (column, pie, and line) are composite controls that consist of three items: a chart, title, and legend.

Note that with this formula, crc51_propertytype refers to the name of the calculated field that contains the property type description. It’s necessary to pass the logical field name to the GroupBy function , hence this name.

If we choose to set the data source of a chart to a collection, it’s important to build the collection before we open the screen that houses the chart control. If we build the collection using a formula on the screen’s OnVisible property , the chart control will not render the collection data due to timing issues.

Figure 19-5 shows the appearance of the chart control in the designer. Through the properties pane, we can set cosmetic attributes that include

• X label angle – By default, the labels on the x axis are slanted by 60 degrees to better accommodate charts with multiple items on the x axis. We can use this property to change the angle or to format the label text so that it appears horizontally.

• Grid style – This property controls the display of gridlines on the chart canvas. The available options include All, None, X only, and Y only.

• Markers – A column chart can display the numeric value above the bar. The markers property controls the visibility of this marker.

• Marker suffix – We can use this setting to append a label that follows the marker value.

• Items gap – This setting defines the space between columns in the chart.

• Series axis min/Series axis max – We can use this setting to define the maximum and minimum values for the y axis.

Setting Legends

By default, the chart label displays the name of the series. In our example, it makes more sense to display the names of the property types. To set this up, we select the legend control and set the Items property to the same value of the Items property for our chart. We then set the Value property to the name of the column that contains the property type description values (Figure 19-6).

Applying Colors and Styles

The chart control applies a default set of colors which are defined through the ItemColorSet property, as shown in Figure 19-7. This property contains an array of colors, and we can modify these values to change the colors of the items in the chart.

Building a Pie Chart

The process to create a pie chart is mostly identical to the column chart. We add a pie chart control to a screen and use the Items property to set the data source. Figure 19-8 shows the appearance of a pie chart control, using the same data source as our previous column chart example.

Building a Line Chart

In this section, we’ll look at how to build a line chart and how to configure the chart to show multiple series.

Showing Multiple Series

The two chart types that can display multiple series are the column and line charts. Both these chart types support up to a maximum of nine series. To demonstrate this feature, we’ll build a line chart that displays two series: a count of the total number of issues by property type and a count of the total number of open issues by property type.

The most difficult part of this is to build the formula that defines the data source for our chart. We can use the techniques from earlier in the book to derive the following formula:

AddColumns(Distinct(Issues,PropertyType),

           "PropertyTypeCount",

           CountIf(Issues,PropertyType=Result),

           "OpenIssueCount",

           CountIf(Issues,CloseDate<>Blank() And PropertyType=Result)

)

This formula returns the distinct property types from the issue entity. It utilizes the AddColumns function to add columns that count the issues for each property type and issues where the close date value is not blank. This produces the output format that is shown in Figure 19-9.

To build our chart, we set the Items property to our formula, set the “number of series” setting to 2, and use the settings in the advanced section to configure the series (Figure 19-10).

The Labels property defines the field that appears on the x axis. We set this to Result. This is the name of the field that the Distinct function returns, and the value of this field contains the distinct property type values. We then set the Series1 property to PropertyTypeCount and the Series2 property to OpenIssueCount.

Using the Designer

Model-driven apps work exclusively against Dataverse data sources, and charts are defined at the table level. To build a chart, we open the table designer and select the Charts tab. From here, we can create a new chart and edit it using the chart designer as shown in Figure 19-11.

The icons in the ribbon bar menu enable us to select one of the eight chart types. These include the column, bar, area, line, pie, funnel, tag, and doughnut chart types.

The process to create each chart type is almost identical. We configure the series and category values in the same way. The primary difference is to choose a different chart type from the ribbon bar menu. Because of the very close similarities, we will focus only on the bar chart type.

A key feature of the bar, column, and area chart types is that we can choose stacked and 100% stacked variations of the chart. We use a drop-down button beneath the chart type button to make this selection.

Next to the chart type buttons are buttons to define the top/bottom rules. These enable us to build a chart to retrieve the top X or bottom X records from the data source.

The main part of the chart designer enables us to name our chart and to choose a view to preview the chart in the designer.

The series and category settings define the data in our chart. Our example illustrates a chart that displays a count of issues by property type. The “Legend Entries (Series)” drop-down enables us to select a field from the issue table, and an adjacent drop-down enables us to select an aggregate function. In this, example we select PropertyType and “Count:All.”

Beneath the Series drop-down, we can use the category drop-down to define the field that appears on the horizontal axis. In this example, we choose property type. As we build our chart, we can see a preview of the output in the middle part of the designer.

Combining Charts

To demonstrate how to combine charts, here’s how to build a chart that shows the maximum duration of an issue and the count of issues, grouped by date.

We define our first series by selecting the duration field and choosing the Max aggregate operator. We can add another series, and we can select the property type field, and we can choose the “Count:All” aggregate operator. We can use the icons next to the aggregate drop-down to select the chart type. In this example, we choose the line chart type to show the maximum duration and column chart type to display the count of issues.

To group our results by date, we can define a category that includes close date and month (Figure 19-13).

Displaying Charts

There are two primary ways for a user to view a chart – through a table view or through a dashboard. Figure 19-14 shows the view of the issue table in a model-driven app.

The user can click the “Show chart” button in the header section to display the chart panel. From this area, the user can use a drop-down to display the charts that are associated with the table.

For each chart, the context menu provides the option to expand the chart, to refresh the chart, or to export and import the XML chart definitions.

Displaying Dashboards

A great way to utilize charts in a model-driven app is to create an interactive dashboard. Chapter 13 introduced this topic, and in this section, we’ll revise the steps that we use to add a chart to a dashboard.

The two dashboard types are the classic dashboard and the interactive dashboard. The main feature of an interactive dashboard is that the user can click a chart area to filter a list (or stream) of data that appears beneath the chart area.

To create an interactive dashboard, we edit our model-driven app and select the menu option to create an interactive dashboard. In this example, we’ll create a multi-stream, two-column overview dashboard. This opens the designer as shown in Figure 19-15.

In the top part of this designer, we can provide a name for the dashboard and select the source entity (a Dataverse table) and view for the dashboard. A useful pair of settings are the “Filter By” and “Time Frame” drop-downs. This configures the dashboard so that the user can filter the data in the charts and dashboard by a time frame. The “Filter By” drop-down enables us to select a date field from the entity, and the Time Frame drop-down provides values that include today, yesterday, this week, last week, this month, last month, last quarter, and month to date.

In this example, we’ll add our two charts to the dashboard, and we’ll add the active issues view to the stream section beneath the charts.

After we create the dashboard, we need to add a link to the dashboard in the site map of the app. To deploy the app, it’s necessary to publish the site map. It’s also necessary to publish the dashboard. Without doing this, the dashboard will not appear in the list of available dashboards that the user can select.

There is currently no option to publish a dashboard from the dashboard designer, and therefore, we need to publish the dashboard from elsewhere. One way to do this is from the Default Solution (or to create a solution for our app, which we’ll cover in Chapter 26).

To publish a dashboard from the Default Solution, we select the Solutions menu from the Maker Portal and open the Default Solution. From here, we can search for our dashboard and publish it using the menu item in the context menu (Figure 19-16).

Figure 19-17 shows the appearance of the dashboard at runtime. The “Show Visual Filter” and “Hide Visual Filter” buttons in the header control the visibility of the charts.

The user can use the date period drop-down to filter the records in the dashboard by date. The user can also click segments in either chart to filter the issue records in the lower part of the dashboard.

Customizing XML

The chart designer provides a simple editor. There are many chart settings that are inaccessible through the editor. These include settings that relate to the color scheme, labels, tooltip, legend text, font styles, gridline, and axis. From a data perspective, the chart editor also provides limited data retrieval and filter capabilities. Fortunately, we can access all of these hidden settings by editing the XML definition of the charts.

To demonstrate, we’ll modify the combined line and column chart that we created earlier. By default, the series and legend labels appear in this format: “Max (Duration),” “Count:All (PropertyType(Property)).” We’ll replace these labels with text that is more user friendly.

The chart that we created earlier also appears odd because we group the issue records by “close date” and there are records without a “close date.” We’ll modify our chart to filter out records without a “close date.”

To carry out this task, we can retrieve the chart XML and modify it manually with a text editor. This method can be very arduous, and fortunately, there are third-party tools that can greatly simplify this task. The tool that we’ll use here is the “AdvancedChartEditor,” which we can obtain through the XrmToolBox.

Figure 19-18 illustrates the appearance of this tool. It provides a “Load Entities” button to retrieve a list of entities (tables). We can then select an entity to show the charts that belong to an entity.

From the chart list, we can select a chart and click the “Edit Chart” button. This opens the chart editor as shown in Figure 19-19.

There are two main parts to this chart editor. The data description textbox specifies the data source for the chart in FetchXML format. This is the same FetchXML language that we encountered when we built custom queries for a portal app (Chapter 14).

The “Presentation Desc” textbox shows the XML that defines the chart content. We can click the “Open Visual Editor” button to edit the chart definition in a form. The Preview button opens a visual preview of the chart, and it provides a useful way to verify the changes that we make. When we complete our changes, we can click the “Update & Publish” button to deploy our changes. For future enhancements and customizations, we can use this same process to amend the chart XML and to republish our changes.

Customizing the Chart

Figure 19-20 shows the form that opens when we click the “Open Visual Editor” button. We can choose the areas of a chart that we want to modify by using the tree node that appears in the left-hand panel. The high-level groupings include series, chart areas, titles, legends, and annotations.

To modify the label text that appears against each axis, we can select the relevant axis, and we can use the title setting to define the label. In the axis area, we can also define the minimum and maximum values of the axis, and we can also modify the font and color settings.

To modify the labels that appear in the legend, we use the legend text setting that appears beneath the Series node.

As we use the chart editor, we discover that there are many visual settings that we can customize. For example, we can give chart areas a 3D effect, we can change the appearance of the columns in a column chart so that they appear as cylinders, and we can even select 20+ additional chart types which include bubble, Kagi, polar, radar, Renko, pyramid, and more.

It’s important to note, however, that these chart types and many of the additional presentation settings do not work in model-driven apps. It is possible that future releases will support these features. The reason why these settings exist is because the older web client from Dynamics 365 supports these features.

Customizing the Data Source

To customize the data source for a chart, we can modify the FetchXML query for the chart. The ability to specify a query with FetchXML provides great benefits. We can define filter, group, and sort data in ways that are not possible using the chart editor. We can also join other tables and produce aggregate results including the sum and count of a grouped set of records. We can define “outer joins” between tables to return records that do not exist (e.g., customers who have not made an order).

In this chapter, we’ve seen how to use computed and rollup columns to retrieve the necessary data for a chart. The disadvantage of this technique is that we can easily clutter our tables with columns that serve little purpose elsewhere. By defining the data source of a chart with FetchXML, we can keep our tables tidier.

Building a FetchXML query can be complicated. To simplify this task, we can use third-party tools. The one that we’ll use here is “FetchXML Builder” for XrmToolBox, by Jonas Rapp.

Figure 19-21 shows the layout of the “FetchXML Builder” designer. We use the “Query Builder” node on the left-hand side to build a query. The context menu in this section enables us to add attributes, filters, and link entity. We use a link entity to create a join to another table, and we can define the join as an inner or outer join.

Useful features include the ability to view the FetchXML syntax that we build. We can also click the “Execute (F5)” button to run the query and to view a tabular representation of the results. This can help verify that our query is correct. There is also the option to display the SQL equivalent of the query. This can assist app builders who are more conversant with SQL.

In our example, we want to filter the source of our chart to exclude records without a close date. We can customize our query in the editor and use the result to amend the data description of our chart through the “AdvancedChartEditor.”

When we finish editing our chart, we click the “Update & Publish” button to apply changes. Figure 19-22 shows the final result of our changes.