Adding a special element to the metadata using a derived variable

When you add a special element to a table using an axis expression, the Table Object Model automatically takes care of creating any associated helper elements that are required by special elements you have created. These are elements that do not appear on the resulting table but are required during the calculation of the special element's value.

In some circumstances, however, you may prefer to add both the special elements and their helper elements to the metadata. You can do this by using a derived variable. This eliminates the need to use an axis expression, and can increase the speed of population of tables.

This example creates derived variables based on variables in the Short Drinks sample data set. It defines special elements calculated using factors and others calculated using numeric variables, and shows how to define helper elements.

The first derived variable, hhsize2, has some categories based on those in the hhsize categorical variable, with the addition of a factor attached to each category. The variable also includes a mean element. The mean is calculated using the factors atthhsize2 “Derived variable based on the Household size variable with categories and a mean based on factors”

hhsize2 "Derived variable based on the Household size variable with categories and a mean based on factors"
categorical [1]
{Hsize_1 "1 Person" expression ("hhsize * {HH_1}") factor(1),
Hsize_2 "2 People" expression ("hhsize * {HH_2}") factor(2),
Hsize_3 "3 People" expression ("hhsize * {HH_3}") factor(3),
Hsize_4 "4 People" expression ("hhsize * {HH_4}") factor(4),
Hsize_5 "5 People" expression ("hhsize * {HH_5}") factor(5),
Hsize_6 "6 People" expression ("hhsize * {HH_6}") factor(6),
Hsize_7 "7 or More People" expression ("hhsize * {HH_7}") factor(7),
NotAnswered "Not Answered" expression ("hhsize * {NA}"),
Mean "Average Household size" [CalculationType="Mean", HasNoData=True, ExcludedFromSummaries=True] elementtype(AnalysisMean)
};

For a list of the properties to use when adding special elements to the metadata using a derived variable, see the Special elements and helper elements in metadata. This topic also defines the helper elements required and their properties.

The second derived variable, Income2, is a categorical variable based on the Income numeric variable. It includes some bands based on the values in the original variable, and it also includes a mean element and the relevant helper elements (SumX and SumN) required to calculate the mean using the values from the numeric Income variable:

income2 "derived variable showing bands, mean, and standard deviation"
categorical [1]
{income_1 "Lower income" expression("income > 0 and income <= 30000"),
income_2 "Middle income" expression("income > 30000 and income <= 60000"),
income_3 "High income" expression("income > 60000"),
SumXSquared [CalculationType="SumXSquared", Hidden=True, ExcludedFromSummaries=True, DataElement=""] elementType(
AnalysisSummaryData) multiplier(use income),
SumX [CalculationType="SumX", Hidden=True, ExcludedFromSummaries=True, DataElement=""] elementType(AnalysisSummaryData)
multiplier(use income),
SumN [CalculationType="SumN", Hidden=True, ExcludedFromSummaries=True,DataElement=""] elementType(AnalysisSummaryData),
Mean "Average income" [CalculationType="Mean", HasNoData=True, ExcludedFromSummaries=True] elementtype(AnalysisMean),
Stddev "Standard deviation" [CalculationType="Stddev", HasNoData=True, ExcludedFromSummaries=True] elementtype(AnalysisStddev)
};

This example also includes a Standard deviation element. This requires an additional helper element, SumXSquared, but it does not require separate SumX or SumN elements, as these are shared with the mean element.

This example is available as a sample file called TablesDefineSpecialElements.dms. To run this sample, you need to have UNICOM Intelligence Reporter and access to a SQL Server installation and appropriate user access rights. Before running the sample, you must set up a SQL Server database called TablesDefineSpecialElements. For more information, see Creating a new SQL Server database. If you run this script more than once, you must delete the records in the database created by the previous run, otherwise the serial numbers will be duplicated and errors will result.

The examples in this topic are in the TablesDefineAxesExportToRDB.dms, TablesDefineAxes.dms, and TablesDefineSpecialElements.dms sample files that come with the UNICOM Intelligence Developer Documentation Library. For more information, see Table scripting sample Data Management scripts.