This article deals comprehensively with how to customise financial reports within Reportance. The reporting module in Reportance provides a great deal of flexiiblity already, however this article lets you go the next step and edit the underlying Excel templates used to generate the report.
This article assumes you are fluent in the use of Microsoft Excel and understand key concepts such as named ranges and hidden sheets.
Managing Report Templates
To see your available templates, login to https://workpapers.hownowhq.com/ with your Reportance credentials, select Report Settings, and click on Report Templates. The following link will take you straight there (once you are logged in):
https://workpapers.hownowhq.com/admin/reportsettings/reporttemplates
You will see a screen that looks similar to the following:
By default, you will have read-only access to the Reportance Standard template maintained by HowNow. Select this and click 'download' to get access to a copy of the template.
Creating Your Own Template
We suggest downloading the Reportance Standard template as a starting point. Note that all report templates are Excel Template files, with a .xltx extension. This means when you open them normally, a new document is created from them. To edit the template iteself, right click on the template file and click 'Open', as follows:
After making any edits to the template (refer to our Detailed Editing section below for more information), save the file and ensure it continues to be an Excel Tempalte (.xltx) document.
Next, click Add in the Report Templates area of the website (https://workpapers.hownowhq.com/admin/reportsettings/reporttemplates), give the template a meaningful name, upload the newly saved .xltx file and click Save.
Once this is done, you have the option of making this new template the default one if you choose. Simply select the report template from the list and press Set Default.
The report will now be available from a drop down menu within the Reportance Reports area. The selected report will always default to the report template marked with Set Default on the website above.
Detailed Editing
The report template system is powerful, but a number of rules must be complied with to get the expected results.
Adding/Removing Sheets
You may add any worksheets to the template that you wish. When you do so, they will appear automatically in the contents based on the sheet name. Note, these sheets will appear in all reports, and users won't have the option of turning them off when generating a report from within Reportance. Once in Excel, users will of course be able to delete or hide these sheets as required.
You may delete any visible sheet in the report template, noting that Reportance will no longer send data to these sheets once done so. The one exception to this is the 'Profit and Loss' sheet, which is used as a template when other 'financial statement' based sheets are required, for example, a trading account.
You can rename an existing sheet, however, note that Reportance will no longer interact with that particular sheet (for example to extract data). It is equivalent to deleting the Reportance sheet and inserting your own identical one with a different name.
Hidden Sheets
Reportance includes a number of hidden sheets by default that contain important data for generating the report. These all start with the name "Rep_", for example "Rep_Notes", "Rep_SubReports" etc.
The full list is as follows:
| Sheet Name | Role |
| Home | Contains basic report data as configured by the user when generating a report from Reportance. SYSTEM SHEET, do not edit or delete. |
| Rep_Notes | Lists the accounting policies and their Id's to be imported into the report. SYSTEM SHEET, do not edit or delete. |
| Rep_SubReports | Lists the report pages that should be listed or excluded, based on the user selections in the Reports screen in Reportance. SYSTEM SHEET, do not edit or delete. |
| Rep_Layout | Lists the layout options for each account, which stores the users selections when creating the Report as to how each account should be displayed (whether rolled up, expanded, or referenced to a note). SYSTEM SHEET, do not edit or delete. |
| Rep_FormatRules | Rules that specify ranges within the Excel report where the colour should be updated to reflect the Primary Brand Colour (as specified online) for the firm. If you are directly editing colours in the Excel report, you can delete all the rules in this sheet (leaving the first row) as you will be setting the colours manually. When creating rules, the Id can be any sequential number, the worksheet is the name of the worksheet to edit, the range is the range reference to change, and 'Foreground, Fill and Border' determine which colours for that range are updated to the Primary Brand Colour. SYSTEM SHEET, feel free to edit individual rows, but do not delete the first row or add/insert columns. |
| Rep_Depr | Lists the depreciation reports to be included within the report. SYSTEM SHEET, do not edit or delete. |
| Rep_Columns | Lists each column in the report with key system data. To get relevant column information for management reports, we suggest using Rep_ColumnData below. SYSTEM SHEET, do not edit or delete. |
| Rep_ColumnData |
Lists each column in the report, in sort order, with key data. Can be useful when creating management reports as you can use this table to determine what periods are presented. SYSTEM SHEET, do not edit or delete. |
| Rep_Settings | Lists key information used to determine the report connection and major report settings. A number of variables here ensure the financial statement content is imported into the right regions within each worksheet. These fields, ContentRowStart, TitleCell, and ColumnHeaderRowStart, are the only ones you should edit in this sheet. SYSTEM SHEET, do not delete, and limit editing to the values of ContentRowStart, TitleCell and ColumnHeaderRowStart. |
Management Data
In addition to the financial statements, Reportance dumps a table of data from the accounts which can be used to prepare useful management reporting information. This useful information is contained in a hidden sheet called Source Data. Do not edit or delete this sheet.
The Reportance report template uses this information and prepares another hidden sheet called Analysis Chart. This uses a range of formulas and referencing to extract values from both Source Data and Rep_ColumnData which it then presents in a neat tabular form which is easy to reference. The various graphical reports reference ranges from within this worksheet. You can happily edit the Analysis Chart, or create your own similar one to extract relevant information for yourself.
Editing Existing Sheets
As previously mentioned, you can easily add your own sheets into the Reportance template. You can also edit existing Reportance template sheets, the below section lists each page and provides relevant information to help assist in editing the worksheet for yourself.
Many pages refer to the Rep_FormatRules script. This is a script that updates the colours in various pages of the report by running through the list of rules provided in the Rep_FormatRules hidden sheet. In most cases, if you are changing colours in the report to your chosen colour, you do not need these rules, and so you can safely delete them (by deleting the relevant rows in the sheet). When deleting rules in this sheet, do not delete row 1, which contains the column headers.
Cover
The Rep_FormatRules script updates the colour of the title cell. No other scripts occur on this page.
You can edit this page freely. We recommend deleting all Rep_FormatRules that apply to this sheet if you do edit it, as it is easier for you to manually change the cells to your chosen colour.
Contents
The Rep_FormatRules script updates the colour of the title cell.
The contents are listed from Row 6 onwards.
No other scripts occur on this page.
You can edit this page freely, noting that any content from Row 6 onwards will be deleted by the contents listing when the report is generated.
We recomend deleting all Rep_FormatRules that apply to this sheet if you do edit it, as it is easier for you to manually change the cells to your chosen colour.
Compilation Report
The Rep_FormatRules script updates the colour of the title cell. No other scripts occur on this page.
You can edit this page freely. We recommend deleting all Rep_FormatRules that apply to this sheet if you do edit it, as it is easier for you to manually change the cells to your chosen colour.
Client Declaration
The Rep_FormatRules script updates the colour of the title cell. No other scripts occur on this page.
You can edit this page freely. We recommend deleting all Rep_FormatRules that apply to this sheet if you do edit it, as it is easier for you to manually change the cells to your chosen colour.
Balance Sheet, Profit and Loss, Appropriation Statement, Notes
These four reports, the 'meat' of the financial statements, all operate exactly the same, so are discussed together here.
The Rep_FormatRules script updates the colour of the title cell.
The Rep_Settings hidden sheet contains fields that determine where content is pasted into or edited, as follows:
| Field | Purpose |
|
ContentRowStart |
This row is the first row in which the contents of the financial statements will be pasted. This row and all rows below it will be deleted prior to pasting the report information in. |
| TitleCell | This records the cell location of the title. Reportance uses this to update the title when copying the Profit and Loss statement to generate trading or divisional statements. If you change the location of the title in your template, ensure you update the range in this cell. |
|
ColumnHeaderRowStart |
Column headers contain up to three rows, and are edited by the user when generating a report from Reportance. The ColumnHeaderRowStart determines the row at which the first row in the column headers will appear. You must then allow an additional two rows for the remainder of the column header to be pasted. For example, if the ColumnHeaderRowStart is specified as row 6, the column headers will be pasted in Rows 6 to 8 in these sheets. |
If you change the number of rows in your header, make sure you update these values in Rep_Settings accordingly so the content is pasted into the right area.
Other Rules:
Do not delete the hidden first row.
Do not add/remove or reorder columns as the content will be pasted incorrectly. You can add/remove rows above the point at which the financial statement content is pasted (ContentRowStart).
Please note, when producing trading accounts, Reportance will copy the Profit and Loss worksheet to use as a template for these (updating the title as necessary). Any changes to the profit and loss sheet will accordingly also apply to any trading account or divisional statements.
Taxable Income Reconciliation
The Rep_FormatRules script updates the colour of the title cell. No other scripts occur on this page.
You can edit this page freely. We recommend deleting all Rep_FormatRules that apply to this sheet if you do edit it, as it is easier for you to manually change the cells to your chosen colour.
Income Results, Investment Results, Cashflow Results, Working Capital
These four sheets make up the graphical story boards for the client. They all operate exactly the same, so are discussed together here.
All of these sheets have extensive rules in the Rep_FormatRules hidden sheet, to change the colours of the cells and borders to reflect your primary brand colour. No other scripts occur on this page.
You can edit this page freely. We recommend deleting all Rep_FormatRules that apply to these sheets if you do edit them, as it is easier for you to manually change the cells to your chosen colour.
Depreciation Reports
These are generated by code script and cannot be customised, with the exception of page footers, which are inherited from the profit and loss statement.