CODEWITHSUNDEEP
business-objects
wo0lien
wo0lien

Reputation: 1

How to prevent duplication in sum when rows has the same id in SAP BO designer indicator?

I would like to prevent counting duplicates in a sum indicator in SAP BO universe when two items has the same ID.

I have two tables in DB

OrderId Price
1 10
2 15
SubOrderId OrderId
1 1
2 1
3 2

Here is a sample of report :

OrderId SubOrderId Price
1 1 10
1 2 10
2 3 15

Since the price is related to the command I would like the sum to be 25 instead of 35

I want to achieve this at the universe level so that the users does not have to worry about a possible wrong sum of prices.

Doing Sum(Price) gives 35.

The solution I got from now is to have two indicators, one of them being restricted to use in the command only context. Then duplication is not possible but the indicator is not usable in complex queries.

The other one is unsafe but can be used with the subcommand related objects.

At the end comparing the two sums (by doing two discting queries) indicate whether or not the unsafe sum is right or not.

Thank you all for your help

Upvotes: 0

Views: 131

Answers (1)

Lajos Arpad
Lajos Arpad

Reputation: 76601

In SQL in general you can do something like this:

select sum(Price)
from (
    select OrderId, max(Price)
    from yourtable
    group by OrderId
) t

Basically, you do a subselect where you group by OrderId, get the Price for each group, so you will have exactly as many lines as the number of OrderId, each having the Price of the given order. And, at the end, you sum(Price) from this subquery.

Upvotes: 0

Related Questions