BusinessObjects Board

Data Aggregation issue in WEBI Report

building reports webintelligence
#1

Hello Folks,

I have a following issue in my WEBI Report. For Example, Giftcard 1234 has actual card value as $100, its redeemed value $30, then I need to show balance due amount as $70. While getting data from backend, I am getting this information in two different transactions with different Card categories.

Gift Card nbr Card Catg Gift card act value Gift card redeem value Balance Due
Transaction 1 1234 1 (Actual) 100 0 100
Transaction 2 1234 2 (Redeemed) 0 30 30
Expected Result 1234 100 50 50

Unfortunately, I do not have control on the backend data or on the semantic layer (Universe level). I have tried creating Variable with If statement using Card Catg, Gift card actual value and redeemed value but that didnt help.
Can you please help me out if there’s easy way to resolve the issue.

Regards,
Kris

#2

Put a sum() around the last 3 columns and drop the Card Carg Column

#3
  • why is “Expected result” = “50” if you are talking about “70” as result ?!
  • make all “card catg = 2” negative values within a variable
  • sum the variable-values as “balance due”
  • delete column “card catg”
#4

Let’s try to make a webi document with the following csv input:

transaction_id;card_number;card_cat;value
1;1234;1;100
2;1234;2;30

Setup a variable named balance using context Where() that restrict the data used to calculate a measure:

 =Sum([value] Where ([card_cat]=1)) - Sum([value] Where ([card_cat]=2))

Use it with card_number inside a block:

image

:rocket:

#5

You can also put a break on card number and put column sums in your break header or footer and turn on folding to have your card totals plus have the ability to “unfold” to see the transaction details.

#6

Big Kudos to you all for sharing different ideas, I was able to resolve the issue by doing the following -

  1. I have removed Card Catg from the report.
  2. Created new Gift card act and Redeemed variables and used business logic without considering the Card Catg.

Regards, Kris

#7

I see you’ve found a solution, but another idea in case you end up redesigning it is to get your measures into the same column using an if statement:

If(not(isnull([Gift card act value]))) then [gift card act value] elseif(not(isnull([Gift card redeem value]))) then (-[Gift card redeem value])

Then you can add a break to the table by gift card number and add totals and you’ll get the expected result of 70.

Happy calculating!

#8

Thank you Tyler for your Idea, much appreciated!

Regards, Kris