IN THIS SECTION


Overview


The Custom Report feature allows you to tailor the Profit & Loss, Balance Sheet and Cashflow Reports and layout the data as you require - each layout can be saved and run as and when needed.  Custom Reports can be used for many purposes including:


  • Creating custom layouts to meet statuary form requirements, e.g. GAPP, IFRS
  • Consolidating accounts with different codes/names into a single line
  • Performing calculations and presenting the results as a single item (e.g. KPI)
  • Displaying charts summarising report data


With a custom report you can:


  • Add, rename and delete groups
  • Move accounts between groups
  • Set groups to display Debit or Credit accounts as positive
  • Control how results and totals are displayed for a particular group
  • Hide groups - handy if you only want to see certain accounts/formulas in the report results
  • Add, rename and delete formulas
  • Change formulas to perform calculations with the totals from groups, other formulas and raw numbers
  • Add charts
  • Create a Copy of an existing custom report
  • Move groups and formulas up and down in the report and nest them in other groups
  • Remap your Chart of Accounts, See the video link below.


You can watch a video on re-mapping your Chart of Accounts here.


Types of Custom Report


You can create different types of custom report - the type you choose dictates the type of accounts that are included and the initial layout of the report. 


TypeAccounts IncludedDefault LayoutNew Accounts Added to
Profit & LossProfit & LossProfit & LossDefault P&L Groups: Revenue, Expenses, Cost of Sales, Other Income, Other Expenses
Balance SheetBalance SheetBalance SheetDefault Balance Sheet Groups: Current Assets, Non-Current Assets, Current Liabilities, No-Current Liabilities, Equity
KPIP&L and Balance SheetKPIDefault P&L and Balance Sheet Groups
CashflowP&L and Balance SheetCashflowDefault Cashflow Groups: Operating Activities, Financing Activities, Investing Activities, Cash
Combined P&L, BSP&L and Balance SheetP&L + Balance sheet, groups only, groups hiddenDefault P&L and Balance Sheet Groups
Combined P&L, BS, CashflowP&L and Balance SheetP&L + Balance sheet + Cashflow, groups only, groups hidden
Default P&L, Balance Sheet and Cashflow Groups


To create a new Custom Report


You can create a new Custom Report in one of two ways:


1. Click on the Create Custom Report button while viewing any of the main reports

2. Click on the Custom Reports menu item and click Add Report button, then select which type of custom report you would like to create


When you create a new custom report it will be laid out depending on the report type you selected (see table above).  From there you can make changes to the layout as described further in this guide.  Once you've made the required changes click on the Save button to save the report so you can run it or change it later.


Watch a video guide on Custom Reports here.


To view, edit, duplicate and run saved Custom Reports


Once you've created and saved a Custom Report you can access it again through the Custom Reports menu (or by pinning it to the main menu - see below).

  • Select Custom Reports from the main menu - a list of previously saved custom reports will be displayed
  • Click on the report name to run the report - the report filters can be selected in the same way as any other reports - see Running Reports
  • Click on the Edit  button on the right of the report to edit the layout
  • Click on the Delete button on the right of the report to delete the report
  • Click on the Duplicate button on the right of the report to create a copy of an existing custom report
  • If you have multi-client enabled, click on the Copy to Client option to copy that custom report to another client - select the client you wish to copy the report to, amend the name if necessary and click Copy


NOTE you can also edit a custom report directly from the report results page by clicking on the Edit Layout button.


Automatically Added Accounts

Sometimes, a new account is added to your Chart of Accounts - this may be because a new account has been added to an existing entity in the source system or you have added an additional entity to Joiin. When this happens Joiin will automatically add that account to any existing custom reports - so you will see the account appear when you run or edit the report. 


Joiin will attempt to add the new account to the default section for that custom report type. The default sections for each report type correspond to the sections you see on the standard reports. For example the P&L Report has Revenue, Cost of Sales, Expenses etc. section - each account in the Chart of Accounts is mapped to the relevant section - when a new account is added it will appear in that section in any Custom P&L reports. If the section does not existing because it has been customised by the user, then the section will be created again automatically. The user can then make any required changes to the report to move the account where required.


Marking Custom Reports as Favourite


