Skip to Content
0

Restricting key figures in BEx

Dec 25, 2017 at 11:54 AM

107

avatar image

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.

tax-1.jpg (26.7 kB)
tax-2.jpg (19.1 kB)
tax-3.jpg (20.8 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

4 Answers

Best Answer
Koen Hesters Dec 26, 2017 at 10:36 AM
1

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-ExceptionAggregation

if you need help, let us know

grtz

Koen

Share
10 |10000 characters needed characters left characters exceeded
Yiannis Kasolis Dec 28, 2017 at 01:20 PM
0

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

Share
10 |10000 characters needed characters left characters exceeded
Faheem Fa Dec 26, 2017 at 02:19 PM
0

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


tax-5.jpg (24.6 kB)
tax-4.jpg (19.1 kB)
tax-6.jpg (19.5 kB)
Show 4 Share
10 |10000 characters needed characters left characters exceeded

Hi, can you share your settings as well please

0

Hi Koen,

This is the settings I've maintained.

0

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

0

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

1
window rock Dec 27, 2017 at 05:22 PM
0

You need to set the exception aggregation on "Key" fields. For example if your infoprovider has Billing Document, and Billing Item, the exception should be placed on these two characteristics.

Share
10 |10000 characters needed characters left characters exceeded