User Defined Data Column (UDDC) in Cross-Tab

Modified on Wed, 6 Mar at 12:21 PM

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.

image002.png


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.

image004.png


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.

The Newly created UDDC – Margin – will be displayed in the Sales cross-tab as below.

image006.png


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.

image008.png


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.

image010.png


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. 

User Defined Data Column UDDC custom measure front-end data Outliner Expression Manage UDDC

Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article