You can mark a custom report as a Favourite - this makes it appear in the main reports menu on the left, making it more easily accessible.   In the Custom Reports list simply click on the star icon next to the report and it will be added to the left-menu.  You can then run the report just by clicking it in the menu like the standard financial reports.



You can also mark a report as Favourite while editing it or when viewing the report results.



Editing Reports


When you create a new report or edit an existing report you enter report edit mode.  Some things to note while you're in this mode:


  • In Edit mode the report will include all accounts from your Chart of Accounts of the type corresponding to the report type. When you run the report only the accounts corresponding to the selected Companies will appear.
  • You must click the Save button to save any changes you make.  The Save button will be greyed out initially but will become active as soon as you make any changes.  TIP: We recommend you save often when editing a custom report especially if you're making a lot of changes.
  • To run the report from this view click on the Run button.  If there are any unsaved changes the Run button will be greyed out until you save those changes.
  • In this guide we refer to the "three-dot" menu by which we mean the icon with three vertical dots that appears on every group and formula which, when you click on, opens up a menu of actions you can perform on that group or formula.


Changing the report name


Simply change the name in Report Name field and click on Save.


Searching/Filtering within a report


You can filter the groups and accounts you see when editing a custom report in a number of ways. The filter and search boxed below the report name allow you to do this.



Show hidden elements

Switching this toggle controls whether hidden elements are displayed while editing the report. Groups and formulas can be hidden so that they are not show when the report is run. However, while editing the report it is useful to see those hidden elements. Use this toggle to hide or display them as required.


Show archived accounts

Use this toggle to show or hide any archived accounts. If you have a lot of archived accounts it can be useful to hide them to reduce the amount of accounts to work with. 


NOTE you cannot delete a group unless it is empty and that includes archived accounts so you may need to switch this toggle on to see all archived accounts if you are having trouble deleting a group.


Search by name

To search for a group or account simply enter your search text in the search box on the left. Search will be performed across all groups, formulas, account codes and names and the matching items will be filtered as you type.  


Filter by company

You can select specific companies to filter by. To only show accounts for specific companies select the companies from the dropdown on the right.



Moving and copying accounts between groups


Accounts can be moved or copied from one group to another in one of the following ways:


One at a time

  • Click on the three-dot menu on the right of the account you wish to move
  • Select Move to or Copy to
  • Select the group to move the account to
  • Click on Save to save your changes


Bulk move

  • Select the accounts you wish to move by clicking on the checkbox - the Bulk Move box at the top will highlight how many accounts have been selected
  • TIP: you can select more than one account by clicking on the first account - this will highlight it green - then holding the shift button down and selecting the last account - this will select all accounts between the first and last.
  • When you've selected all the accounts you wish to move click on the Bulk Move button and select the group you wish to move them to - once moved your selected accounts will be cleared
  • To clear all the selected accounts, click on the Bulk Move button and select Clear Selected
  • To reselect the accounts you have just cleared click on the Bulk Move button and select Reselect last Cleared - helpful in case you moved accounts into the wrong group accidentally



NOTE: You may need to Expand the group first to see the accounts before you can move/copy them - see Collapse/Expand below.



Adding/Editing Groups, Formulas and Charts

Custom reports are made up of Groups, Formulas and Charts.


Groups are report sections containing accounts, or other groups and formulas - you can organise accounts into different groups of your choosing. Groups are indicated by the grey Group badge. The number of accounts, groups and formulas within a group is displayed below the group name.



Formulas are calculations performed on the group totals or results of other formulas.  Formulas are indicated using the green Formula badge. The formula itself is displayed below the formula name.



Charts are report sections that display data from groups or formulas in bar or line chart format. Charts are indicated by the Chart icon. 



Groups, Formulas and Charts can be arranged within the report in any desired order - they can also be hidden, collapsed and styled to achieve the required report output. This section describes how to make changes to Groups, Formulas and Charts to meet your report specification.


Quick Toolbar


You can quickly perform many of the actions described below using the Quick Toolbar. Simply hover your mouse over the Group, Formula or Chart to bring up the quick toolbar. Hover over each button to see what it does. In the section below, if an option is available through the Quick Toolbar it will be marked with QT.




Adding/Editing Groups


