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:
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).
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:
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:
Enter a name for the field.
Create Field For
Select the database table to create the field for.
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.
(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.
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.