uxui_grid_excel and uxui_grid_excel_compute
Use the
uxui_grid_excel and
uxui_grid_excel_compute properties to calculate values of columns in a grid with numeric questions. To produce totals for categorical questions with associated values, see the
CategorySum template.
Using computation in a grid
To use computation in the grid, set uxui_grid_excel to true at the grid level.
Add a uxui_grid_excel_compute property to the category to use for the computation. This control will be set as read‑only.
Calculating a total
To display a single total at the bottom of the grid, set the uxui_grid_excel_compute property at the grid level.
Specifying a calculation
To specify a calculation, use the exp attribute of the uxui_grid_excel_compute property.
In the exp attribute, select the rows by using Rn and the columns by using Cn, where n is the index for the row or column, starting from 0. The 0 row or column is usually the header, so generally the exp attribute uses rows or columns starting with 1, unless the header is hidden.
To use a specific cell, use CnRn to specify the row and column.
When the uxui_grid_excel_compute property and associated exp attribute are defined at the grid level, specify the cells by using CnRn notation; do not specify only the rows or columns.
Range expressions similar to Excel are exported by using a S:E notation, where S is the starting row, column, or cell, and E is the end row, column, or cell.
Operators in an expression
The expression can include all the numeric operators, and the SUM function.
SUM(C)
Calculates the sum of the rows above this item in the column, but after any row where uxui_grid_excel_compute is defined, providing a subtotal.
SUM(C, *)
Calculates the total of all values entered in the column.
SUM(R)
Calculates the subtotal for the row.
Examples of the exp property
The following example adds column 1 and column 2:
exp: 'C1 + C2'
The following example adds rows 5 and 6, and then multiplies the total by row 7:
exp: 'SUM(R5:R6) * R7'
The following example adds all rows preceding this row, but after any rows where a uxui_grid_excel_compute property is defined.
exp: 'SUM(C)'
The following example adds cells 1.1, 1.2, 1.3, 2.1, 2.2, 2.3, 3.1, 3.2, 3.3:
exp: 'SUM(C1R1:C3R3)'
The following example adds cells 1.1 and 2.2.
exp: 'C1R1 + C2R2'
Specifying labels for the result values
When using the uxui_grid_excel_compute property at the grid level, the default label is “TOTAL:”. It is displayed at the bottom left of the grid.
To change the labels, use one of the following properties. If a substitution is required or if the label needs to be translated, use totalLabelId.
totalLabel
Specify the label to be displayed to the left of the total. To remove the label, specify an empty string.
totalLabelId
Use
totalLabelId when the sum needs to be embedded in the label string. In this case, the string for the total is extracted from a metadata resource instead of a custom property, so this method can also be used if translated labels are required for the total. For more information, see
Translating text in the UXUI custom properties.
When using the totalLabelID, include %{SUM} in the label to indicate where to put the running total.
Setting the precision
To set the precision used when displaying double types, include the precision attribute. The following example adds rows 5 and 6, divides by 7, and then displays the result to 1 decimal place:
exp: 'SUM(R5:R6) / R7, precision: 1'
Possible values
uxui_grid_excel can be true or false.
uxui_grid_cell_compute contains an expression.
Applies to
Grids with numeric questions
Example
Metadata
In the following example, some styles and iterators or questions have been omitted to reduce its size.
RunTotalBoth "How many of each type of drink do you drink each day? (This example uses SUM(C) and SUM(R) notation to provide subtotals and totals."
[
uxui_grid_col_width = "20%, equal",
uxui_grid_excel = true
]
loop
{
Water "Water",
Juice "Juice",
TotalNonAlcoholicBeverages "TotalNonAlcoholicBeverages"
[
uxui_grid_excel_compute = "{ exp: 'SUM(C)' }",
ux4_attributes = "{ textAlign: 'center' }"
],
Beer "Beer",
Wine "Wine",
TotalAlcoholicBeverages "TotalAlcoholicBeverages"
[
uxui_grid_excel_compute = "{ exp: 'SUM(C)' }",
ux4_attributes = "{ textAlign: 'center' }"
],
OtherBeverages "Other Beverages",
DailyTotal "Total Beverages Per Day"
[
uxui_grid_excel_compute = "{ exp: 'SUM(C,*)' }",
ux4_attributes = "{ textAlign: 'center' }"
]
} fields -
(
Monday "Monday" long [0 .. 100] defaultanswer(0 );
Friday "Friday" long [0 .. 100] defaultanswer(0 );
WeekdayTotal "Weekday Total"
[
uxui_grid_excel_compute = "{ exp: 'SUM(R)' }",
ux4_attributes = "{ textAlign: 'center' }"
]
long [0 .. 100] defaultanswer(0 );
Saturday "Saturday" long [0 .. 100] defaultanswer(0 );
Sunday "Sunday" long [0 .. 100] defaultanswer(0 );
WeekendTotal "Weekend Total"
[
uxui_grid_excel_compute = "{ exp: 'SUM(R)' }",
ux4_attributes = "{ textAlign: 'center' }"
]
long [0 .. 100] defaultanswer(0 );
WeekTotal "Full Week Total"
[
uxui_grid_excel_compute = "{ exp: 'SUM(R,*)' }",
ux4_attributes = "{ textAlign: 'center' }"
]
long [0 .. 100] defaultanswer(0 );
) expand grid;
See