The field or axis reference item is a special type of field/axis item that you can reference in a derivation.
Understanding Reference Items
To understand reference items, you need to understand how derivations are constructed and calculated.
When you create a derivation on the column axis, SuperCROSS adds a new calculated column.
The values used in the calculation generally come from the other values in that row:
When you create a derivation on the row axis, SuperCROSS adds a new calculated row.
The values used in the calculation generally come from the other values in that column:
However, if we want to build the derivations using values from outside the respective row/column then we need to use reference items. A reference item is a pointer to other cells somewhere else in the table.
For example, if the Total row and column are set as the reference items:
The values used in the calculation can now come from both the cell row and the reference item row:
The values used in the calculation can now come from both the cell column and the reference column:
Select the Reference Item
You need to nominate which row/column will be the reference item before you create the derivation. If you do not specify a reference item, then the last item in a field or axis will be automatically set as the reference item by default.
To check or change the reference item, right-click the label for the row or column that you want to set as the reference item and make sure Field Reference Item is selected:
A field or axis can only have one reference item at a time. Selecting a new row/column to be the reference item will automatically clear that status from the previous reference item.
Once you have set the reference item, you can use it in your derivation:
In the Define Derivation dialog, click the Add Reference button:
SuperCROSS displays the Define Reference dialog. This dialog displays all the available cells in the reference item.
Choose which value you want to use in your derivation:
If you have a table in which there are nested classifications in the opposite axis to the derivation, you need to indicate which field, or combination of fields, in the nested axis to take the reference item from.
For example, this table has the Marital Status field in the column axis and the Gender and Occupation fields nested in the row axis. In both the Gender and Occupation fields, the Total row has been set as the field reference item (because of the field nesting, the Total row for the Occupation field appears three times, but this counts as a single field reference item).
If you create a derivation for the Marital Status field, and you choose to use a value from a field reference item, you need to choose whether to use a value from:
- The Gender reference item (in this case the Total section for Gender).
- The Occupation reference item (in this case the Total row for Occupation).
- Or the Gender+Occupation reference item, which is the intersection between the two reference items (in this case the Total row for Occupation in the Total section for Gender).
You make this decision in the Field Reference Setting dialog:
Creating an axis derivation is slightly simpler. While there can be multiple field reference items in an axis, the axis can only ever have one axis reference item.
In this example, if you create an axis derivation on the column, then you simply select the axis you want to use the reference item from:
For further details on how to use reference items, refer to these worked examples.