In this article:
Transpose | Convert | Lookup | Links | Move | Replace | Repeat | Debug

Tutorials - Expert tasks

We have documented a number of typical expert tasks for you:

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.

screenshot define imports transpose

Read more: Transpose

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. Some examples:

Examples:
LEFT(name, 10)
UPPER(name)

screenshot account convert data

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.

screenshot account lookup mapping

Read more: Lookup 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).

screenshot account linked data sets

Read more: Link data | Edit data

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.

screenshot define imports source

Replace data

When you create an import, you need to decide how new data correlates to the existing content of the data set: do you want to simply add to the existing content, or do you want to completely overwrite all existing content? Or selectively replace existing content based on certain criteria?

By default, existing data will be deleted if the fields that you selected to replace upon, match the new data that is being imported. But you can also select the option Delete using filter values, which will delete data if it matches the results of filters on the fields that you have selected to replace upon, irrespective of the new data that you are importing.

screenshot define import columns replace

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 tend to be fairly simple to configure, but in some cases you might want to understand what's going on inside XLReporting when it is running your reports, models, or imports.

At any time, you can open up the "Developer Console" of your browser to see the results of the underlying processes:

screenshot debug objects

Read more: Debug objects

Recommended reading:
Back to top | Tutorials | Reports | Imports