Lesson 5 – Adding data visualisations to your dashboard

Introduction

In this lesson you will learn what data visualisation objects are available in inmydata dashboards, and how to add and edit them.

Learning Objectives

When you complete this lesson you should be able to:

• Understand the different visualisation types available in a dashboard.
• Add different visualisations to a dashboard.
• Edit visualisations on a dashboard.

Prerequisites

Before you begin this lesson you should be able to:

• Install the inmydata client applications.
• Open the dashboard designer.
• Understand the dashboard designer interface.
• Create and open dashboards.

Managing dashboard objects

Dashboard objects are the objects that provide the visual display and control elements of the dashboard. The objects can be grouped into five broad categories, each of which is described in the following sections.

Data and Control Objects

The Data and Control objects contain data listing and control objects which allow you to filter the data on the dashboard.

The control panel is a container object for labels, query parameters, filter parameters, images and refresh buttons. The control panel will be described in detail later in this lesson.

The tree view selection panel allows the user to apply complex filtering to a dashboard using a simple tree view interface. The tree view selection panel will be described in detail later in this lesson.

The pivot table is a tabular representation of data that can automatically sort, count, total or give the average of data quickly creating unweighted cross tabulations. The pivot table will be described in detail later in this lesson.

The data grid allows you to display a simple listing of data in a dashboard. It also gives the user the ability to group, summarise and sort the data. The data grid will be described in more detail later in this lesson.

Single Series Charts

Single series charts are charts that display data visually by mapping a single set of textual values (series) against a single numeric value.

This is in the form of a circular area chart (pie and doughnut charts).

Use this type of chart when you are interested in the value of a single variable across a single group. Two examples might be plotting the value of sales by sales representative, or the value of sales each month this year.

Multi Series Charts

Multi series charts are charts that display data visually by mapping one or more sets of textual values (series) against one or more numeric values. This is in the form of a two dimensional chart where the numeric values are plotted on a y axis against the textual values on the x axis.

Use this type of chart when you are interested in the value of one or more variables across one or more groups. One example might be plotting the values of sales and the value discounted by sales representative. Another might be plotting the value of orders shipped, and the value of orders delivered in each month this year.

Stacked Charts

Stacked charts are a variation on multi-series charts that display data visually by mapping one or more sets of textual values (series) against one or more numeric values. This is in the form of a two dimensional chart where the numeric values are plotted on a y axis against the stacked textual values on the x axis, giving not only an indication of the individual values for each series, but also an indication of the summed values across all series.

Use this type of chart when you are interested in the value of one or more variables across one or more groups, but also wish to see the summed total of the different values. One example might be plotting the total order value for each month, but seeing the total order value broken down by the order status.

Combination Charts

Combination charts are a variation of multi-series charts and stacked charts that allow different types of values to be displayed on a single chart. The different types of values can be differentiated by giving them a different visual appearance, for instance displaying them as a column, line or area. In addition, different values that have a significantly different scale can be plotted on a second Y axis, allowing them to be scaled independently to fit neatly on to the chart.

A good example of a combination chart might be to plot the value of orders and quantity ordered each month.

Scroll Charts

Scroll charts are a variation on single series, multi-series, stacked and combination charts that cater for a large number of series values by scrolling or allowing zooming.

Spark Charts

Spark charts allow you to plot a single value against one dimension, for a range of group values. The charts are designed to give a quick and visual indication of how a value is trending for each group value. For instance, you may use a spark chart to give a quick visual representation of how sales are trending in several stores.

Gauges

Gauges are objects that show a single value. Gauges are useful when you have a single value that you want to be measured quickly against a particular range. Some gauges will allow you to show the value against a number of coloured ranges (such as a traffic light or dial), and others will allow you to show the value only against a total range (such as a cylinder or thermometer).

A good example of when a gauge may be useful is to show the total ordered value against the target, for a specific month or year.

Adding dashboard objects to your Dashboard

You can add dashboard objects to your dashboard in one of two ways, by dragging the required object from the Toolkit docking window or by selecting the required object from the Add button of the Dashboard Objects group of the Edit Dashboard tab on the ribbon. Both these methods are described in more details below.

Adding dashboard objects from the toolkit

Using the toolkit to drag a new object on to the dashboard has the advantage of allowing you to decide where the new object is placed in the tab, relative to the other objects already present. To add an object by dragging it from the toolkit, follow these steps:

1. Press and hold the left mouse button over the appropriate object in the Toolkit docking window.
2. Move the mouse until it is over the guide icon that represents the location you want to place the new object (the guide icons are explained in more detail below).
3. Release the left mouse button.
4. Define the details of the dashboard object in the Dashboard Object Definition dialog box (explained in more details below).

When you are dragging a new dashboard object on to the chart, the mouse cursor will change to the chart dragging cursor ( ). As you drag the mouse over the tab, two types of guide icon will appear.

Adding dashboard objects from the ribbon

Using the Add button of the Dashboard Objects group of the Edit Dashboard tab on the ribbon to add objects to the dashboard has the advantage of allowing you to select the object type from an ordered list of objects. To use the ribbon to add a dashboard object to the dashboard, follow these steps:

1. Press the Add button in the Dashboard Objects group in the Edit Dashboard tab in the ribbon.
2. Select the type of object you wish to add to your dashboard from the list.
3. Define the details of the dashboard object in the Dashboard Object Definition dialog box (explained in more details below).

Defining the details of objects in the dashboard

The Dashboard Object Definition window allows you to specify the appearance and behaviour of dashboard objects that have been added to your dashboard tabs. The window is opened when you add a new dashboard object to your dashboard, or select the Edit button in the Dashboard Objects group in the Edit Dashboard tab in the ribbon. The table below gives an overview of the different tabs in the window, which will be described in more detail below:

Defining data for a dashboard object

Follow the steps below to specify the data;

1. First select the Query data set that you wish to base your dashboard object;
   
2. Select the calendar you wish to be used to derive any date groupings (such as year, period, week etc).  
3. If you wish to, press the filter button to apply a filter to your data set (applying filters will be described in more detail below).
4. Next, decide how you want to group your data, and select the appropriate option;
       The three options are
   1. On Change of:
       Create a record for each distinct combination of the group values selected.
   2. For All Records:
       Creates a single record regardless of the group values selected.
5. Add the group columns () you require (some object types, such as single series charts, will only allow a single group column).
6. Next, select which summary columns () you require (some object types, such as single series charts, will only allow a single summary column). 
7. For each summary column, select the summary operation you wish to be applied. Distinct count is the number of distinct values in the column for the group selected. Some summary columns will be calculations, rather than values that exist in the dataset. For these columns, the only summary operation

Managing group options

The group options dialog allows you to manage how groups are collated and sorted. To open the Group Options dialog box, follow these steps:

1. Select the group you want to manage in the Data tab of the object definition dialog.
2. Press the Group Options button.
    

Once the Group Options dialog is open, you can change the group you are managing by selecting the appropriate value in the Group dialog box.
 

Managing how groups displayed

For multi-series charts, such as line charts or column charts, you can select whether a group is a category or a graph. A category is used to build the groups that are plotted along the main axis (usually the x axis). A graph is display as a separate element in the chart, for instance a separate line or column.

Managing how groups are collated

The Group Selection tab in the Group Options window allows you control how group values are collated. This can change the number and value of the groups created. If your group value is a date, or date-time value, you can choose which time interval is used to create your group. Follow these steps to define data and date-time groups:

Select the time interval you want the group to be created from.
 

Often, there may be too many group values to fit comfortably on the chart, and you may only be interested in the groups that have either the largest or smallest values. To resolve this, you can limit the number of group value using one of the group limiting options. The table below describes each of these group limiting options:

To apply one of the group filtering options, follow these steps:

1. Select one of the group limiting option (top n, bottom n, top percentage, bottom percentage)
2. Select the summary column that will be used as the value to determine the top or bottom values.
3. Enter a value for the number of groups (N) or percentage.
4. If you want to include all other groups summarised into a single group on the chart, select Include Others and enter a label for the extra group.
5. If you want to include ties, select the include ties option. For instance, if you select top N, where N is 5, and the values of group 5 and 6 are identical, the chart will include 6 groups.
    

