User Defined Header Column (UDHC) in Cross-Tab

Defining a User Defined Header Column (UDHC) in a Cross-Tab

Custom dimension value columns or rows can be created by defining and applying mathematical formulae on existing row and column values as per the cross-tab or reporting requirements. This is also known as User Defined Header Columns (UDHC) or Custom Dimensions.

Users can create new dimension value columns by performing various conditional statements, such as string, arithmetic, date, trigonometry, or using various arithmetic operators (such as +, -, /, etc.) or comparison operators (such as ==, >, < etc.) on two or more existing dimension columns or rows.

Note: UDHC is created on front-end data by users and not on the aggregated result set of a cube. It can be used in cross-tab and graphs.

The custom dimensions used in cross-tab are also available as templates in Smarten. This can be reused by various users while creating different cross-tab.

Example 1:

The below cross-tab shows Product Category wise Gross sales for different years. To display row dimension values for Total value of 'Edibles' categories, 'Non Alcoholic Drinks' and the 'Difference' as difference of Non-Alcoholic Drinks from Alcoholic Drinks, follow the below ste

SALES CROSS-TAB

Step 1

In the cross-tab, right-click on a specific cell in the row dimension. The system displays the menu.

Step 2

In the menu, select Add Row. The system displays Add custom dimension value (UDHC) dialog box.

Step 3

Calculation Priority over Custom Measure option: Users can choose the calculation priority among UDDC and UDHC while creating UDHC. ie; This option is used to prioritize the value to be displayed at the intersection cell as per the formula of UDDC or UDHC. 

ADD CUSTOM DIMENSION (UDHC) DIALOG

Step 4

Enter the name as 'Edibles' and create the expression in the Expression box as 'ProductCategory_Bakery+ProductCategory_Confectionary+ProductCategory_Ice_Cream+ProductCategory_Snacks '.

A particular dimension value should be mentioned with prefix Dimensionname_Dimensionvalue. For example, to mention dimension value Ice Cream for Product Category dimension the expression will be as ProductCategory_Ice_Cream. 

Step 5

The VERIFY EXPRESSION will verify the expression and display a message if the expression is valid or not. Click OK.

Similarly create the dimension value 'Non-Alcoholic Drinks' using the expression as ' ProductCategory_Cool_Drinks+ProductCategory_Fruit_Juices+ProductCategory_Health_Drinks+ProductCategory_Tea ' and 'Difference' using the expression as 'ProductCategory_Alcoholic_Drinks -(ProductCategory_Cool_Drinks+ProductCategory_Fruit_Juices+ProductCategory_Health_Drinks+ProductCategory_Tea)'.

The Newly created UDHCs – Edibles, Non-Alcoholic Drinks and Difference – will be displayed in the Sales cross-tab as below.

The 'Edibles', 'Non-Alcoholic Drinks' and 'Difference' values can also be accessed from the Manage UDHC option available in the cross-tab settings toolbar (as displayed below). You can any time Edit the expression, Add new dimension values, Delete the dimension values, Make the dimension value Private / Public and Active / Inactive from the UDHC template dialog box.

MANAGE CUSTOM DIMENSION VALUE (UDHC) TEMPLATE

Example 2:

Let's see how to create column dimension values in the below sales cross-tab. The below sales cross-tab shows the product category wise Gross Sales and state wise Sales contribution % (column group percentage values) of various states. To display the value of the 'South East' region (Arkansas and Florida) together, follow the below steps.

Step 1

Follow the Procedure of adding custom dimension value.

With Calculation Priority over Custom Measure option:

Step 2

Check the Calculation Priority over Custom Measure checkbox to give the intersection value priority as per the UDHC formula.

Step 3

In the Add custom dimension value (UDHC) dialog, enter the name as 'South East' and create the expression in the Expression box as 'State_Arkansas+State_Florida'.

Click OK to add the 'South East' column value to the Sales cross-tab as below.

The value '56.81' is calculated as per the UDHC formula ' State_Arkansas+State_Florida'.

UDHC VALUES WITH CALCULATION PRIORITY OVER CUSTOM MEASURE OPTION 

Without Calculation Priority over Custom Measure option:

Step 1:

Follow the Procedure of adding custom dimension value 'South East' having the option  Calculation Priority over Custom Measure unchecked.

The sales cross-tab will be displayed as below.

The value '50.71' is calculated as per the UDDC formula 'column group percentage (measure)'.

UDHC VALUES WITHOUT CALCULATION PRIORITY OVER CUSTOM MEASURE OPTION

Note: This article is based on Smarten Version 5.x. This may or may not be relevant to the Smarten version you may be using.