Everything in XLReporting is configurable. That's great as it gives you maximum flexibility to
adapt it to your requirements. But it can also be tricky to know what the best approach is to a given
problem. Surely you want to avoid unnesessary complexity. How do you know what's best?
For that reason we summarized our experience and that of our customers into best practices.
Clean your data
- Efficient reporting does not only depend on the software tool. By far the most important
thing is having a good structure in your data. As obvious as that may seem, the reality is
often that people don't spend enough time on (re)structuring and cleaning their data.
- Please ensure that you use structured coding for all "dimensions" in
your business. Do you have multiple companies, business units, cost centers, regions,
products? We call these "dimensions" because this is how you can aggregate and view your
business information. Create properly coded lists for each of these, without
inconsistencies, and ensure that you can easily aggregate and group them.
- If you use a Chart of Accounts, ensure that it can be grouped and subtotalized at the right
levels to meet your reporting requirements.
- People often work with bad source data: partial or incomplete, or copies of
some other data. This makes your reporting process very fragile. XLReporting can import any
data from any source, so it is best to go back to the original source (usually your finance
or other systems), and use the original data. One source of truth!
- Use the YYYY-MM convention for financial periods so you can use our built-in functions that
deal with fiscal years, and calculations such as period-discrete versus year-to-date
How to define
- Create concise, meaningful, and self-explanatory names for all objects. You can change them
at any time. Don't go cryptic. Spaces in names are perfectly fine. When you have more than a
dozen or so objects, start to use groups to keep your menu navigation
- Use the Description field to enter instructions and explanations for other
- Don't litter. Don't keep old copies of objects hanging around. It is confusing for everyone.
Delete them, they are easy to re-create when you need something similar later.
- If you really need to keep some old stuff, lock it or move it into
separate groups to it does not get in the way of normal day-to-day
- While you are editing objects, lock them temporarily so other users can't
inadvertenty access them.
- Start with our demo data, and expand from there. It's easier than starting from scratch.
- Design your data sets in such a way that is optimal for reporting. Don't feel bound by how
your source data or existing spreadsheets look like, because you can convert that during the
data import anyway.
- Think of data sets as simple tabular lists (e.g. like worksheets in Excel),
not as rigid tables in a complex relational database. Data sets are meant to be flexible and
- You can link data sets to one another, but don't go too far. Don't link more than 3-4 data
sets. Keep your data simple and flat. We deliberately did not design XLReporting as a
relational database because that usually is too rigid for most reporting purposes.
Add extra columns for information that you often use in reports. For example, storing your
actuals amounts and budget amounts in separate columns makes it easier to add variance
analysis to your reports. It doesn't matter if you don't fill all columns on every row.
- Choose the appropriate data type for every column, because it adds
validation, helps the users to enter data, and it ensures good quality of your data.
- Import your transaction data from other systems, but not your lookup lists (e.g. list of
companies, business units, Chart of Accounts etc). Edit those manually in XLReporting. This
gives you more flexibility, as you will be able to regroup or reclassify things for
reporting purposes without being constrained by your other systems.
- Think carefully about the dimensions in your business, and the aggregation
or grouping levels. Add those as columns to your lookup lists (e.g. list of
companies, business units, Chart of Accounts etc) so that you can use that information in
- Always go back to original source data. You can easily convert its format anyway. Don't
import copies of something - often such data is incomplete, incorrect, or out of date.
- Perform as much logic as possible during your imports, so you don't have to do that in your
reports. Examples are:
- Clean up, validate, and recalculate your source data.
- Currency translations (look up FX rates and translate currency amounts).
- Chart of Accounts mapping (if your companies use different account numbering).
- Optimize the data for reporting purposes.
- It is perfectly fine to create multiple imports going into the same data set. For example,
if you have multiple accounting systems, you can still import all of them into one and the
same data set. Each import has its own source and follows its own logic.
- Start with our demo reports, and expand from there. It's easier than starting from scratch.
- Create one report for every clearly defined purpose, and then create multiple layouts for
the different "views" on that same information. That way you only have to build logic once.
- Don't create copies of reports just to cater for small variations. It will be difficult to
maintain over time. It is better to do this with multiple layouts within the same report, or
with user filters.
- Don't create reports that merely show a subset of data in other reports. If you want to
restrict the data for certain groups of users, you can do that much more efficiently through
user roles (which enable you to set report filters).
Minimize complex logic and calculations in your reports. It is better to do this during the
data import, so it only needs to be done once and can then be stored. Add extra columns to
your lookup lists to classify or group data, instead of doing this with calculated fields.
- Use SELECT() filters as much as possible. These ensure that users have to make a selection
(e.g. choose a period) and the report is filtered accordingly, which keeps them smaller and
- Learn to use the SELECTPERIOD() and PERIODSUM() functions to build powerful and dynamic
period-discrete and year-to-date calculations with just a few clicks.
- Start with our demo models, and expand from there. It's easier than starting from scratch.
- Models may look and feel like spreadsheets, but they are not meant to be used as
spreadsheets. Don't try to replicate your complex spreadsheets. Models are much more
powerful, they are really templates that only need to contain queries, formulas, and
formats. All data is pulled in realtime whenever you open a model. This ensures proper
separation between logic and data.
- Build your models in small steps. Create multiple queries that each pull in a certain type
of information. If you use the same SELECT() filters in multiple queries, they are shared
and the user only needs to select them once.
- Use data queries in combination with query functions to pull desired (aggregate) data into
individual cells. Ideal for building KPI models that show lots of varied data.