Top N / Bottom N and the rest as additional field

[Former Member]

Hi all,

we have the following requirement which I am not able to fullfil:

Designing a query which shows not only a Top N  or Bottom N selection but also the rest in an additional line at the end.

This query should be the basis for a design studio application.

This is what we want:

TOP 3 Customer

Customer	Sales	Percentage
Smith	45000	30%
Jones	30000	20%
Brown	15000	10%
REST	60000	40%
TOTAL	150000	100%

This is what we get:

TOP 3 Customer

Customer	Sales	Percentage
Smith	45000	30%
Jones	30000	20%
Brown	15000	10%
TOTAL	150000	100%

We created a query with two variables: one for top n and another for bottom n.

These variables have to be filled when executing the query.

So the user can select a free quantity of top or bottom n.

(We do not know the total quantity of all members)

Within the table the user may calculate the percentage of the rest by himself.

For this reason we added the "Percentage" column to show the relation of the top n to the total.

But we want to see the distribution also within a pie chart.

And there it is totally senseless to display only the top three and their distribution without a rest.

It is mandatory to have a rest when using a pie chart.

Is there no way how we can combine some filter or whatever in query designer to fulfil this requirement?

I googled for a while and found nothing, but I cannot image that with SAP BW it is not possible to create a Top N list with an additional item "rest".

In my opinion this is common need.

There is a need for a new variable in BEx Query Designer "TOP N + REST" or something like that...

Hopefully someone has an idea or hint how to solve this problem.

Regards

Steffen Bruckner

[cornelia_lezoch · Accepted solution]

Hi Steffen,

there is no easy and perfect solution for this.

If N is a fixed number, you could work with a structure - having structure elements for the N selected values (each must be selected by an exit variable) and then have one structure element for the rest and one for the sum ( I would calculate rest as substraction Sum - N-objects).

I N is flexible then I can see only the solution that you have already. Depending on your frontend it might be possible to combine results from several queries into one table.

regards

Cornelia

[Former Member · Accepted solution]

Hi Cornelia,

unfortunately N is flexible so it is not possible to calculate the rest by a substraction as we so not have always the same n elements.

So actually it seems that there is no solution for this....as I was afraid of...

Thanks

Steffen

[umashankar_poojar · Accepted solution]

Hi Steffen,

Could you please check on Virtual Characteristics? Here output can be modified once you have result in BEx, anyway you have desired result by using TOP N variable, now you just need to get the remaining output as REST (which is sum of sales excluding the shown in report).

Virtual Characteristics using Customer Exit OR BADI, you can implement your own logic and get the output.

Currently I don't have ready made solution for suggestion, but you can implement using Virtual Characteristics.

Hope it helps!

Thanks,

Umashankar

[Former Member]

Hi Steffen you can go through this link

to visualise Top / Bottom N and Others

http://scn.sap.com/community/businessobjects-design-studio/blog/2015/08/14/how-to-visualize-others-i...

If you wish to show only top N and others there is an easy option in DS 1.5 in advanced properties of the pie chart there is an option "Data Transform". Click Enable and set the "Bin Number" which is representing the Top N value (including others). The others are grouped together in the chart.

Thanks

[Former Member]

Hi Karthik,

thanks for the answer.

Unfortunately this is not enough as we wanted to Show not only a predefined number (e.g. Top5), we wanted to use a variable, that the user may select from top1 top20...

It seems that we have to decide wether we have the flexibility or we have the rest.

The Option in the pie Chart in Design Studio is a good hint. In my first try I used this but I thought that there must be a smarter and better way to do this.

One possible solution may be an acces to the there selected value so we may Change this with a script in design Studio but as far as I know t is not possible to do this.

Regards

Steffen