IN THIS SECTION


Overview


NOTE: Custom Columns is a Beta feature and is being switched on for clients a few at a time.



Custom Columns let you build the columns of a report one by one. Each column has its own dates, so one report can show, for example, this month, the same month last year, the year to date and the full-year Budget side by side. You can also add Variance, Total, Average and Formula columns, and give any column its own companies, category or eliminations.


Without Custom Columns, the columns of a report come from the Report Filters you set on reports (Report Period, Compare settings etc.). With Custom Columns, the report has no single Report period: each column has its own. You would normally start by setting the report filters you require and then customise columns from there.


Where Custom Columns are available


Custom Columns are available on the Profit & Loss, Balance Sheet, Cashflow, KPIs and Combined reports, including their custom layouts. They are not available on the Trial Balance, A/R and A/P Ageing, Sales by Customer or Purchases by Supplier reports.


Opening the Columns editor


Run the report, then open the Columns menu above the report results and select Edit columns under Custom columns. You can also hover over the Columns button and click the edit pencil (Edit columns). On a report broken down by category, clicking the category button in the report filters also opens the Columns editor.


The Columns editor opens at the side of the page, with the report's filters along the top: Companies, Currency, Eliminations, and Category and Budgets to use where the report offers them. Below them is a preview of the column headers: click a header to select it, and the settings for that column appear underneath.


Use Undo and Redo at the bottom left to step through your changes. Click Save to apply your columns to the report, or Cancel to close the editor without saving.


Starting from a Column template


Click Column templates to start from a ready-made set of columns. Hover over a template to see what it contains. For the Month templates, choose Last month or This month first. The available Templates will depend on the report being viewed. Click Back to columns to return to the editor without choosing a template.


Adding columns


Click New column and choose the type of column to add. To add the same column next to every column, use Add to all columns. To add a column next to one column only, click the small arrow on that column's header (or right-click it) and choose from Add a column.


Period column


Choose Period column…, then set the dates:


  • Dynamic dates - a period that moves with the current date, such as This month, Last month, This financial quarter, This financial YTD, This financial year or Last 12 months.
  • Fixed months - a start and end month that do not change.


Then choose Actual, Budget or Forecast (Forecast is shown where forecasts are available). Click Add Column to add the column. The column will be created and highlighted and the details of it shown below. 


Previous Year, Budget, Forecast and % Sales


From the arrow on a column's header, add a Previous Year, Budget, Forecast or % Sales column for it.


Variance


Choose Variance… from New column to add the difference between any two columns: pick the first column, then the column to compare it with (vs). Click Apply to add the column. The column will be created and highlighted and the details of it shown below. From there you can choose to show the variance as an amount or a percentage.


TIP: When a column already has a Budget, Forecast or Previous Year column next to it, the arrow on its header (and Add to all columns) offers shortcuts such as Budget variance or Previous Year variance, which add the variance straight away.




Total and Average


Choose Total… or Average…, then tick the columns to add up or average. All ticks the months that add up, None clears the ticks. Then click Add Total (or Add Average) to create the column.


Formula


Choose Formula… to build a column from other columns, for example 40% of Company A + 60% of Company B, or one column divided by another. A formula can use brackets and can multiply or divide by a number, for example 50% x (A + B). When a formula divides, you can show the result as a percentage or as a ratio (with an 'x' suffix) and set the decimal places.


Rows such as Gross Profit Margin are worked out again from the Formula column's own figures. A Formula column cannot have its own Budget, Forecast, Previous Year or % Sales column, and a Formula column that divides cannot be added up in a Total or have a Variance taken of it. If you remove a column that a Formula column uses, the Formula column is removed too.


Once you've written your formula, click Add Formula, then type a name for the new column in its header.


Editing a column


Click a column's header to select it. The column is highlighted and a panel opens below it. The panel's title shows where the column sits, for example Oct 26 - Dec 26 · PY, followed by the dates it covers today (for example now Oct 25 - Dec 25). Click a part of the title to select that heading and every column under it. The settings for the selection are shown at the bottom of the panel.


Period


Click Period to change the column's dates, using Dynamic dates or Fixed months as above.


Companies, Category and Eliminations


By default a column uses the report's own setting, shown as Same as report. Click Companies, Category or Eliminations to give the column its own. For example, one column can show a single company, or one option of a tracking category, while the rest show the whole report. A Budget column also has its own setting to choose which budgets it uses. Budgets to use at the top of the editor sets the budgets for the whole report.


Renaming, moving and removing


  • Rename - click Rename (or double-click the header) and type the new name. Reset name goes back to the name Joiin gives it.
  • Clone - makes a copy of the column.
  • Move - use the left and right arrows, or drag the header to a new position.
  • Remove - removes the column. If other columns depend on it, Joiin asks you to confirm. A report needs at least one column, so the last column cannot be removed.


Splitting columns by company or category


To turn a column into one column for each company, or for each option of a tracking category, click the arrow on its header and choose By company or By [category name]… under Split. To split every column, use Split all columns.


Group columns by


When columns are split, choose how they are grouped under their headings with Group columns by: None (keep the columns in the order you set), Period, Company or Category.


Saving your columns


Click Save to apply the columns. The report runs with the new columns, and they are kept when you save the report as a Saved Report or add it to a Report Pack.


Custom Columns in Report Packs


Reports in a Report Pack can have Custom Columns too. Click the Columns icon on the report's toolbar to open the same Columns editor.


When you open Edit Report Parameters on a report with Custom Columns, the Dates and Compare settings are replaced by a Columns section: "This report uses custom columns (N). Each column has its own dates and comparison." From there you can Edit columns, or Remove custom columns... to go back to the Dates and Compare settings. Your custom columns stay in the saved report until you click Save.


Reports with Custom Columns


Report period


On a report with Custom Columns, the Report period button shows Custom columns. Clicking it gives two choices:


  • Edit columns - Each column has its own dates.
  • Remove custom columns… - Go back to one Report period for the whole report.


Columns menu options


Turning on Total, Average, Change, Percent of Sales or Budget in the Columns menu builds the columns from the dates and Compare settings again. Joiin asks first (Replace custom columns?), because this removes your custom columns. To add these to a report with Custom Columns, add them in the Columns editor instead.


Removing Custom Columns


Select Remove custom columns… from the Columns menu or the Report period button and confirm with Remove columns. The report goes back to columns built from the selected dates and Compare settings.