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:

Bank Accounts displayed Cumulatively

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.

Changing Dataset to Transactional

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 Ⓒ.

Bank Accounts displayed Transactionally

Set accumulation

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.

 

Accumulate Data

In our example below, changing our accumulation option to Start of reporallows us to see the cumulative income growth which has occurred period to period within our report:

Forecasted Income Accumulation

Rounding

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.

Forecasted Breakdown of Incomes

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:

Applying Rounding of Two Decimal Places

Now we can relax in confidence that every penny has been accounted for!

Our Report with Rounding Applied

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:

Set Table Cell Widths

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:

Incomes Past 3 Months

To do so, we’ve reduced the size of our data columns and then expanded that available to our first column.

Adjusting the 1st Column Width

Column Formulas

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:

Applying Column Formulas

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:

Report with Column Formulas Added