Managing the sort order of Groups

The Group Sorting tab in the Group Options window allows you to define the order in which the groups are shown in the dashboard object. You can sort each group based on the group value, or one of the summary values. Follow these steps to define the sort order:

1. First, select which column the group sorting is going to be based on. This may be the same column that the group itself is based on, or a summary column.

1. Select how you want the sort column value to be applied to sort the group. The options are described in the table below:

Once you have defined a specified order, you can change the order by dragging the columns up or down in the list.

Defining the appearance of your dashboard object

The Dashboard Object Definition window Appearance tab allows you to specify settings that will govern the appearance of the dashboard object.

To define the appearance of objects on your dashboard, follow these steps:

1. Select the type of chart or legend you wish to display.
2. Enter a title and second title (the second title will appear on a second line).
3. Select if you want point labels on the chart, whether those labels should (if possible) be drawn inside the chart, whether they should be shown with vertical text, whether to show a legend on the chart.
   
4. For line and area charts, you can select whether the chart should use spline calculations to smooth the line between the points, or if not selected, simply draw a straight line between the points.  
5. Line and area charts will also let you define how to handle zero values. The options are described below:

Defining the numeric format for dashboard objects

The numeric format tab of the dashboard object definition screen allows you to specify the numeric format of numeric values in a data grid, and the point labels and (if they have not been defined separately) the labels on the Y axis of your charts. Follow these steps to define the numeric format for the dashboard object:

1. If you are configuring a dashboard object with a dual Y axis, you can specify a different numeric format for each Y axis. As such, you need to select which Y axis you are specifying the details for.
   
2. If you want to format the numeric values, select the format option.
3. Select the maximum number of decimal places you want to show.
4. Select whether you want to force trailing zero (for instance, the values are currency and you always want two values after the decimal point).
5. Decide what scaling units to apply to your numeric values in a dashboard object. Scaling provides notation for large values, so for instance 1340 can be displayed as 1.34K, and 2345263 can be displayed as 2.35M. There are a number of scaling units pre-defined for you, however if these do not fit your requirements you can define your own.
   To define your own scaling units, select other from the scaling units list. Next supply a comer separated list for the scaling units and the scaling values. Each entry in the scaling units list will be displayed as a suffix to your values. Each entry in the scaling values represents the amount the value will be divided by from the previous scaling unit to achieve the display value.For instance, you could set your scaling units to "Thousands,Millions,Billions" and your scaling units to "1000,1000,1000". In this example, 1340 will be displayed as 1.34Thousands, and 2345263 will be displayed as 2.35Millions.
    If the scaling units list has one more entry than the scaling values, units will be given the first entry in the list as a suffix. For instance, for time scaling units, you could set your scaling units to "minutes,hours,days,weeks,years" and your scaling values to "60,24,7,52".
6. If required, select a currency symbol (value that will be displayed before your numeric values). If the value you want is not in the list, select other and enter your desired value. You can also enter a number suffix, a string that will be displayed after numeric values.

Managing the X Axis for dashboard objects

The Dashboard Object Definition window X Axis tab allows you to specify settings that govern the appearance of the X axis on the dashboard object. Follow the steps below to specify the X Axis details on your dashboard object;

1. Enter a title for the X Axis.
2. Select the orientation of the labels on the X Axis.
   
3. If you wish to trim the length of the labels on the x axis, follow these steps;
   1. Check the checkbox labelled Limit the length of labels on the x axis
   2. Enter the maximum length of the label
   3. Select whether to trim extra characters from the start, or end of the label.

Managing the Y Axis for dashboard objects.

The Dashboard Object Definition window Y Axis tab allows you to specify settings that govern the appearance of the Y axis on the dashboard object. Follow the steps below to specify the Y Axis details on your dashboard object;

1. If you are configuring a dashboard object with a dual Y axis, you can specify a configuration for each Y axis. As such, you need to select which Y axis you are specifying the details for.
   
