Hi everyone

I have 2 cubes, orders and discounts.

Orders are the typical header, detail variety. The header contains the
customer and the details contains the product line items for the order,
including a $ amount.

The orders cube lets me look at the OrderAmount measure by customer,
product and time.

For each order line item, it is possible to give multiple discounts.
For example, volume discount and partner discount could both be applied
to one line item, but only the partner discount may apply to a second
line item.

So the discounts cube is separate to the orders cube, and has all the
same dimensions, plus a Discount dimension, with members like "Volume",
"Partner" etc. The measure is DiscountAmount. It does not have
OrderAmount measure since it's a different grain.

So this is ok, and users can analyse orders and discounts in detail,
but separately.

What I would like to do now, is to show the discount amounts for each
type of discount, and also show it as a percentage of the total
OrderAmount for whatever selection of customer, product and time the
user has made.

For example:

Customer = XYZ Product = ABC Time= 2006
(total OrderAmount for this combination is 100K according to orders
cube)

Discount DiscountAmount %OrdersAmount
Volume $1,000 1%
Partner $20,000 20%


Whats the best way to get this output?

I have tried virtual cube, but when you drill into the discount
dimension it shows only empty cells for measures that are not based on
DiscountAmount.

I thought of rolling up each type of discount as a measure in the
orders cube, but it is hard to maintain (when new discount type comes
along) and makes for many measures.

Any help would be appreciated. I am on AS2000

Cheers,
Tim