New article
Recently updated
Introduction to BI Reporting Analysis
Who is this article for?Users who want to know more about the Analysis area of BI Reporting.
BI Reporting access is required.
Analysis is designed to help you interpret the dataset and create meaningful visualisations to easily identify trends and distributions.
Here data can be displayed in a variety of arrangements within tables and charts. Data can be 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
- Formatting Data
- Creating Charts and Gauges
- Data Forecasting
- Pivoting and Summarizing Data
- Adding to Your Visual Gallery
1. Overview
The Gear icon is used to access any of the configuration details throughout the analysis grid or charts. Selecting this icon at the top of the analysis will allow you to rename the analysis, yet, selecting the icon against the data table will display a variety of features to edit the table controlled by a horizontal menu.
Undo/Redo icons at the top of the grid allow changes to be removed or reapplied, as necessary.
The analysis is comprised of panels. These panels can contain either a table, crosstab or chart and can be collapsed and expanded using the ‘+’ and ‘-‘ icon respectively. Panels can also be rearranged within the analysis by dragging and dropping them on the screen.
Some panels may contain an option to Export, this will be indicated by the icon. The format options to export can vary from panel to panel but can either be Excel, CSV, or PDF.
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 KPI’s 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 the data table will appear:
| Function | Description |
|---|---|
| Columns | The option to show and hide columns by checking on and off the associated columns. |
| Sort |
The option to sort the columns to the defined parameters. Select a data column and then select the order direction and apply by selectin ‘Add’. |
| Group | Group multiple columns and exclude detail rows allowing counts to be more visible. |
|
Aggregate |
Calculate totals, averages and additional values for the top levels and grouped levels. |
| Paging | Define how many rows to display at one time or “Show all rows” in the dataset. |
The column headers within the data table can be used to rearrange the data table by dragging and dropping and column widths can be adjusted.
Table column headers contain additional features, to access these features select the text of the column header.
A dropdown menu will appear providing options to sort, format, filter and group the dataset based upon that column.
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 at the top of the formula tab and will present a new page displaying the syntax and the notes on all formulas available in analysis.
To create a new formula column, select the “Formula” tab at the top of the screen:
- Define a name for the formula column.
- Insert columns into the formula box by selecting from the Insert a Column dropdown.
- Enter the formula you wish manually by typing it in or select it from the Formula dropdown.
- Choose the Data Type for the new column and specify the Display Format.
- Click Add to add the formula column to the table.
Use the Replace and Remove options to manage the formula columns for that data set. Formula columns that have been added are now available for use in additional formulas.
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 “Filter” tab at the top of the screen:
- Select the Filter Column, the column containing the values you wish to compare.
Formula columns are also available from this list to be filtered. - 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 Add to apply the filter to the dataset.
Filters that are created will be added to the list under the filters tab.
If multiple filters are applied operator will appear allowing the creation of And/Or statements and filters can be grouped by Parentheses managed by the (+) and (-) icon in various arrangements.
Once the filters are configured, the Gear icon can be used to collapse the settings, displaying the filters in a list. Unchecking a filter checkbox will disable the filter and selecting the description text will allow the filter value to be changed.
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 comparison of =, a sliding date can additionally be chosen by using this comparison and will provide a list of relative dates e.g. Last Week End, Last Month Start, 30 Days Ago etc…)
Sliding Dates are also available when using the date range comparison and can be used to look at data from set time variables e.g. the last quarter, the last month, the last 90 days etc…. by specifying start and end values. Once completed click Add to apply the filter.
6. Formatting Data
The format of the appearance of data in the data table can be changed by selecting the column header and selecting Format from the Options dropdown.
These options allow you to apply several different standard formats to the data. The formatting options presented are dependent on the data type of the column.
Selecting the Cell Colour option allows cell colours to be defined based upon the data values. The colour will be displayed for any cells matching or a lower value that the defined value. Leaving a value blank indicates the maximum value.
To remove formatting on a column, select the No Format option.
7. Creating Charts and Gauges
To create a new chart display, select the “Add Chart” tab at the top of the page.
Selecting this will produce a new chart panel containing its own configuration area. The type of chart is indicated along the top menu of the panel and contains options for charts presented as a Bar, Line, Curved Line, Pie, Scatter Plot, Heatmap and Gauge.
Dependent on the chart type selected several different options will appear, including:
| Option | Description |
|---|---|
|
Label Column* |
The variable being measured against, also known as an independent variable. Example 1: Document Control Type. If the data is not of date type, there will be the option to sort the data. Example 2: Date In (date issues raised). If the data is of date type, there will be the option to break down the data by time interval e.g. month |
|
Data Column* |
The variable being measured, also known as the dependent variable. The data aggregation method is selected by the associated dropdown with options including Sum, Average, Standard Deviation, Count, Distinct Count, Minimum and Maximum. It is important to ensure the appropriate method of aggregation is selected for the data type. Example 1: Read Count (Sum). Example 2: Issue ID (Distinct Count). |
| Additional Column | Specifies an additional data series to be added to the chart in comparison to the Data Column. Additional options will appear to configure this additional data series including aggregation options and display types. |
| Bar Orientation | When the chart type “Bar” is selected there is the option to change the orientation from horizontal to vertical. |
| Relevance | Allows the data show to be tuned to look at either top/bottom values or top/bottom percentage. The amount can then be altered from an automatic value and the option to show others (all data that is not included in the relevance as its own count) is available. |
| Forecast | If your data column is of date type the option to forecast is available. Options include Time Series, Regression and Trendline (see below for more details on the options available) |
*This does not correlate to an x-axis or y-axis due to variations of chart orientations and display options
Charts are displayed in individual panels which can be expanded and collapsed by using the ‘+’ and ‘-‘ icons. Panels within the analysis can also be moved by dragging and dropping via selecting the top of the panel section.
8. Data Forecasting
Data forecasting is available for Bar, Line and Curved Line charts.
Data forecasting is the process of generating new data values for events that have not yet occurred. Forecasting specifically refers to the statistical methods that use time-series, cross sectional, or longitudinal data to produce predicted data values. These forecasted values are displayed most effectively on charts.
Guidance for applying data forecasting effectively is:
- The label column should be a date type.
- The data column can contain any data type, however, if it is non-numeric the data will be replaced with an integer.
There are three forecasting options available within analysis, Time Series, Regression, Trend Line.
Time Series
If the data, you wish to display is in a natural time related order with a strong and consistent interval the Time Series forecasting selection may be appropriate to use.
This method of forecasting is optimised for analysing data across time periods.
To create a time series forecast:
- From the Forecast dropdown, select Time Series.
You should see a new visualisation appear on the chart (in this case a green column). This is the forecast.
- If the time interval displayed on the x-axis is smaller than a year, there will be additional options next to the forecast.
- An additional dropdown will appear allowing you to define the cycle of the forecast (the periodic variation used to analyse the data). Changing the cycle will produce different forecasts.
Regression
Regression is the measure of a relation between the two variables. Regression is an appropriate method of forecasting for casual data where a strict trend might not be evident.
To create a regression forecast:
- From the forecast dropdown, select Regression.
- Another dropdown with the title of Type will appear. Here you can specify the type of regression you wish to follow which will in turn affect the forecast.
| Type | Description |
|---|---|
| Linear | Calculates the forecast with values based on the trend line. |
| Autoregressive |
Calculates an output based upon previous outputs, using ‘Burg’s’ method. This model provides a representation of a type of random process, it is often used to describe time-varying processes in nature and economics. The output variable depends linearly on its previous values and a random variable. |
| Exponential |
Calculates a forecast based on the relationship between the dependent and independent variable as a curvilinear function. This is the most useful when the data values rise and fall at increasingly higher rates. You should not use an exponential trend line if your data contains negative values or zeros. |
| Logarithmic |
Calculates a best-fit forecast that is particularly useful when the rate of change increase/decreases rapidly and then levels out. Positive and negative values can be used to create this forecast. |
| Polynomial |
Calculates a forecast based on the relationship between the dependent and independent variable as a curvilinear function. It is used when data fluctuates. The order of the polynomial to use (2,3,4,5) is dependent on the number of fluctuations in the data. Polynomial 2 (order 2 polynomial) trend line will generally have one fluctuation. Polynomial 3 (order 3 polynomial) trend line will generally have two fluctuations etc… |
| Power |
Calculates a forecast based on the relationship between the dependent and independent variables. It is best used with data sets that compare measurements that increase at a specific rate. |
Trend Line
Trend lines are useful data presentation tool when the general trend of the data can not be seen easily. A trend line will produce a line on the chart to indicate the trend of the data, this can be straight or curved.
To add a trend line, select Trend Line from the Forecast dropdown.
Another dropdown with the title of Type will appear. Here you can specify whether you wish the trend line to be straight or curved.
Example of a straight trend line:
Example of a curved trend line:
9. Pivoting and Summarizing Data
The method to pivot and summarize data within analysis is known as a Crosstab.
To produce a Crosstab, click the “Add Crosstab” tab at the top of the page.
Selecting this will produce a new panel for the crosstab containing its configuration settings where several fields need to be defined appropriately.
| Item | Description |
|---|---|
|
Header Values Column |
The values contained in this column will be shown horizontally, as column headers across the top of the crosstab table. Dependent on the data type contained in the column, additional options may appear. |
| Label Values Column | The values contained in this column will be shown vertically, in the first column on the left. |
| Aggregate Values Column | The column which values will be aggregated to produce the contents of the rest of the table cells. |
| Aggregate Function | This is applied to the Aggregate Values Column, options include Sum, Average, Standard Deviation, Count and Distinct Count, Minimum and Maximum. |
| Summary Function | Select a summary function to display a summary result either as a summary row or summary columns for totals. |
| Compare Label Columns |
Check the checkbox to display the differences between cells with a shading indicator. This is calculated on a row basis from left to right. If the value to the left is NULL there will be no comparison displayed in the cell immediately to the right. |
Select the Gear icon to hide the configuration area.
10. Adding to Your Visual Gallery
As you create tables and charts within your analysis, each have an option to add to the Visual Gallery. Add to the visual gallery by selecting the visual gallery icon.
After selecting the icon, you will be prompted for a title and a description, this will be used in the visual gallery to identify your widget so other users are able to find it. Click Done once completed.
This widget will now be available to be added to the dashboard and reports.