Controlling how data displays on a table
Find out how to control how your data displays on a table, from setting Rounding options and adding Column Formulas, to adjusting Table Cell Widths, Data Sets and more.
Written by Ross MacLeod
Data isn’t just for charts! As well as controlling how your data displays on a graph, you can also control how it appears within your table. In this guide we’re going to look at exactly that. We’ll be covering:
- Selecting data sets
- Choosing how you accumulate cumulative data
- How to set rounding
- Adjusting table cell widths
- Adding column formulas
Set data set
By default we’ll display your profit & loss accounts transactionally, and data from balance sheet accounts cumulatively. However, you can override this behaviour on an item-by-item basis using the Set Data Set option. This allows you to view accumulation of profit & loss data, such as your incomes, historically or within a window, and movements within balance sheet lines, such as cash in or out, transactionally.
In our example below, we’re currently displaying our bank accounts data cumulatively for three quarters. However, as the title and subtitle of our table implies, we’d actually like to view the cash movements in and out of our bank accounts category and lines:
To achieve this, we need to change the dataset of our bank accounts row to transactional. If we expand our bank accounts in the side panel, we’ll find that we currently have this row set to Use the dataset: Cumulative. Using the dropdown, we can change this to transactional to achieve our desired objective.
To add clarity to the figures on display, whenever you set a balance sheet account to display data transactionally we’ll add a Ⓣ adjacent to the item. Likewise, when setting a profit & loss account to cumulative, we’ll add a Ⓒ.
Data set to display cumulatively is automatically accumulated from the point that the org was created, giving you, in the case of bank accounts, the actual, total value within your account at period end. But what if you want to view the accumulation of this data within a particular reporting window instead? That’s where accumulation options come in. If you expand any cumulative data item you’ll find the option to set how that data is accumulated. This can ether be from org creation or since the starting point of your current report.
In our example below, changing our accumulation option to Start of report allows us to see the cumulative income growth which has occurred period to period within our report:
Any data added to a table will be rounded to 0 decimal places unless otherwise stated. This can be changed on an item-by-item basis, allowing you to include pennies where appropriate or even round up or down your data to tens, hundreds, thousands and millionths. Below we’re added a table to look at our forecast incomes, but we’ve decided we’d like to see this data displayed to two decimal places.
To do so, we simply need to expand our Income category in the rows section of the side panel and then choose our desired rounding:
Now we can relax in confidence that every penny has been accounted for!
Set table cell widths
Sometimes you may want to squeeze just one more column into your table or enlarge your table’s first column so you can see the full name of an account. Fortunately, the table cell width section allows you to adjust these settings, so you can ensure that your table’s columns display perfectly to the size you desire:
In our example below, we’d like to enlarge the space available for our income accounts to fill so that we can see the name of each account in more detail:
To do so, we’ve reduced the size of our data columns and then expanded that available to our first column.
Adding column formulas to your reports is a quick and powerful way to gain more insight into your data. You can add column formulas to easily explore metrics such as variance period to period, data averages, financial year and report totals, start/end variances.
Select the column formulas section and then start adding different options to explore how they liven up your report:
Below we’ve added an average, a financial year at report end total and a financial year total, to give us more meaningful insight into our figures: