BusinessObjects Board

Universes Merging - limiting data

building reports webintelligence
#1

Hi

I’ve got two Universes:

  1. Orders - I get from here Cell Name, Order Number, Quantity Ordered and Completion Date.
  2. Production Bookings - I can get from here Cell Name, Order Number , Quantity Completed, yet I can’t get Completion Date

I can merge those universes on Cell and Order Number.

Now, if I create report that contains:
Cell - Order Nr - Completion Date - Qty Ord - Qty Completed

  • it works fine, as granularity level is enough, for it to calculate correct Qty Completed.

However, I want also, something like Dashboard, where I have only Cell, Qty Ordered and Qty Completed.
I create query from Orders Universe, where I get orders in date range (user prompt for completion date). I don’t have a way to filter data from Bookings Universe in same manner, so I get all data from that universe. In Report, from Orders Universe I get:

Cell - Qty Ordered

from Bookings Universe I I want Qty Completed, so final version I want:

Cell - Qty Ordered - Qty Completed

Could someone help me create variable for Qty Completed, to sum Qty Completed from Bookings Universe, but only for Order Numbers that were retrieved in Orders Universe.

If I use sum only, it sums all Qty Completed, for all data retrieved from that Universe. I’ve tried some combinations of IN / FOREACH / FORALL, yet neither seems to work. How do I restrict SUM in one universe, based on data from another universe?

Thanks for Help

pit84 (BOB member since 2020-05-12)

#2

Try creating a detail variable with Cell or Order Number as the associated dimension. Then filter you table where that new variable is not null which ensures a match.