Aggregation

Hello all,

I have an issue while running a report.

The query looks like below.

Customer Qty1 Qty2 open ord qty Value

The query is built on a Multiprovider.

Qty1, Value come from same cube, Open Ord qty is a cal key figure Qty1-Qty2

When Open Ord Qty is zero I should exclude that row.

Now when aggregation happens Qty comes correctly but the value is still sum of all rows. It is not excluding the rows where we had Open Ord qty = 0

I'm not able to use "before aggregation" because data is coming from two different providers.

So now I have created a calculated key figure to achieve this but I have to have the Doc number and item in the rows. Now I have these two guys as hidden.

Now the report shows in multiple rows...say for one customer I have 10 Docs and items , Now I get 10 rows in my report, bcos I have the Doc/item hidden . If I remove them the aggregation on "value" key figure will go wrong.

Can I use Display Hierarchy to solve this situation.

Customer Open Ordt qty value

4100162 1000 2500

100 2000

200 4000

In the above we have doc/item hidden.

if we remove it should actually give following

4100162 1300 8500

but I'm getting following

4100162 1300 10000

The difference 10000-8500 = 1500 is coming from closed orders, which we dont want.

can we solve this with display herarchies.

any suggestions appreciated.

Meyeps