Groups are report sections containing accounts. The accounts that appear within each group of a newly created report are based on the accounts that are within the Chart of Accounts of all the Companies  you have linked.  

  • The accounts will appear in the standard report groups (Revenue, Expenses, Current Asset etc.) depending on the account mapping - see the Chart of Accounts section for more information on accounts mappings.  
  • The Balance Sheet and Cashflow reports have additional accounts (for Profit for the Year and Net Income respectively) which are added automatically by Joiin.
  • In the same way as Joiin merges accounts with the same name/code on the main reports, accounts are merged in the edit screen too.


To add or edit groups:

  • Click on the New Group button to add a new group
  • Alternatively, to edit an existing group, click on the Group Name or select Edit from the three-dot menu on the group
  • Make your changes to the group options - see below
  • Click on Accept to make the changes or Cancel to abort the changes
  • Click on the Save button to permanently save the changes


Options for editing groups


When editing a Group there are various options available. To show the group options, click on the group's name.


OptionDescription
Group NameThe name of the group that will appear when the report is run.

Show Total

Select this option to control whether the totals for the group are shown when the report is run. [Available in Quick Toolbar]

Collapse and Show Total

Select this option to control whether the individual accounts are shown when the report is run - this is helpful if you don't need to see individual account values but just the total.  Note you can quickly expand or collapse a group by selecting the Collapse/Expand option from the three-dot menu. [Available in Quick Toolbar]
Hide GroupSelect this option to hide the group entirely when the report is run.  This can be helpful if you have some accounts which you want to include in a formula but do not want to display the accounts or total separately - or if you want to remove accounts entirely from a report. [Available in Quick Toolbar]

NOTE: you can hide any hidden groups from the screen while editing by deselecting the Show Hidden Elements toggle.
Style as accountSelect this option to change the styling of this Group on the report output. With this option selected, when collapsed, the group will appear the same as a normal account. [Available in Quick Toolbar]
Page break beforeSelection this option add a page break before this group when it appears within a Report Pack.
This group containsThis controls how the results and totals are displayed for accounts in this group. You should set this to the correct setting depending on the type of accounts you will be placing within this group.

Profit & Loss - Displays the values of the accounts in this group as on the P&L, i.e. the account balance movements from the start of the period to the end
Balance Sheet - Displays the values of the accounts in this group as on the Balance Sheet, i.e. the account balances at the end of the period
Cashflow Cash - Displays the values of the accounts in this group as on the Cash section of the Cashflow, i.e. the account balances at the start of the period
Cashflow Activities - Displays the values of the accounts in this group as on the Activities section of the Cashflow, i.e. the account balance movements from the start of the period to the end, carries over from previous fiscal year

NOTE: on Nested groups this setting is disabled and automatically set to the parent group setting

Credit Positive | Debit Positive

Use this switch to control whether you want to show Debit Account (e.g. Expenses) balances as positive or Credit Account (e.g. Sales) balances as positive.

This also controls how budget variance figures are shown - a Debit Positive group would show actual amounts over budget in Red and under budget in Green- the opposite applies for Credit Positive groups.
Budget FavourabilityControl how budget variance figures are displayed on reports.

Over budget - Display budget variance in GREEN if the actual value is over the budget value
Under budget - Display budget variance in RED if the actual value is over the budget value
Netural - show the budget variance in black, regardless of value


Adding/Editing Formulas

Formulas are calculations that are performed when a report is run. Groups and other Formulas can be combined with operators (PLUS, MINUS etc.), KPIs and Temporals to create formulas. 

  • Click on the New Formula button to add a new formula
  • Alternatively, to edit an existing formula, click on the Formula Name or select Edit from the three-dot menu on the formula
  • Enter or change the name of the Formula in the Formula Name field
  • Build your formula using the Formula Editor - for details on how to use the formula editor see this article.
  • Click on Accept to make the changes or Cancel to abort the changes
  • Click on the Save button to permanently save the changes


Types of Operand


When selecting operands for your formula you can choose from the following types.



Type
Description
GroupA group from within the custom report.
FormulaA another formula within the custom report.
KPI
A built-in KPI. This is a convenient way to access common values without having to calculate them yourself.
TemporalA built-in Temporal value which can be useful for certain types of formula. Currently there are two available temporals.

