Models are configurable objects that combine data queries with spreadsheet-like templates,
enabling you to build advanced reporting and planning models.
Let's look again at our diagram (which we explained in the introduction) :
Models are free-format templates which you can build in the same way as you would build an Excel spreadsheet. Models can contain multiple sheets, and each sheet contains a layout, cell values, cell formatting, formulas, and functions.
But unlike Excel spreadsheets, models contain only your logic unencumbered with data: realtime data is pulled into the model during Run model. This ensures that models always work with updated and accurate data, and your logic never gets compromised.
In other words: optimal separation of logic and data, which keeps your reporting and planning processes very robust.
Models can be defined for 2 different purposes:
You can create a model in two ways:
Either way, the rest of the process is the same, and you can enter a name for your new model, set user permissions for it, and define its sheets and queries:
When you create a model, focus on the template, don't enter real data. You can integrate data through queries, which we'll explain below. The above example -with just a few rows- is a fully functional model.
You can use the toolbar to populate and format the cells, create formulas, set named ranges and conditional formatting - all in the same way as in Excel. You can also insert objects, such as cell editors, named ranges, sparklines, images, web links etc. XLReporting supports all common spreadsheet functionality. You can create multiple sheets if needed.
You can define the settings for the model via these fields:
Models can contain multiple sheets. Each sheet can contain its own content and layout. You can add sheets by clicking on the + icon. You can rename or delete a sheet by clicking on the arrow behind the sheet name. You can also re-arrange the order of sheets by simply dragging them.
If you prefix a sheet name with an underscore (e.g. _test), that sheet will be hidden in Run models. This is useful for sheets that are used for calculations or data processing and which don't need any user interaction.
Queries enable you to define how data sets should be integrated into your model. A model can contain multiple queries. During Run model, all its queries are simultaneously executed against the XLReporting database and the resulting realtime data is inserted into the model.
You can define queries by clicking on the Define button:
This opens a dropdown menu with further options:
When you add a new query, or edit an existing query, the first step is to choose the type of query and determine the name and settings for that query:
You can choose between 3 types of queries:
If you select Report or Data, you need to select the source data set: this is the primary data set that you want to retrieve information from. All its linked data sets will be automatically included, and you can create a report in exactly the same manner as Define reports, including converting the data, filtering the data, and setting the report options and fields position.
If you select Save, you need to select the target data set: this is the data set into which you want to import the data from your model. You can create the column mapping in the exactly the same manner as Define imports, including mapping the columns, converting the data, filtering the data, and the settings for inserting or replacing data.
In Options when running the model you can select a number of features:
Once you completed the query definition, a special function will be inserted into the current cell: =QUERY(ID, range), where ID is the query number, and range is the cell range into which the query data will be inserted. The function shows the description of the query.
After every change, the preview of the query results is automatically refreshed, which helps to integrate the query into your model.
The cell that contains the =QUERY(ID, range) function indicates where the query data will be dynamically inserted during Run model:
The 3 rows below the placeholder are templates for the actual query data:
Headers - the query column headers will be placed on this row.
Data rows - the query rows with data. Dependent on the options that you selected:
✭ The default setting is to Insert rows, enabling you to place additional content in your model underneath the query. Everything will be shifted down when the data rows are inserted, and all formula references will be adjusted accordingly. Cell values on columns adjacent to the query range (i.e. to the left and to the right of the query) will be copied down.
Totals row - the query report totals will be placed on this row.
You can set the cell styles (i.e. font, size, cell format, colors, borders etc.) for each of the 3 template rows. If left blank, default cell styles will be applied.
During Run model, realtime data will be inserted and the above template produces the following result:
You can use functions to pull selected data from report or data queries into individual cells. Unlike report queries, which insert an entire formatted report into a range of cells in the model, functions enable you to insert a single value or an aggregate total (e.g. a sum or count) into one cell. You can create as many functions as you want, to populate multiple cells with varied data:
The example in this screenshot retrieves the sum of column 5 of query ID 1 for all rows where column 2 = "30. Revenue": =DSUM("30. Revenue", 1, 5, 2). The correct result is 240.436.
There is a collection of query functions that you can choose from. All query functions require the same parameters between brackets:
Cell objects are definitions that you can place in a cell in a model to create advanced functionality within that cell, for example cell editors, buttons with row actions, conditional formats, sparklines, images, or hyperlinks.
To add cell objects, you need to select a cell or range of cells, and click one of these buttons in the toolbar:
You can choose from 6 types of objects:
Once you click the toolbar icon, a popup window will guide you through the process of defining the object.
By default, models are read-only in Run model (i.e. all cells will be locked). This is the default setting for reporting models.
But you can also create planning models that allow users to enter and edit information into the model during Run model. Creating this type of models requires 2 or 3 additional steps:
Cell editors are definitions that you can place in a cell (or in a cell range) to make a cell editable (remember: by default, all cells are locked) and define both the type of cell value and the method by which users will be able to enter or select cell values.
To add cell editors, you need to select a cell or range of cells, and click this button in the toolbar:
And then select the appropriate cell editor:
You can choose from 8 types of editors:
When you add a cell editor in a cell or range of cells, those cells are unlocked, and the user can enter or edit data in the model (and validated by the selected editor) during Run model.
Planners are configurable patterns that can be used to distribute amounts across a range of cells (for example, to apply a seasonal revenue pattern to a series of financial periods).
Adding planners to a model requires 2 steps:
The below example defines 3 patterns for a cell editor: 12 equal months, a 4-4-5 weeks calendar, and a seasonal pattern that trends upwards in the Summer:
In addition to statically defined planners, you can also refer to a data query. Your data query can include multiple rows, with each row being treated as a separate planner. The first column on every row needs to contain the planner name, and the other columns need to contain the numeric patterns.
The absolute values of the numeric patterns are irrelevant, what matters is each relative value as a proportion of the sum of all values. You can define as many indices in the patterns as you need.
The PLANNER function returns a proportion of data according to the selected planner and the selected index. The returned value is calculated as follows:
data * value of given planner index / sum of all planner values
If the given index does not exist in the planner, the function will use the given
ratio (which can be a cell reference, a formula, or a given value) and will return
the result of data * ratio.
If neither the index or a ratio exists, the function will return 0 (zero).
PLANNER(data, planner, index, ratio)
PLANNER(1000, "EqualMonths", 6) = 83.33 (1000 * 1 / 12
PLANNER(1000, "Weeks445", 6) = 96.15 (1000 * 5 / 52)
PLANNER(1000, "Summmer", 6) = 111.11 (1000 * 4 / 36)
PLANNER(1000, "", 0, B3/C3) = 200.00 (1000 * 1/10, assuming B3=1 and C3=10)
Row actions are are configurable buttons that you can place in a cell in a model to allow rows to be inserted, copied, or deleted.
To add row actions, you need to select a cell or range of cells, and click this button in the toolbar:
And then select the actions you want to enable:
This option enables you to define precisely where rows can be inserted, copied, or deleted in the model during Run model. Typically you would only enable this in the data entry section of your model.
Once created, you can call the row actions by clicking on one of the icons. Row actions are only active in Run models.
By creating a save query in your model, you can save information back into a data set.
For every save query, you can select how new data should be imported into the data set:
Once you have added a save query to a model, a Save button will automatically be added to the Run models screen, and the user can enter and edit data (in cells where you have created the above cell editors) and click the Save button when done.
XLReporting expects the row below your query to contain columns headers, and you can specify how many rows of data you want to import. If you combine save queries with report queries, the number of rows is automatically expanded based on the results of the report query.
For each column in your target data set, you can either select the relevant column in your model, a static value, or a function. You can also transpose columns into rows, convert the data, and filter the data. It works in the same way as Define imports.
When saving, XLReporting will try to process every row it encounters, up until the number of rows you have specified.
The option when empty values enables you to either skip entire rows when a field is empty, or repeat the values from the previous row (which is useful if your model data is grouped).
When defining a model, you can use the Save and Actions buttons in the right-top of the screen:
These buttons enable you to do the following:
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 model to either a new dashboard, or to a new dashboard:
Click on Actions - Review this model to review a number of aspects of this model and its currently selected query: