First let me give you guys some information.

Source Table : DataTbl

CALTIME CALTIME2 TYPE MONEY

201712 201708 Month 2062.91666

201712 201708 Month 2062.91666

201712 201708 Month 2062.91666

201712 201708 Year 2062.91666

201712 201708 Year 2062.91666

201712 201708 Year 2062.91666

My dimensions: CALTIME,CALTIME2,TYPE

CALTIME :DataTbl.CALTIME

CALTIME2 :DataTbl.CALTIME2

TYPE : DataTbl.TYPE

My measure : MONEY (Only one)

1. Projection function : None

2. SQL : Cast(SUM(Case when DataTbl.TYPE = 'Month' then DataTbl.MONEY else Cast(DataTbl.MONEY as integer) end) as integer)

On my WebI report, I will have multiple textboxs, inside of those textbox will be a formula to extract data by CALTIME and CALTIME2 like below :

Formula for MONTH = [MONEY] WHERE ([TYPE] = "Month" And [CALTIME] = "CONDITION1" AND [CALTIME2] = "CONDITION2")

Formula for Year = [MONEY] WHERE ([TYPE] = "Year" And [CALTIME] = "CONDITION1" AND [CALTIME2] = "CONDITION2")

My purpose is, if the TYPE is "Month", we will perform the sum of all related values and remove all the decimal places. If the TYPE is "Year", we will remove all the decimal places of the value, then perform the sum of all decimal-removed-values. For example with the above data, and I want to calculate for CALTIME = 201712 and CALTIME2 = 201708, I will have the following results :

TYPE = Month = 2062.91666 + 2062.91666 + 2062.91666 = 6188.74998 = 6188

TYPE = Year = 2062 + 2062 + 2062 = 6186

But acttually what I'm getting at my WEBI report is :

TYPE = Month = 2062+ 2062 + 2062 = 6186

TYPE = Year = 2062 + 2062 + 2062 = 6186

My question is, is this possible to achieve what I want just by declaring measure's options in the Universe. I can't edit all the textboxes on the WEBI reports since there are hundreds of them.

## Add comment