Days in Period - This function calculates the number of days in each period on the report. Can be used for calculating a daily value such as daily revenue, or combined with Days in Year, an annualised value such as annualised revenue.
Days in Year - This function calculates the number of days in the year for each period on the report. Can be used for calculating an annualised value such as annualised revenue.

Enter a value
This allows you to enter a fixed, numeric value into your formula - select this option and enter the required value.



* Note you cannot select another Formula as an operand if it includes the formula you're editing as an operand - this prevents circular references from occurring




Options for editing formula


When editing a Formula there are various options available.


OptionDescription
Formula NameThe name of the formula that will appear when the report is run.

Hide formula 

Select this option to hide the formula result when the report is run.  This can be used if you want to have intermediary formula (for use in more complicated calculations for example) that you do not wish to show the results for. [Available in Quick Toolbar]

NOTE: you can hide any hidden formulas from the screen while editing by deselecting the Show Hidden Elements toggle.
Hide if zeroSelect this option to hide the formula result if the results are zero.
Style as accountSelect this option to change the styling of this Formula on the report output. When this option is selected the formula will show as a normal account row. [Available in Quick Toolbar]

Result formatting

Select the format you wish to show the formula result using.  This can be either Plain, Currency or Percentage.

Numeric - results displayed as a number
Currency - results displayed as currency
Percentage* - results displayed as a percentage

* NOTE when Percentage is selected, the Total Calculation Method setting is disabled. This is because, with Percentage formatting, the Total Calculation Method will always be Calculate.
Total Calculation MethodThe method used for calculating the values in the Total column:

Sum - sums up each column
Start Balance - uses the start balance from the first column
End - uses the end balance from the last column
Average - takes an average of all the values in each column
Calculate - calculates the the total using the total values from the formula operands

See this section for more info
Budget FavourabilityControl how budget variance figures are displayed on reports.

Over budget - Display budget variance in GREEN if the actual value is over the budget value
Under budget - Display budget variance in RED if the actual value is over the budget value
Netural - show the budget variance in black, regardless of value



Total Calculation Method Explained



Adding/Editing Charts


  • Click on the New Chart button to add a new chart
  • Alternatively, to edit an existing chart, click on the Chart Name or select Edit from the three-dot menu on the chart
  • Enter or change the name of the Chart in the Chart Name field
  • Select the Chart Type: Bar, Line or Donut.
  • Select the Groups or Formulas you wish to include as the Chart series.  Each group or formula you select will appear on the chart as a bar, line or donut segment.
  • Click on Accept to make the changes or Cancel to abort the changes
  • Click on the Save button to permanently save the changes


Options for editing charts


When editing a Chart there are various options available.


OptionDescription
Chart NameThe name of the chart that will appear when the report is run.

Collapse

Select this option to show the chart collapsed when the report is run - the chart can then be revealed by clicking on the title if required. [Available in Quick Toolbar]
Start at zeroSelect this option to force the chart axes to start at zero. If this option is not selected the chart will automatically adjust the axes start point depending on the data.
Chart typeSelect the type of chart: Bar, Donut or Line.
Chart seriesSelect each group or formula you wish to appear as a chart series.

For Bar and Line chart, each series you select will appear as a separate bar or line on the chart in a different colour - the bars/lines will be repeated for each period/company/category selected in the report filters.

For Donut charts, if you select only one series, the donut segments will be made up of each period/company/category selected in the report filters. If you select more than one series then the donut segments will represent each of those series using the total values for all the period/company/categories selected in the report filters.



Moving and Nesting Groups


You can move groups, charts and formulas around in the report so they appear where desired.  You can also move groups or formulas to be nested within other groups.


  • Click on the three-dot menu on the group, chart or formula you wish to move
  • Select Move
  • Select To, Above or Below
  • Select the Group, Chart or Formula to move to/above/below
  • Click on the Save button to save your changes


Alternatively use the Quick Toolbar to move groups, charts and formulas up and down within the report.



Deleting a Group, Formula or Chart


To delete a group, formula or chart:

  • Click on the Group Name or select Edit from the three-dot menu on the group
  • Click on the Delete button
  • Click on the Save button to save your changes


NOTE Groups cannot be deleted if they have accounts in them or if they are referenced by formulas. Formulas cannot be deleted if they are referenced in other formulas.