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 ElegantJ BI. 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.
The Newly created UDDC – Margin – will be displayed in the Sales cross-tab as below.
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 ElegantJ BI Version 4.1. This may or may not be relevant to the ElegantJ BI version you may be using.