Q: Except agg counter using replacement path variable

[Former Member]

Hi,

I have a problem in a query. The query is using a formula variable (replacement path) to count "number of orders" according to the how-to-guide available on SDN: "How to Get the Document Count in a Query Using Formula Variable with Processing Type Replacement Path".

This works fine in many cases, but if a document is deleted, this will still be counted and the result will therefore be incorrect. This is my problem and I cannot compress the InfoCube after each load (which would solve the problem), as I am also using the BWA which then requires a full delete/fill after each load to be aligned with the compressed InfoCube.

I therefore need some other solution for the counter and I have tried using query conditions to supress the empty lines, but this only works for certain drill-downs (and of course I want the counter to be correct always, regardless of drilldown).

Does anybody have any suggestion on how to solve this?

Thank you in advance,

Mikael

[Former Member · Accepted solution]

Hi,

Im not 100% sure if this will work but you can try to create -

CKF = 0amount (amount you have in a document) > 0 and set it's exception aggregation of 0amount of type Total referenced to 0doc_num.

Hopefully in this case if document is deleted the total amount on this document will be 0 as (for ex it was 5$ prev and delta brings -5$).

First this will aggregate according to document number which gives 0 and then - calculates the formula CKF = 0.

[Former Member · Accepted solution]

Hi,

That is similar to what I have tried with my conditions (I have also tried with formulas as you are suggesting), but I have not been sucessful. Yes, all key figures are zero for the order, but if I do not drill down on order then the qty is not zero anymore and my condition/formula stops working.

The method you suggest could probably solve the "number of orders" problem, but I also have a similar problem with "number of materials" and this is more tricky as we also have the same material for many orders...so my qty can be zero on one order item, but not zero on another so this would require some kind of exception aggregation on both order and item.

Best regards,

Mikael

[Former Member · Accepted solution]

Hi, this is tricky ......

I can only suggest to create a ZCHAR which is compounded (Order + material) populate it in cube to use it as reference char in exception aggregation as ther's no such option (in exception agggr to use 2 or more chars as references)/

The rest of algorithm is the same.