2. Enter a title for your y axis. If you want, you can specify an alternative number of decimal places for this Y axis, that will override the value specified in the Numeric Format tab.
3. If all the values to be plotted on a Y Axis are either positive, or all negative, by default the Y Axis will always have a minimum value (or maximum if all your values are negative) or zero. If all your values are well above (or well below) zero if might be sensible to uncheck the "fix minimum value as zero" option so it is easier to differentiate values plotted against your Y Axis.

Alternatively, you can override that automatic calculation of the Y Axis range, by selecting that Y Axis values should be fixed, and specifying the minimum and maximum values. (NB. If the values in your chart cannot be plotted on the range you have given, the range will be extended to compensate).

Managing drill downs for your dashboard object

A drill down allows the user to click on an area of a dashboard object, to open another display that shows more detail. Unless you specify otherwise, when the user clicks on an area of a dashboard object, they will be asked to select a dimension in the dataset the object uses (or a linked dataset). The platform will then generate a drill down based on the parent object, showing detail broken down by the dimension selected.

You constrain the drill down functionality by defining a drill down path. Follow these steps to define a drill down path:

1. Select the define a specific drill down path. If you select this option, and do not add any visualisations to the drill down path, the user will not be offered any drill down from this object.
2. Press add to select an existing visualisation to add to the list.
3. Repeat step 2 to complete the drill down path.  

Drill downs are only available once the dashboard has been published.

Adding tree view selection panels

Tree View Selector Panels allow users to quickly apply complex hierarchical filtering to objects on a dashboard. Follow these steps to add a tree view filter panel to your dashboard:

1. Drag a tree view panel from the toolkit on to your dashboard, or select Tree View Selection Panel from the list that appears when you press the Add button in the Dashboard Objects group of the Edit Dashboard tab.
2. Define the tree view selection panel settings in the Tree View Selection Panel window (described in more detail below).

Defining data for a tree view selection panel

The Tree View Selection Panel window Data tab allows you to specify the data that will be used to display and build the filters for your tree view selection panel. Follow these steps to specify the values that will appear in the tree view:

Follow the steps below to specify the data;

1. First select the Query data set that you wish to base your treeview;
    
2. Select the calendar you wish to be used to derive any date groupings (such as year, period, week etc).  
3. If you wish to, press the filter button to apply a filter to your data set (applying filters will be described in more detail below).
4. Select a column in the Available Columns list, and press the Add Column button to add the column as the root group item.
5. Select a different column as the child filter column in the Available Columns list, and press the add child column button ().
6. Repeat the steps above to add all the columns you require.
7. If you wish to, you can change the way group values are grouped, sorted and displayed by selecting the group options button to open the group options window.

Defining the appearance of a tree view selection panel

The Tree View Selection Panel window Appearance tab allows you to specify settings that will govern the appearance of the tree view selection panel. Follow the steps below to specify the appearance of your dashboard object;

1. Enter a title for your tree view selection panel.
2. By default, nodes will remember their checked state when you publish a dashboard. However, if the underlying data changes, you can select whether new nodes are checked when added to the treeview.

Defining which objects a tree view selection panel applies to

The Tree View Selection Panel window Objects to filter tab allows you to specify which objects on your dashboard the panels filter will be applied to.

The Objects to filter tab contains a root node for all tabs in your dashboard that contain at least one object that has the same columns as the tree view filter panel. Each root node contains a child node for each dashboard object in that tab that has the same columns as the tree view filter panel.

Follow the steps below to apply the filter to objects on the dashboard;

Alternatively, to select all the objects on a single tab, select node that represents the tab.

Adding a Pivot Table

The Pivot Table editor window allows you to specify the appearance and behaviour of pivot tables on your dashboard tabs. A pivot table is a tabular representation of data that can automatically sort, count, total or give the average of data quickly creating un-weighted cross tabulations.

Defining the data for a Pivot Table

Follow the steps below to specify the data;

1. First select the Query data set that you wish to base your dashboard object  
2. Select the calendar you wish to be used to derive any date groupings (such as year, period, week etc).
   
   

1. If you wish to, press the filter button to apply a filter to your data set. Next, decide how you want to group your data, and select the appropriate option;
   
   
2. Next, add dimensions () to the row and column groupings you require. You can do this by selecting the appropriate available column and pressing the add button to the left of the row(s) list or the add button to the left of the column(s) list, or simply dragging columns from the available columns list to the row(s) list or column(s) list.
   

1. If you wish to, you can change the way group values are grouped, sorted and displayed by selecting the group options button to open the group options window.
   
2. Next, select which metrics you require as summary columns.
   
   
3. For each summary column, select the summary operation you wish to be applied. Distinct count is the number of distinct values in the column for the group selected. If you select distinct sum, you will need to select a dimension to define the distinct values. Finally, if the column is calculated, rather than existing in the dataset, you will only be offered the summary type calculated.
   

Defining totals and differences for a pivot table

The totals and differences tab of the pivot table dialog allows you to define the appearance and behaviour of total and difference rows and columns for the pivot table.

Follow these steps to define the appearance totals for the pivot table:

1. Check the appropriate checkboxes to display row and/or column totals as required.
   
2. If required, change the label of the total row and columns.

Difference columns display the difference between a column and the preceding column. Follow these steps to show difference columns:

1. Select a column
2. Select the type of difference you want to show. The options are;

Defining the appearance for a Pivot Table

The Pivot Table editor window Appearance tab allows you to specify settings that will govern the appearance of the pivot table.

The available options are:

Defining the Numeric Format for a Pivot Table

The Pivot Table editor window Numeric Format tab allows you to specify settings that will govern the formatting of numeric information of the pivot table.

You can specify a different numeric format for each summary value in the pivot table. As such, you need to select which summary you are specifying the details for.

Next, select the maximum number of decimal places you want to show. If your values are currency values, select force trailing zeros.

Finally, if you require it, select a currency symbol (value that will be displayed before your numeric values). If the value you want is not in the list, select other and enter your desired value. You can also enter a number suffix, a string that will be displayed after numeric values.

Adding a Gauge

The Gauge Editor window allows you to specify the appearance and behaviour of gauges that have been added to your dashboard tabs.

The editor window consists of the following windows;

Specifying Data for your Gauge

The Gauge Definition window Data tab allows you to specify the data that will be used to render your gauge.

Follow the steps below to specify the data;

First select the Query data set that you wish to base your dashboard object;

If you wish to, press the filter button to apply a filter to your data set.

Next, you need to define the value that the gauge will display. First, select the column that represents the value that will be displayed. Next select the summary operation that will be used to calculate the value for the gauge. Finally, if you want the values on the gauge to be displayed as a percentage, select the percentage button. ()

If the gauge object you are defining supports colour ranges, you can choose to allow the minimum and maximum values for each colour range to be calculated automatically, or set manually. If you opt to allow the colour ranges to be automatically calculated, each colour range will represent exactly a third of the range for the whole gauge. 

To define the range for the whole gauge (the minimum and maximum values that will be displayed on the range), first select the Range for the whole gauge option in the list of ranges.

 Next we need to define a value for the minimum and maximum values. The value can be a fixed value, or a dynamic value derived from one of the columns in the data set.

To set a range value to a fixed value, select Fixed Value, then enter the value.

To set a range value to be dynamic, select the column, then the summary type that will be used to calculate the range value.

Just be aware, if the value the gauge displays is smaller than the minimum value you have defined, or larger than the maximum value you have defined the range will be automatically extended to allow the value to be represented on the gauge.

Next, if the gauge object we are defining supports colour ranges, we can choose whether to show each colour range and define how it will appear.

 To show or hide a particular colour range, select the colour range in the list of ranges and check or uncheck the Show this range option.

The minimum and maximum values for a colour range can be a fixed value, or a dynamic value based on a percentage of the whole gauge range, or a summary calculation on a column in the data set.

To set a colour range value to a fixed value, select Fixed Value, then enter the value.

 To set a range value to be dynamic based on a percentage of the whole gauge range, select Percentage of Range, then enter the percentage value.

 To set a range value to be dynamic based on a column, select the column, then the summary type that will be used to calculate the range value.

Finally, select a colour for the colour range.

