BEx Query - Using a formula

[Former Member]

Hi,

I am having trouble designing BEx query to show the total expense amount for a shipment; the variable total expenses is the total of invoice amount but if the Invoice was not generated I need to use the 'Shipment Cost Net Value' in total expenses formula. Here is the BEx formula I used -

The raw data in the cube looks like this -

Ship Cost    Item     Shipment     Net Value      Invoice

SC1                   1          SH1            $200              $190

SC1                   2          SH1            $350              $0 (Not paid yet)

The query does not include the Shipment Cost Line Item in the rows; so the total expenses shows $190 since the total Invoice amount at the Shipment Level is not $0 anymore -

Shipment     Net Value      Invoice       Total Expenses

SH1            $550              $190           $190

The total expenses should be $540 since one of the Shipment Cost Line Item was not paid, so I have to continue using 'Shipment Net Value'. How I can resolve this?

[Former Member · Accepted solution]

Hello Grame,

You just need to use aggregation exception type sumation on Total Expenses, with reference characteristic Shipment Cost Line Item.

BR

Ondrej

[Former Member · Accepted solution]

Thank you. I tested it with a couple of scenarios and works great so far; will test it out with some others and update the post if I notice any exceptions.

[Loed]

Hi,

I think ITEM object should also be displayed so that your formula will work..

Regards,

Loed