How to Format a Report and Create Entity Members
1. Abstract
Financial statements often require indentation of account names, and special formatting of account names, values, column headers, totals & subtotals. Such formats may include highlighting, bordering, adding prefix/suffix, number formatting, and more. The dataview object can be fully customized leveraging different features and options that will give the desired appearance and meet the formatting requirements.
2. Context
Let’s imagine you are dealing with the creation of a financial statement with specific formatting. Empty rows, borders, bold formatting, and many other formatting requirements to deal with.
Anyone who has attempted building P&L, Income or financial reporting statements in Board knows how time-consuming the task can be. The reason is that data view, the standard object for tabular reports, is designed to perform slice & dice analysis on the data, and not to deliver formatted statements. (This is not only the case with Board but also common with other software)
With that in mind, let us take a look at some of the challenges in building P&L statements and how we can overcome them.
3. Content
We will provide you with a few best practices to achieve the final output (figure 2), starting from the Board default data view structure (figure 1), using a P&L Account stand-alone entity. You could be tempted by taking advantage of hierarchies' grouping and aggregating functionalities but due to nature of P&L subtotals which involve calculation rather than pure aggregation we suggest the use of a flat dimension combined with a rule for subtotals calculation (no Unbalanced Hierarchy or Relationships).
Row position
Financial statements typically follow a specific order of accounts. When we upload them in Board could be hard to achieve a desired sort order without workarounds such as having adding sort keys in the underlying table or using sorting functionalities. Even if the custom sort option is a game changer, the suggestion is to load the required order since the first load using codes that will give the possibility to include additional element in between without the need of re-sorting the entire list. For example:
010 Sales
020 Cost of good sold
030 Gross Profit
…
If in a second stage we realized that an additional line is needed (015 Other sales) it’s easy to manage the order.
Row indentation
To create the indentation, add consistently some spaces at the beginning of the entity member description. This will create the effect of the grouping.
Entity members formatting
Through the Format tile in the datamodel area is possible to create an ad hoc format for a specific entity. Defining the Row Template is possible to define the custom format not only for the entity member, but also for the value, the total and the grand total.
Referring to the figure 2, with the row template it’s possible to apply the bold formatting for group titles or subtotals.
In addition, it’s also possible to:
- Customize the background of the cell: this cloud be particularly useful to highlight the subtotals/ grand totals
- Customize the foreground. Increasing the size, changing the font or applying the italic format
Creation of empty rows
To give the effect of space between different P&L grouping, it’s necessary to add some dummy members in the Entity and then leverage the option in the Format tile to edit and customize the entire row to make it appear transparent. This could be achievable, applying a white foreground and background to the specific row.
Borders and Table style
It’s possible to modify the table borders, changing the color or the thickness. The feature is available Format Tile.
It’s possible to change the table style and disable the alternate rows highlighting directly in the data view setting under the table style section. Here there are some quick options to enable/disable and modify the appearance of the table.
In the data view setting bar there are also other options and features that can be used to customize the overall appearance of the data view.
Negative number formatting
While reporting financial information it’s sometimes necessary to display negative numbers in parenthesis. For that, there is an option that can be enabled in the Format tile, under the options section.
Add currency symbols after the values
There are different alternatives to add symbols in a data view cell. The suggestion is to use the options available in the Format tile in the data model section.
This was a list of examples on how to leverage some of the available options and features to customize a data view and obtain the desired appearance. Using the same features is possible to have full flexibility on the look and feel of the data view and any Board object.
Comments
-
Hi, thank you for this tip.
However, it could be great to add a new feature in the Row Style Template, which can easily allow us to play with indentation instead of adding some space in the member description to meet the Row indentation requirements.
Thanks
1 -
@Nicolas BENDONGUE you can meet the Row indentation requirements also by setting spaces in the “Symbol before” in the Format tile of the data model section. Regards
3 -
One might feel tempted to create the indentations by TAB. But it is also important to keep in mind that an entity extract uses TABs as column separators by default.
Reimporting such an extract would render the entity useless.3