If you are defining a Traffic Light gauge object, you can enter a label for each of the colour ranges. You can choose the display these labels rather than the value on the gauge.

Defining the Appearance of the Gauge

The Gauge Editor window Appearance tab allows you to specify the appearance of the gauge. Follow the steps below to specify the appearance of your gauge;

First, select the type of gauge object you wish to display, enter a title and select whether or not you wish the value represented by the gauge to be added as a label to the gauge.

Next, if your gauge object supports labels, choose whether you want the labels for the gauge shown inside the gauge, outside the gauge or not shown at all.

If your gauge object supports tick marks choose whether tick marks should be shown inside the gauge, outside the gauge or not at all.

If your gauge object supports tick marks choose whether tick marks should be shown inside the gauge, outside the gauge or not at all. Next enter the number of major and minor tick marks, and the frequency of labels on the gauge.

If you are defining a Traffic Light gauge you can choose to display range labels rather than the value on the gauge. You can also select whether to show the label or value below the gauge, inside the gauge or not at all.

Defining Numeric Formats for a Gauge object

The Gauge Editor window Numeric Format tab allows you to specify the numeric format used to display numbers in on the gauge. Follow the steps below to specify the numeric format on your gauge;

First, select the maximum number of decimal places you want to show. If your values are currency values, select force trailing zeros.

You can apply scaling to your numeric values in a gauge. Scaling provides notation for large values, so for instance 1340 can be displayed as 1.34K, and 2345263 can be displayed as 2.35M. There are a number of scaling units pre-defined for you, however if these do not fit your requirements you can define your own.

To define your own scaling units, select other from the scaling units list. Next supply a comer separated list for the scaling units and the scaling values. Each entry in the scaling units list will be displayed as a suffix to your values. Each entry in the scaling values represents the amount the value will be divided by from the previous scaling unit to achieve the display value.

For instance, you could set your scaling units to "Thousands,Millions,Billions" and your scaling units to "1000,1000,1000". In this example, 1340 will be displayed as 1.34Thousands, and 2345263 will be displayed as 2.35Millions.

If the scaling units list has one more entry than the scaling values, units will be given the first entry in the list as a suffix. For instance, for time scaling units, you could set your scaling units to "minutes,hours,days,weeks,years" and your scaling values to "60,24,7,52".

If you require it select a currency symbol (value that will be displayed before your numeric values). If the value you want is not in the list, select other and enter your desired value. You can also enter a number suffix, a string that will be displayed after numeric values.

Finally, if you wish to suppress the thousand separator for numbers, select the Suppress thousand separator check box.

Selecting Colours for your Gauge

If your gauge supports colour ranges the screen allows you to change the colour for each colour range.

To define colours for the colour ranges, select the colour range then press the button to choose the colour for this range.

If your gauge does not support colour ranges, simply select the colour to be used when rendering your gauge. 

Editing dashboard objects

To edit a dashboard object in your dashboard, follow these steps:

1. Select the dashboard object you want to edit in the tab.
2. Press the Edit button in the Dashboard Objects group in the Edit Dashboard tab.
3. Complete the required changes in the Dashboard Object Definition window.

Moving dashboard objects

You can move dashboard objects either within the tab they are currently displayed, or between the tabs in a dashboard.

To move dashboard objects within a tab, follow these steps:

1. Select that tab that contains the object you want to move.
2. Press the Design Dashboard Tab button in the Dashboard Tab in the Edit Dashboard tab.
3. Press and hold the tab header of the dashboard object you want to move.
4. Drag the object over the appropriate drag icon (described in detail below).
5. Release the left mouse button.
6. Press the Preview Dashboard Tab button in the Dashboard Tab in the Edit Dashboard tab.
   
   

As you drag the object over the tab, two types of guide icon will appear.

To move a dashboard object from one tab to another, follow these steps:

1. Select the dashboard object in the tab.
2. Press the Cut button in the Dashboard Objects group in the Edit Dashboard tab.
3. Select the tab you want to move the dashboard object to.
4. Press the Paste but button in the Dashboard Objects group in the Edit Dashboard tab.