Best practices

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

1. 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.
2. 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.
3. If you use a Chart of Accounts, ensure that it can be grouped and subtotalized at the right levels to meet your reporting requirements.
4. 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!
5. 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 amounts.

How to define

1. 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 simple.
2. Use the Description field to enter instructions and explanations for other users.
3. 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.
4. 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 menu navigation.
5. While you are editing objects, lock them temporarily so other users can't inadvertenty access them.

Data sets

1. Start with our demo data, and expand from there. It's easier than starting from scratch.
2. 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.
3. 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 visual.
4. 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.
5. 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.
6. 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.
7. 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.
8. 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 your reports.

Imports

1. 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.
2. 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.
3. 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.

Reports

1. Start with our demo reports, and expand from there. It's easier than starting from scratch.
2. 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.
3. 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.
4. 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).
5. 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.
6. 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 faster.
7. 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.

Models

1. Start with our demo models, and expand from there. It's easier than starting from scratch.
2. 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.
3. 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.
4. 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.