Skip to Content

sum calucalation not coming correctly in webi

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jul 05, 2017 at 02:25 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jul 10, 2017 at 08:56 AM

    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..

    Add comment
    10|10000 characters needed characters exceeded