New article
Recently updated
Introduction to BI Reporting Visual Analysis
Who is this article for?Users who want to know more about the Visual Analysis area of BI Reporting.
BI Reporting access is required.
The visual analysis area of the BI tool is designed to help you interpret the dataset and create meaningful visualisations to easily identify trends and distributions much like the analysis area of the tool.
Visual analysis offers some additional functionality such as mark lines and colour conditions that are not contained within analysis. However, visual analysis does not have the ability to forecast or produce trendlines which is a function within analysis.
Data can be displayed in a variety of arrangements within tables and charts. Data is handled in activities including sorting, grouping, and filtering. In addition to this, calculated columns can be created to further allow you to analyse the data.
- Overview
- Selecting Data
- The Data Table
- Creating Formula Columns
- Filtering Columns
- Column Functions
- Creating Charts and Gauges
- Colours and Mark Lines
- Explorer Function
- Crosstab Function
- Exporting Data
- Adding to Your Visual Gallery
1. Overview
The Gear icon is used to access any of the configuration details throughout the visual analysis grid or charts. Selecting this icon at the top of the visual analysis will allow you to rename the visual analysis, yet, selecting this icon against a column will provide details for that data column, allowing grouping and filtering to be conducted.
The visual analysis area is comprised of a data table and a canvas area for the charts created. This table will contain all the data and any calculated columns created. The canvas area is where the data is visualised in the format of a chart or gauge, only one chart can be made at a time to represent the data in visual analysis.
2. Selecting Data
To start working within the tool the dataset must first be defined. The process of defining the dataset is the same in analysis and visual analysis and the same datasets are pulled through in both areas of the tool.
Data can only be analysed from one module at a time; therefore, the first dropdown provides a selection of all modules available within the system.
Data will be visible from a module if the user has access to that module e.g. a user who is a member of the “Issue User” system group will be able to see data from the source ‘Issue’.
If the user is an administrator for the module, they will have access to the unrestricted data from that module e.g. a user who is a member of the “Issue Administrator” system group will be able to see data from the source ‘Issue Unrestricted’.
|
Data Source |
Description | User when... |
| [Module] | Allows access to data within the module but maintains the modular permission based upon the user. | The data contains private details e.g. names and email address and these will be displayed in some way (for example within a table). |
| [Module] Unrestricted |
Allows access to data within the module but removes all modular permissions on the records. | The data contains information which is suitable for all to view, or the information to be displayed is not of a sensitive nature. For example, KPIs and counts. |
Once the data source is selected the first data table is displayed, which is data in association with the core record details. The only exception to this is data from the audit module where an option to select data from “Audit” or “Findings” appears.
The dataset within the tool is built up of several data tables derived from the database. By selecting additional data tables enables a larger dataset to be established for investigation.
Link tables contain very little data but allow associations to be made to additional data tables, for example, organisational areas.
Check on and off the data you wish to be contained within your dataset and build up your dataset. When all relevant data is selected, click Apply Column Selection.
3. The Data Table
Once the dataset is defined, all selected data will be pulled through into the data table. Additional data can be added if required from the selecting the data tab at the top left hand of the screen.
By selecting the Gear icon, the configuration settings for that column will appear:
| Function | Description |
|---|---|
| New Calculation | Provides the option to create a calculation directly from the column. Selecting to create a column from here will bring through the column directly into the calculation field. |
| New Filter |
Provides the option to create a filter based on the column selected. |
| Formatting |
This will only appear for columns that have a format that is able to be changed e.g. not a text field. Selecting this will provide options for the display of that data column. |
|
Grouping |
Checking this on for a non-numerical or non-date field will group the variables. Checking this on for numerical or date fields will then provide options for the grouping such as “Automatic”, “Width of Groups”, “Number of Groups”, “No Binning” and “Date Units”. When grouping by “Date Units” the interval will need to be defined e.g. 1 Months or 2 Weeks etc. |
| Colours |
Colour the cells in the data table based upon several defined variables. |
Column order can be rearranged in the data table by selecting the column header and dragging and dropping it to the new location.
Column widths can also be adjusted by selectin the area in-between columns and dragging it to the right.
4. Creating Formula Columns
Formula columns can be added to help enhance our dataset for further analysis. New columns are always added to the right side of the table but can be relocated to a more appropriate place by dragging and dropping.
Formula help is available by hovering over a formula. This will present the syntax and notes on the formula in question.
To create a new formula column, select Calculated Columns from the top of the data table or + New Calculation from the column settings.
-
Define a name for the formula column.
-
Insert columns into the formula box by navigating to the columns tab (in the calculation pop-up).
-
Enter the formula you wish to use manually by typing it in or select it from the Math, Date, Text, Logic, Covert, or Misc tabs in the calculation pop-up.
-
Verify that the calculation is valid by selecting the Test icon.
-
If the calculation is valid the icon will go green with a tick displayed.
-
If the calculation is invalid the icon will go red with an X displayed.
-
The Add button will be greyed out if the calculation is invalid, meaning that the column will not be able to be added.
-
-
Click the Add button to add the calculated column to the data table.
When the calculated column has been added, the calculation can be amended by clicking the Gear icon of the calculated column and selecting Edit Calculation.
Within the Edit Calculation menu, the calculation can be amended and the option to delete the column will become available.
To apply any changes made to the calculation, test the calculation, and click Update.
5. Filtering Columns
Filters can be used to help handle the data, removing data that does not meet your criteria, and inspect specific variables.
To create a new filter, select the Gear icon on the column you wish to filter:
- Select + New Filter.
- Select the criteria from the Comparison dropdown.
- Dependent on the filter column and comparison chosen additional input controls may be displayed, for example date ranges.
- Click Apply to apply the filter to the dataset.
From here all filters can be turned on and off. To amend a filter, select the filter statement in the filter list, make any amendments as required, and click Apply. To remove a filter from the dataset, select the ‘x’ next to the filter statement.
Additionally, if there are multiple filters on the dataset And/Or statements can be created, and filters can be grouped by dragging and dropping statements on top of one another.
All filters can be removed from the dataset by selecting the ‘Bin’ icon [x] at the top of the filter menu.
Filtering by dates
If the filter column selected is a Date type column, different options to filter will be displayed.
Specific Dates can be filtered on by using the any of the available comparisons.
Sliding Dates can also be used on any of the comparisons by changing date field box to a function box by selecting the arrow and the f(x) icon. Selecting it will display all available date functions that can be used to filter the data.
Selecting a date function will display it in the formula box. If an offset is required e.g. +5 or -5, this can be added into the formula after the open bracket. If no offset is required, simply close the bracket, and click Add.
Sliding dates are particularly useful when using the comparison of between, as this allows data from set periods, e.g. the last quarter to be looked at and minimises the need to amend the filters in the chart often.
6. Column Functions
Showing and Hiding Columns
Click the Show/Hide button at the top for the data table to display the Show/Hide options box. If there are many columns in the data table these can be sorted by data type, alphabetically and hidden columns.
To hide a column, select it in the options box, then click Apply. Re-select the hidden column in the options menu and select Apply and the column to show it again in the dataset.
Columns that have been re-added will not be re-added into the data table in their previous position. Instead, these columns will be re-added to the far-right hand side of the data table. The column can be moved back to its original position by dragging and dropping the column from the Show/Hide Options box into the desired position.
Formatting Data
The format of the appearance of data in the data table can be changed by if your data is not of text data type. Selecting the Gear icon on the column header will open the settings menu for that column.
The option to change the formatting will then appear. Select from the dropdown options how the column should be formatted.
Grouping Rows
To change the grouping enable/disable grouping on a column select the Gear icon to open the column settings menu and select Grouping.
Select a grouping parameter from the dropdown list and click Apply to group the column by that parameter. The grouping options available will depend on the data type of the column.
Continuous Data is grouped into various categories, called “bins” based upon the distribution of the data, ensuring the reasonable chart is generated to produce a readable output. You can remove this binning by selecting the No Binning option from the dropdown list.
Types of grouping available include:
| Grouping | Description |
|---|---|
| Automatic | A grouping model based on the “best fit” of the data will be applied using a default number of bins, based upon the data in question. |
|
Width of Groups |
Allows the width of groups to be specified. The tool will interpret this as an approximate amount due to the tool upon occasion needing to create endpoints of outliers to break the data up into meaningful groupings. There is an option available to Set Customer Outlier Groups where outliers greater and less than set values can be defined. |
| Number of Groups | Allows the number of groups to be specified. The tool will interpret this as an approximate amount due to the tool upon occasion needing to create endpoints or outliers to break the data up into meaningful groupings. |
| Date Units |
Allows dates to be grouped by defined intervals at a specified width e.g. data grouped for every 2 weeks. There is an option available to Set Customer Outlier Groups where outliers greater and less than set dates can be defined. |
| No Binning | This disables the automatic binning features and creates a group for each distinct data value. |
Upon grouping the data, grouped rows are maintained and can be viewed by selecting the '+' icon.
Grouping in Charts
The tool automatically analyses the data within the column and attempts to provide a grouping that ensures any charts created provide a view of the data that is easy to read.
Change the grouping displayed on the chart by selecting the Gear icon on the column that is being displayed in the chart.
It is good practice to change you grouping within charts especially when using values of date data type to ensure that the data display is exactly specific to the requirement.
In-Cell Graphics
Options to produce In-Cell Graphics to understand data easier at a glance are contained in the settings menu for the columns of numeric data.
| Function | Description |
|---|---|
| Bar |
Produces a red of green bar dependent on the value of the cell. The bar colour and size are in relation to the baseline set. |
| Heatmap | Produces a gradient of cell colours ranging from red to green in relation to the baseline set. |
| Icon |
Provides three graphic options Circle, Square and Arrow. The colour of the icon (and arrow direction if selected) are evenly distributed in percentage thirds (less than 33%, between 33% and 66% and greater than 66%). |
The display of the graphics can be turned on and off by using the enable/disable switch.
Colours
Options to produce colour condition in-cell graphics is also available when accessing a columns options menu by selectin the Gear icon.
These colour conditions are based on the defined data threshold. To define these, select Colors in the options menu.
Using the options provided, select a comparison operator, define a value, and select a colour. Additional colours can be displayed by selecting Show more. If the colour required isn’t available, enter a custom colour using the Enter Custom field.
In this field colour values can be entered directly as HEX “#CCCCCC”, RBG “rgb(93,104,171)” or text e.g. “Blue” values.
Select the ‘+’ and ‘-‘ icons to add and remove thresholds. Click Apply to apply the conditions to the column.
7. Creating Charts and Gauges
There are a variety of charts and gauges within Visual Analysis to be able to display data.
| Type | Description |
|---|---|
|
Area |
Similar chart to a Line chart. However, the area between the x-axis and the line is filled in. This area is commonly emphasized by colours and can be broken down to show different variable using a crosstab. This chart will typically compare two or more quantities. |
|
Bar |
Presents grouped data horizontally as rectangular bars that are proportional to the values they represent. It is best used when representing categoric data. The y-axis displays the categories to be compared and the x-axis represents the discrete values. Values from multiple data series may be stacked of grouped side by side. |
| Calendar | Heatmap-style visualisation of time-series data (i.e. data of date type), using a colour gradient to depict volume or frequency of an event over a period of time. It can be very useful for displaying correlations between events and specific time periods. |
| Column | Presents grouped data vertically as rectangular bars that are proportional to the values they represent. It is best used when representing categoric data. The x-axis displays the categories to be compared and the y-axis represents the discrete values. Values from multiple data series may be stacked of grouped side by side. |
| Combo | Presentation method in which multiple data series can be displayed in overlapping chart styles making it easier to compare values. If two different chart types are being displayed the option to display a secondary axis become available to aid the visualization of data. |
| Donut | Relation to the pie chart, representing data in a near identical way; numerical proportion of a variable. However, a donut chart has a hole in the centre providing a different display. |
| Semi-Donut | Similar to the donut but is limited to only half a circle. Numerical proportion is related to the arc length of the variable. |
| Line | Displays a series of data points connected by a straight line. Similar to a scatter chart, however, the x-axis values tend to be in order. Commonly used to track a variable over time, therefore the x-axis is often order chronologically. |
| Pie | Circular graphic representing numerical proportion. The larger the proportion of the dataset a value represents the larger its proportion on the chart will be. The arc length is proportional to the quantity represented by the variable. |
| Semi-Pie | Similar to the pie chart but is limited to only half a circle. Numerical proportion is related to the arc length of the variable. |
| Scatter | Scatter charts use Cartesian coordinates to plot data points that are in relation to the two variables on the x-axis and y-axis. Often used when tracking the dependency of one continuous variable upon another continuous variable. |
| Timeline | A feature that is available on several of the chart types. Adding a variable of date data type into the timeline field will allow animated snapshots of the data to be displayed at defined intervals. It can be started and stopped at various points in time so that the resulting data can be analysed. |
| Arc Gauge | Most useful when showing an aggregation of values from a single column. Displayed with an arc that move from red, to yellow, to green as the maximum value is approached alongside a static numerical value (this can be a count, distinct count, sum, average etc.). |
| Bullet Gauge | Useful when displaying an aggregation of values from a single column. The information displayed in this visualization is displayed in a much more compact format than an arc gauge. |
|
Dial Gauge |
Much like the Arc and Bullet gauge the Dial gauge is most useful when displaying an aggregation of values from a single column. Like the display of an arc gauge however, the dial gauge contains visual markers for the defined boundaries and the values on the arc display. |
| KPI Gauge | Useful for displaying an aggregation of values from a single column. Unlike the other gauges the KPI gauge displays the value only with no other visuals. Options can be set against the display to indicate when a defined boundary has been exceeded in the form of an icon or change of colour. |
Additional chart options include the Crosstab feature which allows groupings to be applied and percentages to be shown.
8. Colours and Mark Lines
Colour Conditions
Options to produce colour conditions in relation to data thresholds are available when accessing a columns options menu by selecting the Gear icon.
These colour conditions are based on the defined data threshold, to define these select Colors in the options menu.
Using the options provided, select a comparison operator, define a value, and select a colour. Additional colours can be displayed by selecting Show more. If the colour required isn’t available enter a custom colour using the Enter Custom field.
In this field colour values can be entered directly as HEX “#CCCCCC”, RBG “rgb(93,104,171)” or text e.g. “Blue” values.
Select the ‘+’ and ‘-‘ icons to add and remove thresholds. Click Apply to apply the conditions to the chart. Multiple colour conditions can be added based upon the same variable.
Mark Lines
Options to produce a mark line on a visualization to represent data thresholds are available by selecting the Gear icon. Mark lines are only available on the axis which is counting the variable e.g. the dependent variable.
The mark lines are defined by an individual value and selecting a colour. Additional colours can be selected by selecting the primary value and then choosing Show more from the menu. If the colour required isn’t available enter a custom colour using the Enter Custom field.
In this field colour values can be entered directly as HEX “#CCCCCC”, RBG “rgb(93,104,171)” or text e.g. “Blue” values.
Select the ‘+’ and ‘-‘ icons to add and remove mark lines. Click Apply to apply the conditions to the chart.
The mark line can be renamed to provide an indicator of what the threshold is displaying. Select the ‘Line x value’ and allow editing of the text field. Enter the text into the text field and select apply to apply the changes to the chart.
9. Explorer Function
The explorer function is available on all charts created in the visual analysis area of the tool. It is available within visual analysis to aid the interrogation of data and additionally is available on the dashboard to aid with the display of information.
To use the explorer function, select the Magnifying Glass icon to open the menu.
There are four features of the explorer function; Zoom, Select, Drill Mode and Export to image.
| Mode | Description |
|---|---|
|
Zoom |
Allows specific areas of the chart to be zoomed into to enable a clearer view of the data. An area of the chart can be zoomed in to by dragging a selection area around the specific range of interest. When the selection is released, the chart will refresh and adjust to just show the selected area. If a selected area/data range has been zoomed in to a refresh icon will appear which will reset the chart back to its original size. Dependent on the chart type and data type a zoom bar may appear outside the chart representing where on the chart has been zoomed in to. By dragging the end of the bars outwards will extend the range of the zoom by dragging the end of the bars inwards will reduce the range of the zoom. By selecting the middle of the bar and dragging the bar across the chart will allow the chart to be panned through by the same zoom range. |
| Select |
Useful for identifying anomalies in the data whilst creating the panel. When in the select mode by selecting an area on the chart e.g. a bar all other data points will be dimmed in addition to the data in the data table below immediately filtered to included only the selected values. |
| Drill Mode |
Useful for breaking down data within the chart display to provide temporary views of supporting information. When in the drill mode and an area of data is selected a menu will appear with all variables which are included in the data table behind the chart. Selecting a column from this menu will break down the original bar by the variable selected and the chart will be redrawn by to reflect the drill down selection. Drill down can be continued as many levels as required. A trail of all drilldown’s conducted on the panel will appear at the top of the panel detailing all the different variables drilled into. The trail enables previous drill selections to be returned to. |
| Export |
By selecting export an export of the panel is downloaded in the png format. If the chart has been filtered or zoomed into or the drill function has been applied, these variables will be represented when the chart is exported. When the chart is exported an image, the title provided does not pull through to the downloaded image. |
10. Crosstab Function
Cross tabulation (also known as crosstabs) is a method used to analyse the relationship between multiple variables simultaneously. Crosstabs can be used in charts or tables, as a table they are more commonly known as “pivot tables”.
Crosstab Charts
When creating a chart, the crosstab function will be visible once the x-axis and y-axis have been defined by columns.
Drag and drop the variable you wish to base the crosstab on into the crosstab area and the graph will be redrawn accordingly.
Applying a crosstab to a chart provides more display options for that chart type. As a standard, the crosstab is presented in a “Stacked” manner. This can be changed by selecting More options against the chart type and presenting additional options such as Percentage Crosstab and Grouped Crosstab.
Crosstab Tables
Data can be presented in the format of a crosstab table. To do this, select the Table and then Crosstab Table option from the visualisation options.
Define the relevant column, row headers, and specify the values to be counted. Multiple row and column headers can be applied, and a Column Summary and Row Summary are available to be added to total the values.
Once happy with the values, click Apply to display the crosstab table.
To re-access the column settings select the Gear icon, make any changes, and then click Apply to apply the updated settings.
Select the Gear icon on the column or row headers to access the groupings and format for the columns.
11. Exporting Data
The data table from Visual Analysis can be exported into a CSV format (Comma-Separated Values). To export the data table, select the CSV Export icon and the values within the table will be exported.
If there is a lot of data within the data table this will take time.
12. Adding to Your Visual Gallery
Once the visualisation is complete, save it to the Home area by selecting the Save icon at the top right hand of the screen. Saving the visualisation does not release the visualisation to be added to reports and dashboards.
To add to the visual gallery, select the Add to Gallery icon at the top right hand of the screen. Provide a title and a description for the panel, this is what will be used to identify the panel in the visual gallery. To add the panel, click Add Panel.