User Defined Data Column (UDDC) in Cross-Tab

Defining a User Defined Data Column (UDDC) in a Cross-Tab

UDDC or Custom measure columns can be created by building a formula from existing dimensions and measures by performing various string, arithmetic, date, statistics, trigonometry, or conditional statements using various arithmetic operators (such as +, -, /, etc.) or comparison operators (such as =, >, < etc.) as per the cross-tab requirement.

Note: UDDC is created on the front-end data by users and not on the cube data (aggregated result set of a cube).

The custom measures created will be available in the Measures list in the Outliner. The custom measures 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 and Cost of Goods for different years. Since there is no Margin column in the cube, let's create it from the front-end using Custom measure functionality.

SALES CROSS-TAB

Step 1

In the Sales cross-tab, right-click on the measure cell. The system displays the menu.

Step 2

In the menu, select Add Column. The system displays Add custom measure (UDDC) dialog box.

ADD CUSTOM MEASURE (UDDC) DIALOG

Step 3

Enter the name as 'Margin' and create the expression in the Expression box as 'GrossSales-CostofGoods'.

Step 4

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

SALES CROSS-TAB WITH MARGIN COLUMN

The 'Margin' column can also be accessed from the Manage UDDC option available in the cross-tab settings toolbar (as displayed below). You can any time Edit the expression, Add new columns, Delete the columns, Make the column Private / Public and Active / Inactive from the UDDC template dialog box.

MANAGE CUSTOM MEASURE (UDDC) TEMPLATE

Example 2:

To show the variance with respect to the Target, the formula gets even more complex. Let's see how to create the Variance column for the Product categories with Target and Gross Sales.

Step 1

Follow the Procedure of adding custom measure.

Step 2

In the Add custom measure (UDDC) dialog, enter the name as 'Variance' and create the expression in the Expression box as 'ifCase( ((GrossSales+0)==0)&&((Target+0)!=0),0,ifCase(((GrossSales+0)!=0)&&((Target+0)==0),100,ifCase( ((GrossSales+0)==0)&&((Target+0)==0),0,((GrossSales*100)/Target))))'.

(The expression states that if the Gross sales is zero w.r.t the Target, then the actual performance is zero. If the Target is zero w.r.t the Gross sales, then the performance will be considered as 100 percentage. Otherwise, the formula will be (Grosssales / Target) * 100.)

Click OK to add the Variance column to the Sales cross-tab.

SALES CROSS-TAB AFTER ADDING VARIANCE COLUMN

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