Skip to Content
0

sum calucalation not coming correctly in webi

Jul 05, 2017 at 12:22 PM

68

avatar image

We are on
SAP BusinessObjects BI Platform 4.1 Support Pack 5 Patch 7
Version: 14.1.5.1726

I have query from database which is as below
select legacy_code,..,
SUM(CASE WHEN amt>=0 THEN amt ELSE 0 END) as DEBIT_AMT,
SUM(CASE WHEN amt<0 THEN amt ELSE 0 END) as CREDIT_AMT,
(SUM(CASE WHEN amt<0 THEN amt ELSE 0 END)+SUM(CASE WHEN amt>=0 THEN amt ELSE 0 END)) as DBT_CRT_DIFF
...

I've pullied all the required objects from universe to my report but unable to build above logic in report
using below 2 vairables created in Webi report

(1) CR_AMT =0+Sum(If([Gross Amount]<0;[Gross Amount];0)) Where ([Debit Credit]="C")
(2) CR_AMT =0+Sum([Gross Amount]) Where ([Debit Credit]="C")

But on both the occassions result set does not match for few legacy code dimension which are selected along with the measure.

Is there a way to achive this in BO Webi Report ?

Any pointers would be appreciated

bo-output.png (24.5 kB)
database.png (62.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Jyothirmayee A Jul 05, 2017 at 02:25 PM
0

Hi,

What happens when you just pull in DEBIT_AMT, CREDIT_AMT and DBT_CRT_DIFF directly from universe without any logic within WebI report?.

Also, try:

CR_AMT = Sum(If([Gross Amount]<0;[Gross Amount];0)) Where ([Debit Credit]="C") with out 0 (I'm not sure why you are trying to add ZERO in front of summation)

Thanks,

Jothi

Share
10 |10000 characters needed characters left characters exceeded
Sachin Joshi Jul 10, 2017 at 08:56 AM
0

Hi,

In order to have show , some value instead of showing null "0+" has been used.

The universe does not have any calculative measures which is Difference bet Credit and Debit values present in database fields.

The suggestion above does not work as there is minor difference for highlighted legacy codes as above.

Any pointers appreciated..

Share
10 |10000 characters needed characters left characters exceeded