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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.