AX1041
When setting up a calculation in a Data Grid component, you may want different rows to use different calculations. For example, in a grid 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 grid, Axiom 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 Data Grid 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
[Calculation]property of a[CalculatedColumn]item.
Creating the ConditionalCalculation data source
The tags for the ConditionalCalculation data source are as follows:
Primary tag
Row tags
Column tags
NOTES:
- The primary tag must be placed in the first 500 rows of the sheet.
- Formulas can be used to create the tags, as long as the initial bracket and identifying keyword are whole within the formula.
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 grid, you would create a [CalculatedColumn] row in the [Calculation] column. In this example, you would enter VariableCalc.
Example calculated column configured to use a ConditionalCalculation data source
When the grid 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.
Conditional calculation example
Imagine that you want to display an employee roster in the grid, with a calculation of the projected salary for the next year. The data and calculations that you want to apply may vary depending on whether the employee is hourly or salaried.
This example uses conditional calculations for two purposes:
- To display the current base pay for each employee, we want to return the value from different columns depending on whether the employee is hourly or salaried. This calculation references the CurrentBase data source.
- To display the calculated current salary for each employee, the calculation needs to be made differently depending on whether the employee is hourly or salaried. This calculation references the TotalSalary data source.
Example calculated columns referencing ConditionalCalculation data sources
In the ConditionalCalculation data sources, there are different conditions defined to determine whether the employee is hourly or salaried, and then apply the appropriate calculation.
- The CurrentBase data source uses the calculation field to display values from different table columns for each employee—the pay rate for hourly employees, and the base salary for salaried employees.
- The TotalSalary data source uses the calculation field to apply a different calculation for each employee. The calculation for hourly employees incorporates their scheduled work hours to calculate the projected salary, whereas the calculation for salaried employees can simply add the proposed adjustment to their base salary.
Example ConditionalCalculation data sources to define different calculations for hourly versus salaried employees
In the rendered data grid, the rows associated with salaried employees and hourly employees use the corresponding calculations from the CurrentBase and TotalSalary data sources.
Example data grid using conditional calculations
-
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. -
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 grid 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 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 grid, the calculated value of each pre-aggregated row is then summed together to result in the final value for the grid row. This means that a single aggregated row of data in the grid 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 grid contains a total row, then it is recommended to omit the calculation from the total row. If it is not omitted, then Axiom 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.
