What are reports?
Reports are configurable objects that define how information from your data sets
should be combined and presented into reports and charts.
Let's look again at our
we explained in the introduction) :
Reports are flexible, powerful, and very interactive with dynamic user filters. You can create
multiple layouts and choose from 7 different report types and 14 chart types (full
list with examples):
- Standard reports - A combination of Excel pivot tables and subtotals, these
reports show automatic row and column grouping, and subtotals.
- Drilldown reports - Similar to Excel pivot tables, these reports show
aggregated two-dimensional information which you can drill into.
- Pivot chart - Similar to Excel pivot charts, the charts include all common
types, such as line, bar, column, pie, donut, area, radar.
- Sparklines - Sparklines show the development of a given item over a period
of time, which is useful to understand trends at a high level.
- Hierarchy reports - These reports show data in a hierarchical structure.
For example, revenue by region or business unit can be represented this way.
- KPI gauges - These reports show gauges, ideally suited for KPI purposes.
- KPI scorecards - These reports show numeric scorecards, enabling you to
compare two different metrics, ideally suited for KPI purposes.
Create a report
You can create a report in two ways:
- Click on Define - New in the sidebar menu.
- Click on the Actions - Create new button of an existing report. This also
enables you to make a copy of an existing report.
Either way, the rest of the process is the same, and you can enter a name for your new report,
set user permissions for it, and define its fields and layouts. At every change, the preview of
the report will be automatically updated:
You can define the settings for the report via these fields:
- Name - enter a name. This must be unique within all your reports.
- Description - enter an (optional) description to explain the use of this
report to users. The text can contain basic html tags to format your text (e.g. <br>
for a next line, <b> for bold text). The text can also contain the following system functions: IMAGE, LINK, HELP, OBJECT, TENANT,
TOOLTIP, USER to include dynamic information in the text.
- Active/locked - activate or lock a reports. When locked, the report can not
- Source data - select the source data set. This is the primary data set for
your report, all linked data sets will automatically be included as well.
- Group - select how this report is to be shown in the menu navigation.
- User roles - select one or multiple user role(s) to restrict access to this report. If you leave this blank, all users have access.
- Script - create a script to process the source data before the report is
generated. This option is for advanced use, and only visible if your user role has the
relevant permissions. Read more about scripts.
You can define the fields and layouts by clicking on the Define button:
This opens a dropdown menu with further options:
- Define fields - select the fields that you want to include in this report,
and (optionally) define custom field names, conversions, calculations, and data filters.
- Add new layout - add a layout to this report.
- Edit this layout - edit the currently selected layout.
- Rename this layout - rename the currently selected layout.
- Delete this layout - delete the currently selected layout.
By default, reports will include all columns from the source data set and its linked
data sets, without any conversions or filters.
Optionally, you can click on Define - Define fields to include or exclude data
set columns, rename fields, convert field values, or to set data filters:
You are free to rename fields for the purpose of this report, without impact on the source data
sets or any other reports.
To insert, delete, copy, or move fields, you can either right-click on a row, or click on the ☰
button in the last column of a row. You can create 2 types of fields:
- Source field - this refers to a data set column. You can use the reserved
word data to refer to its content if you want to convert or filter this
- Calculated field - this does not have its own source. You need to provide a
static value, a formula, a function, or an expression on other fields to give this field
In the above example, we have created 2 calculated fields: one to calculate a variance between 2
other columns, and another one to calculate the variance as a percentage.
You can optionally set a specific format for fields with numeric values. If you leave this blank,
the field values will be formatted to 0 decimals.
- You can enter the full field names in the Convert the data and Filter
the data columns. They are not case-sensitive. XLReporting will match them to
data set columns and report fields and enclose them in brackets e.g. [Actuals]. If this
produces the wrong results, you can place the field names between brackets yourself.
- You can create multiple calculated fields but alway use the source fields, and try to
avoid chained calculations (i.e. one calculated field using the outcome of another
calculated field). Be aware that division operations (e.g. field1 / field2) will only
work if all referred fields are included in the report layout.
Convert the data
You can convert or recalculate field values in your report. You can enter a simple function or an
expression with multiple functions and operators. There are many functions you can choose from:
An expression can contain the following elements:
- The reserved word data - this is a placeholder for the current value of the
selected field. Use this every time you want to refer to the current value in an expression.
For example, to multiple the field value by 100:
data * 100
- Field names in the report - simply type the field name. For example, to
substract the Credit field from the Debit field:
Debit - Credit
- Functions - you can choose from a large
collection of functions with the same syntax as in Excel to perform a large
variety of text, data, and number operations. Some examples:
IF(Unit = "Actuals", Amount, 0)
- Static values - you can use any static value in your expression. When using
text values, these must be enclosed by " (= double quote) characters. Some examples:
data / 1000
data + " some added text"
IF(data = "Sunny",
- Operators - you can use all common mathematical operators, such as: + - * /
and you can use brackets ( ) to control the sequence of operations. For example, to
substract the Credit field from the Debit field, divide it
by 1000, and then round the result to 0 decimals:
ROUND(Debit - Credit) / 1000, 0)
Filter the data
You can filter the data in your report. Filtering means that you can exclude rows in your source
data from the actual report. You can specify a static value, a simple function, or a complex
expression and you can use all common operators. You can choose from various filter functions.
In below example, we are filtering a report on a certain period and only "P&L" data:
In many cases you might want the user to select some value when they start a report. For example,
the company or the period. You can do that by using the SELECT() as a filter:
This will present a list of values that the user can select from before the report is
A special variant of this is the SELECTPERIOD() function. If you use this function in combination
with the PERIODSUM() function, you can build powerful and dynamic period-discrete and
year-to-date calculations with just a few clicks. Read more about period
Click on Define - Add layout or Define - Edit layout. For every
layout, you can enter a name and select its report type:
The report types that you can choose from are:
- Report: standard, drilldown, sparklines, hierarchy.
- Chart: line, bar, column, pie, donut, area, radar.
- KPI: gauges, scorecards.
It is very easy to edit a report layout, simply by dragging fields across the various areas of a
This window enables you to change the report options and the fields
These are settings that determine the overall appearance of the report layout:
- Type of report or chart:
- Report: standard, drilldown, sparklines, hierarchy.
- Chart: line, bar, column, pie, donut, area, radar.
- KPI: gauges, scorecards.
- Column totals - sum, average, variance, none.
- Report totals - sum, average, variance, none.
- Sort by - sort either on the descriptions in the row fields (if you select multiple Row fields, these will all be included in the sort operation), or on the values in the report.
XLReporting provides 7 different report types and 14 chart types, see examples of all report types.
You can manually drag-and-drop any of the available fields into any of the report areas, and if
you no longer want a field in your report, you can simply drag it out again.
- Fields - this shows all fields that are available within this report.
- Filters - drag fields here that you want to use as dynamic filters. They
will be shown above the report with automatic lists of values. Whenever you select a
different filter value, the report will be immediately updated to reflect this. There are
several types of filters, which you can choose from by clicking on the field name:
- date - this will present a date picker.
- select - this will present a list of values, and allow the user to
- multi-select - this will present a list of values, and allow the
user to select one or multiple choices.
- text - this will allow the user to enter any partial text.
- Rows - drag fields here that you want to lay out into rows. This is the
main dimension of your report. You can create a grouping in your report by dragging multiple
fields here, and move them into the desired order. For the standard report type, you can
also indicate where you want the report to show subtotals.
- Columns - drag fields here that you want to lay out into columns. Usually,
these are fields that contain a period, date, or time. By including one or more fields into
columns, you can create two-dimensional reports.
- Values - drag fields here that you want to summarize on. Usually, these are
number or amount fields. Once you have dragged a field here, you can click on it to select
the color and the type of operation.
You can choose from these operations:
All operations are automatically calculated for all subtotal levels in the report.
- Sum - the sum of all values (non-numeric values are ignored).
- Average - the average of all values (non-numeric values are
- Count - the number of values that are not blank (values can be
numeric or text).
- % of total - the % that each value makes up of the report total
(non-numeric values are ignored). The report total is always 100%.
- Minimum - the smallest value (values can be numeric or text).
- Maximum - the largest value (values can be numeric or text).
- We deliberately kept this feature identical to pivot tables in
Excel. It is a very powerful and easy way to define reports.
When defining a report, you can use the Save and Actions
buttons in the right-top of the screen:
These buttons enable you to do the following:
- Save - save your changes
- Actions - open a dropdown menu with further options:
- Create a new report (or copy one)
- Delete this report
- Add to my dashboard
- Export data or print
- Review this report
- Run this report now
Add to dashboard
A dashboard is a personal page with your own favorite reports and models. You can create multiple
dashboards, and each dashboard can contain multiple reports or models.
Click on Actions - Add to my dashboard to add this report to either a new
dashboard, or to a new dashboard:
Review this report
Click on Actions - Review this report to review a number of aspects of this
report and its source data set:
- Data diagram - shows a diagram with all linked data sets. This is derived
from columns that are set to data type Linked values.
- Linked data - shows the results of checks on the Key
values and Linked values that are used to link data sets. Because
linked values need to correspond with key values, this integrity check is important.
- Define fields - shows a summary of all defined columns and data types.
- Data set use - shows all linked data sets, the imports, reports, and models
that are using this data set, and the total number of data rows in this data set.
- Recent activity - show all recent user activity relating to this data set.
XLReporting provides 7 different report types and 14 chart types:
A combination of Excel pivot tables and subtotals, these reports show automatic row and column
grouping, and subtotals:
Similar to Excel pivot tables, these reports show aggregated two-dimensional information which
you can drill into:
Similar to Excel pivot charts, the charts include all common types, such as line, bar, column,
pie, donut, area, radar:
Sparklines show the development of a given item over a period of time, which is useful to
understand trends at a high level:
These reports show data in a hierarchical structure. For example, revenue by region or business
unit can be represented this way:
These reports show gauges, ideally suited for KPI purposes:
These reports show numeric scorecards, enabling you to compare two different metrics, ideally
suited for KPI purposes:
All report types are very interactive with dynamic user filters, and you can change the position
of fields in the report -and the level of detail- at any time.
Recommended reading: Back to top
| Data sets