Expert tasks
On this page we explain a number of common expert tasks.
Transpose data
When importing from Excel workbooks, you may often find that information is laid out in columns, for example period-by-period amounts. XLReporting enables you to transpose that column data into rows with just a few clicks, so you can store it optimally in data sets.
By default, the transpose operation will skip empty values and zeros. If you want to transpose all columns onto all rows even if they are empty, you can do so by selecting the Also include empty option.
Convert data
In imports and reports, you can convert columns in the source data by using functions, expressions with multiple functions and operators, or even with scripts.
You can also watch the tutorial video.
Examples:
LEFT(name, 10)
UPPER(name)
You can also watch the instruction video.
Lookup data
A common scenario in imports is to look up some value in another data set, based on data in your import file or some other logic.
Link data
There are 2 ways in which you can link information between multiple data sets: use a dropdown list or a linked value. Dropdown lists are primarily used for validation of data (when data is being imported or manually edited), whilst linked values are used to connect data sets together for the purpose of reports (their combined data can then be used in the report).
Move data
You can move data from one data set to another by defining an import that has a data set or query as its source.
If you choose a query, you can create any desired transformation of your data. This enables you to transform, aggregate, and/or filter existing data and import the resulting data into another data set, for example if you want to summarize large data sets.
Replace data
When you define an import, you can decide how newly imported data relates to the existing content of the data set: do you want to simply add to the existing content, or update existing content, or completely overwrite all existing content, or do you want to selectively replace or delete existing content based on certain criteria?
If you choose to replace or delete data, you need to select one or more column(s) that will be used to decide which data to replace or delete. The import will use the filter values on these column(s) (e.g. SELECT, SELECTED, or fixed values) if these are defined, or else the realtime values in the source data, to replace or delete existing data.
The option Delete from is similar in that it deletes matching existing content in the data set, but without importing any new data.
Repeat data
A common scenario in imports is the need to "ungroup" grouped data, in other words repeat data from the row above where applicable. That will ensure every row has all the relevant data so it can be stored in a data set. That is often called "flat data". The option Repeat previous values in the When empty field will do just that.
The same option exists if you want to ungroup reports when using queries to pull data into models. In Define models - queries, the Options when running the model enable you to choose Repeat grouped values, so that you will get flat data into your model.
Whenever you export a report into a CSV file (which is a so-called flat-file format), XLReporting will automatically ungroup the report data for you and the CSV file will have all data on every row.
Finally, you can also use a function REPEAT() which gives you a finer level of control over how you want to repeat grouped values in imports, reports, and model queries.
Debug objects
Most objects are fairly simple to define, but in some cases you might want to understand what's going on inside XLReporting when it is running your reports, models, or imports.
If your user role has the Show console permission, you can open up the Developer Console of your browser at any time to see the results of processes and reporting objects:
