AX1084

Using conditional calculations in Fixed Report components

When setting up a calculation in a Fixed Report component, you may want different rows to use different calculations. For example, in a report showing employee data, you might want to use different calculations for salaried versus hourly employees.

You can do this by using a separate ConditionalCalculations data source to define a list of conditions and calculations. For each row in the report, Axiom Financial Institutions Suite will evaluate the conditions in the list and apply the calculation for the first condition that it matches.

To set up conditional calculations for a Fixed Report component, do the following:

• Create a ConditonalCalculation data source to define the conditions. Each row of this data source defines a condition and its associated calculation.

• In the FixedReportColumns data source, enter the name of the ConditionalCalculation data source into the [Calculation] property of a [CalculatedColumn] item.

Creating the ConditionalCalculation data source

The tags for the ConditionalCalculation data source are as follows:

Primary tag

[ConditionalCalculation;DataSourceName]

The DataSourceName identifies this data source so that it can be used in a FixedReportColumns data source. (missing or bad snippet)

(missing or bad snippet)

Row tags

[Item]

Each row flagged with this tag defines a condition and corresponding calculation. Items are evaluated in the order listed in the data source.

Column tags

[Condition]

A condition to evaluate against each row of the report, to determine whether the corresponding calculation is applied to the data row.

Each report row is tested against the conditions in the data source, in the order that the items are listed in the data source. If the report row matches the condition, then the corresponding calculation is used for that row. No further conditions are evaluated for that row.

The condition can use any operator that is normally supported for filter criteria statements in Axiom Financial Institutions Suite, such as greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=), equals (=), and does not equal (<>). SQL IN and LIKE syntax can also be used. You can use AND or OR to create compound statements. The condition can use any database column that is supported for use in the [Calculation] field.

You can define an item row with a blank condition, to serve as a catch-all for any report rows that do not match any of the other defined conditions. If you want to use a blank condition row, that row must be the final row in the ConditionalCalculation data source.

If the ConditionalCalculation data source does not have a blank condition row, and a report row does not match any of the defined conditions, then no calculation is applied and the calculated column displays a zero value for that row.

[Calculation]

Defines the calculation to use for the calculated column, when the row in the report matches the corresponding condition.

Enter the desired calculation as a text string, without an equals sign. The calculation must consist of valid column names and one or more of the following operators: addition (+), subtraction (-), multiplication (*), division (/), remainder (%), or unary negation (-). For example:

GL2021.M1+GL2021.M2

This calculation displays the sum of the two columns for each row.

Use parentheses to determine calculation order, such as: (GL2021.Q1-BGT2021.Q1)/BGT2021.Q1.

The following column names are valid for use in the calculation:

• Database columns: You can use regular table column names (GL2021.M1), calculated field names (GL2021.TOT), and column alias names (CYA_TOT). Table columns and calculated fields must use full Table.Column syntax. You can use any database column that would be valid for inclusion in the FixedReportColumns data source, though the column does not have to be in the data source in order to be used in the calculation.

• Calculated column names defined in the FixedReportColumns data source: You can use the names of previously defined calculated columns in subsequent calculations. For example, imagine that you have a calculated column named Difference that uses the calculation GL2021.Q1-BGT2021.Q1, and you want the next calculated column to show the percent difference. As long as the [CalculatedColumn] row defining Difference is above the row defining Percent Difference, then you can write the percent difference calculation as follows: Difference/BGT2021.Q1.

• Columns with unique names defined in the FixedReportColumns data source: If you have defined a unique name for a database column in the [ColumnName] field—such as GL2021.TOT;TOT22—then you can use this unique name in the calculation.

  This option is intended for cases where you have defined an alternate aggregation or a column filter for the column, and you want to use these results in the calculation instead of the raw column values. In this example, the unique name TOT22 could be used in the calculation. Assuming this column has a defined column filter, the filtered results will then be used in the calculation.

NOTE: If [IsPreAggregationCalculation] is True for the calculated column, then the calculation can only use database columns, and the database columns must be present on the primary table or a lookup table. For more information on how this option affects the conditional calculations, see Design considerations.

Numbers can also be used in the calculation—for example, CPREQ2021.TOT/12.

To create the ConditionalCalculation data source, use the right-click wizard: Create Axiom Form Data Source > Conditional Calculation. The following is an example data source with three condition rows:

Example ConditionalCalculation data source

To use this data source in the report, you would create a [CalculatedColumn] row in the FixedReportColumns data source, and then enter the name of the ConditionalCalculation data source name into the [Calculation] column. In this example, you would enter VariableCalc.

Example calculated column configured to use a ConditionalCalculation data source

When the report is rendered, each row is evaluated against the conditions in the ConditionalCalculation data source to determine which calculation to use. In this example, rows matching account 5300 use the first defined calculation, and rows matching account 5400 use the second calculation. All other rows use the final calculation with a blank condition.

For a more detailed example of using conditional calculations, see the Data Grid component example. The same example could be set up for a Fixed Report component.

Design considerations

• When a ConditionalCalculation data source is used, the calculations in the data source still honor the [IsPreAggregationCalculation] property for the calculated column. This determines whether the calculations are performed on post-aggregated data (the default behavior) or pre-aggregated data. For more information, see [IsPreAggregationCalculation].

• If the calculated column is applied post-aggregation (the default behavior), then the conditions in the data source should be set at the same level as the sum by level, or at a "higher" level. For example, if the sum by level is Acct.Acct, then the conditions can reference either Acct.Acct or an Acct grouping such as Acct.Category. This ensures a one-to-one relationship between the rows in the report and the conditions to evaluate against.

  If instead the conditions are defined at a lower level than the sum by level, then the conditions will be evaluated against the max value of the aggregated row. For example, if the sum by level is Acct.Category, but the condition references Acct.Acct, then the condition will be evaluated against the maximum account code for all the rows that were aggregated into the category row.

  Imagine that account category Revenue has 3 accounts: 4000, 4100, and 4200. If the conditions are defined at the account category level, then it is straightforward to determine which condition will be applied to the Revenue category row. However, if the conditions are defined at the account level, then Axiom Financial Institutions Suite has to determine a single account to associate with the category-level row. In this case, it will be account 4200, which is the maximum account number of the three rows that make up the Revenue row. This may be confusing if the individual accounts in the category would otherwise be associated with different conditions.

• If the calculated column is applied pre-aggregation, then the conditions in the data source can be set at any level. Each row of the pre-aggregated data is evaluated against the conditions. When the data is aggregated based on the sum by level of the report, the calculated value of each pre-aggregated row is then summed together to result in the final value for the report row. This means that a single aggregated row of data in the report may be comprised of data resulting from several different calculations, if the pre-aggregated data matched different conditions.

• If the calculated column is applied post-aggregation, and the report contains a subtotal or total row, then it is recommended to omit the calculation from the total row. If it is not omitted, then Axiom Financial Institutions Suite will attempt to apply the conditional calculation to the total row, using the max value for the aggregated rows. This may result in unexpected data or errors, depending on the condition and the underlying data. This is not an issue if the calculated column is applied pre-aggregation, because in that case the calculated column values are summed rather than applying the calculation to the total row.