Skip to Content
0

Perform sum of values then getting rid of decimals Sap WEBI report

Jan 18 at 07:30 AM

65

avatar image
Former Member

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.

10 |10000 characters needed characters left characters exceeded

are you getting values like 2062.91666 or 2062 without decimal places in the report?

1
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Best Answer
avatar image
Former Member Jan 19 at 08:20 AM
0

Hi Guys,

I realized that I was misunderstanding how WEBI and BO work. I do now.

Thanks alot for reading.

Share
10 |10000 characters needed characters left characters exceeded