Restricting key figures in BEx

[faisalfa]

Hi,

I've a reporting requirement.

The user wants to display single line item for each invoice document.

tax-1.jpg

So, I removed the Bill document number, but the problem I'm facing is the tax amount is getting summed which shouldn't happen as per requirement.

tax-3.jpg

I want to avoid summing the tax amount and display it as a single amount.The tax amount value is singular for invoice.

Can this be achieved in BEx?

This is the output I want to display

tax-2.jpg

Kindly, please let me know.

[kohesco · Accepted solution]

Hi,

please set exception aggregation on your KF, you can set it in your query designer or in BW (this makes sense if it generally the way the use this key figure)

check out blog:

https://wiki.scn.sap.com/wiki/display/BI/OT-OLAP+Exception+Aggregation#OT-OLAPExceptionAggregation-E...

if you need help, let us know

grtz

Koen

[former_member223480]

Hi there, you can do it as much complicated as you want (the system has no limits).

Create a new formula and count on billing doc then divide the tax amount with the counter.

Yiannis

[faisalfa]

Hi Koen,

Thank you for your inputs!

I’ve applied exception aggregation to the Tax amount at BEx level. There is a small issue here through for certain line items where the bill document number is same, the tax amount is getting summed.

Source:

BEx Query:

tax-4.jpg

I tried adding Line item as a ref. char in aggregation tab but, the tax amount isn’t showing correct.

Expected Result:

tax-6.jpg

[kohesco]

Hi, can you share your settings as well please

[faisalfa]

Hi Koen,

This is the settings I've maintained.

[kohesco]

Hi,

I checked it on BW 7.4 SP5, here you can do it in one step because you are able to define more than 1 exception agg char. But I tried to do it without that feature and I managed to have the acquired result via 2 formule variables.

• first one has TAX amount with MAX exc agg on Line item
• second one has the first formula with MAX exc agg on billing document

I set the result lines on max also so it will not sum the result lines if you set them to show.

I guess you can set the first one in BW and the second one as calculated KF, but please try first the formulas out

grtz

Koen

[faisalfa]

Hi Koen,

Greetings!
Our current system is BW 7.3. We are in the process of upgrading our system to BW 7.5.

Once the system gets upgraded I am sure I'll be able to define more than 1 exception agg char.
I'll keep you posted on this as & when I resolve this issue.

Thanks,

Faheem