In November 2022 we added functionality to Account Trees that enabled you to insert KPI rows directly to the body of the account tree layout. Since KPIs have their own number formatting, all Calxa report templates were updated to support the dynamic number formatting required. Templates customised prior to Nov 2022 do not contain these changes and require manual updates to support KPIs rows.
Do I have to upgrade my legacy custom templates?
No, you do not have to upgrade your existing custom templates. An upgrade is only required if you wish to include KPI rows in your Account Trees and use that tree on your custom report.
Can I get help with the required changes?
Yes, we'd be happy to help. Click here to book a time where we can review your requirements.
How to Guide
Follow along with this video guide or use the steps outlined below in order to ensure all expressions are updated as required.
Upgrade Template
When you open a legacy custom template it will display an option to Upgrade or Continue.
Click Upgrade to convert the template to the new format. This will convert the template from the previous model which used data bindings and format rules to a new model with expressions.
Note: 💬 when you click Upgrade we'll make a backup copy of your template before upgrading.
Warning: ⚠️ after upgrading you may have unpredictable output from this template. The following manual adjustments are required before you can reliably use the custom template.
Report Band Expressions
These expressions are applied to report bands and the Visible expression property. Please note, the exact name of the band may vary according to the exact template you have customised but the same general layout will exist in all P&L & Balance Sheet layout reports.
Detail1 -> Visible
iif([ShowBusinessUnitName] = True, True,?)
DetailReport2 -> Visible
iif([ShowAccounts] = False, False,?)
GroupFooter1, DetailReport4 & DetailReport5 -> Visible
iif([ShowKPI] = False, False,?)
Table Row Expressions
These expressions are only applied to the tables inside the DetailReport2 Band and at the row level. Please note it's easiest to navigate rows using the Report Explorer.
1st Row -> Visible
iif([AccountLevel] == 1, True,?)
2nd Row
On the 2nd table row you should apply the Visible, Background Color and Bold expressions. All other expressions such as Border Color and Foreground Color should be removed.
Visible
iif([AccountLevel] == 1, False,?)
Background Color
Iif(
[AccountLevel] > 1 And [HasChilds] And [ReportLevel] > [AccountLevel], rgb(245,245,245),
[RowItemTypeId] == 'Kpi', rgb(254,242,228),
[HighlightOption] = 'ExceptionHighlight', rgb(230,243,251),
?)
Bold
Iif(
[AccountLevel] > 1 And [HasChilds] And [ReportLevel] > [AccountLevel], True, [AccountLevel] = ? And [RowItemTypeId] != 'Kpi', True,
?)
Total Underline Table Rows
The 3rd through 7th table rows are the very small rows that control the total underlines on the resulting report. Apply the following expressions to the 3rd through 7th table rows and the Visible expression property as follows.
3rd & 4th Rows -> Visible
Iif([AccountLevel] = ? And [AccountTypeId] <> ?, True, ?)
5th Row -> Visible
Iif([AccountLevel] = ? And ([AccountTypeId] <> ? Or ([AccountTypeId] = ? And [IsLast])), True, ?)
6th & 7th Row -> Visible
Iif([AccountLevel] = ? And [AccountTypeId] = ? And [IsLast], True, ?)
Cell Level Expressions
For each cell in the 2nd row ensure all expressions are removed and apply only the required expressions listed below as required by the specific column type in the report.
Background Color (Static Actuals Columns)
This rule is applied to Actual columns only, where the column is shaded dark and only for reports where the Actual column is fixed like Actual v Budget for example. If the column is dynamically shaded like a multi-period report see the conditional columns expression below.
Iif(
[AccountLevel] > 1 And [HasChilds] And [ReportLevel] > [AccountLevel], rgb(232,232,232),
[RowItemTypeId] == 'Kpi', rgb(241,229,215),
[HighlightOption] = 'ExceptionHighlight', rgb(217,230,238),
?)
Background Color (Conditional Actuals Columns)
This rule is applied to multi-period reports where the columns conditionally change from Forecast to Actual. In this rule the [Month1Info] variable should be replaced with the actual info required. In a monthly template for example, you'll use [Month2Info], [Month3Info] and so on but in a Quarterly template you'll use [Q2Info], [Q3Info] and so on.
Iif(
[Month1Info] = 'Actuals' And[AccountLevel] > 1 And[HasChilds] And[ReportLevel] > [AccountLevel], rgb(232, 232, 232),
[Month1Info] = 'Actuals' And[RowItemTypeId] == 'Kpi', rgb(241, 229, 215),
[Month1Info] = 'Actuals' And[HighlightOption] = 'ExceptionHighlight', rgb(217, 230, 238),
[Q1Info] = 'Actuals', rgb(245, 245, 245),
?)
Background Color (Comparison Style Reports)
This rule is applied to reports with columns for Business Units or Organisations like P&L Comparison. In this rule, the [BusinessUnit1IsHeading] variable should be replaced with the actual info required. For example [BusinessUnit2IsHeading], [BusinessUnit3IsHeading] and so on.
Iif(
[BusinessUnit1IsHeading] And[AccountLevel] > 1 And[HasChilds] And[ReportLevel] > [AccountLevel], rgb(232, 232, 232),
[BusinessUnit1IsHeading] And[RowItemTypeId] == 'Kpi', rgb(241, 229, 215),
[BusinessUnit1IsHeading] And[HighlightOption] = 'ExceptionHighlight', rgb(217, 230, 238),
[BusinessUnit1IsHeading], rgb(245, 245, 245),
?)
Foreground Color
This rule is applied to all table cells that display number values and you'll replace the placeholder [Actuals] with the correct variable for each column in the report. You can use the Text expression on the same cell to identify what variable is being displayed in this cell.
Iif(
[Actuals] != ? And [Actuals] < 0 And [RowItemTypeId] == 'Kpi', rgb(255, 0, 0),
[Actuals] != ? And [Actuals] < -0.5, rgb(255, 0, 0),
?)
Text (Value Columns)
Iif([RowItemTypeId] == 'Kpi',
FormatString([RowFormatString], [Amount]),
[Amount])
Text (Variance Columns)
Iif([RowItemTypeId] == 'Kpi',
FormatString([RowVarianceFormatString], [Amount]),
[Amount])
Text (Percentage Variance Columns)
You can essentially leave these cells unchanged.
[Amount]