Create a Multiple Level Field

A multiple level field is based on the relationship between tables. This type of UDF is only available if there are multiple fact tables in your database.

There are three options:

Type

Description

Count

Counts records in a table that are associated with a specific field in another table. For example, the number of accounts per customer.

You can set a ceiling for the count to limit the results (e.g. for the above example of the number of accounts per customer, you might set a ceiling of 4 to see the values for customers who have 0 accounts, 1 account, 2 accounts, 3 accounts and 4 or more accounts).

Copy

Copies a field from one table to an associated record in another table. For example, using the Retail Banking database you could create a UDF that copies the customer age from the Customers table and appends it to the account records in the Accounts table.

With this type of UDF:

You can specify a value to use if there is no match found in the other table (for example if there is no corresponding customer age for an account). For example, you could default to the string "No Matches".
You can specify a value to use if multiple matches are found in the other table. For example, you could default to the string "Multiple Matches".

Sum

Sums a measure in one table that is associated with a specific field in another table. For example, using the Retail Banking database you could create a UDF that creates a sum of the Account Profit from the Accounts table and appends this to the Customers table.

To create a multiple level field:

Select Cross > Define Field. The Define Fields window displays.
Click Multiple. The Multiple Level Field window displays.

Enter the following details:

Field	Description
Field Name	Enter a name for the field.
Create Field For	Select the database table to create the field for.
From Associated	Select the associated table to use to create the field.
Count, Copy, or Sum	Select whether to create a count, copy, or sum type of field.
Ceiling	(Only applicable if you select Count field) Select a maximum value for the count. Any counts above the maximum value will be grouped together. For example if you set this to 4 then the values for the field will be the counts of 0,1,2,3, and 4+
Field to Copy	(Only applicable if you are creating a Copy field) Select the field to copy.
Value if no matches found	(Only applicable if you are creating a Copy field) Enter the value to use if there are no matches found to copy.
Value if many matches are found	(Only applicable if you are creating a Copy field) Either enter a value to use if there are multiple matches in the other table, or select the Use first match for many matches check box. If the field you are copying is a date field, then you can also choose to use either the earliest or latest date found for this field in the source table.
Field to Sum	(Only applicable if you are creating a Sum field) Select the field to sum.
Define Condition	Create the condition to use to select the values to count, copy, or sum. In the Based On drop-down list, select the field. Use the radio buttons to define the condition: Select the Minimum radio button to select only the minimum value of this field. Select the Maximum radio button to select only the maximum value of this field. Select the In radio button to specify that the value must be one of a list of possible values (and then select the values from the list). Click And or Or to add the condition to the list of Conditions.

Click OK.