Top N Query in Layout - N should be selectable by the user

Top N Query in Layout - N should be selectable by the user in the screen

One of our Clients wants to select variable top N queries in a screen, means that the user should be able to select if the layout is for example a top 10 or a top 15 or a top 20 query.

At the moment you have to go into the layout and select the number of the top N query there. This is not comfortable and for light users not even possible.

Has anyone had the requirement yet and has a solution for that?

Thank you very much!

Tanja

Find more posts tagged with

Accepted answers

All comments

Andrea Masiero

the only solution i see at the first glance is as follow.

You create a cube (not structured by the entity where you want to filter the top X) In this cube the user can enter the value of the top members he want to see. He'll enter 10 if want to see top 10, or 20 if want to see top 20 and so on....

The layout that need to be filtered on top something should be sorted by the kpi cube descending.

Next to this cube you use the ranking function "counter".

Next to the counter you use the cube where the user enter 10 or 20 or something. (let's call it toplimit) The value will be replicated for all the members user has by row in the layout.

You create a column algorithm like this if(ranking function <= toplimit;1;0)

You filter the layout by this column algorithm.

hope it's clear, it's simple as it is. #yourfantasyisthelimit

Dietmar Jeschkeit

I had the same answer. But in current version of Board there is a bug. The filter isn´t working because of the ranking function. I have tried it together with Alexander Kappes. He will inform the lab.

Previous Member

Hello Tanja,

did you think about to use the Nexel functionality?

You can create a cube "Ranking" with the dimension to rank. Then you can use the excel function "RANK" with the write inplace function into this cube, add it to the Library and use the Nexel Writeback step in a procedure.

Finally you can use a mono-dimensional cube (in my case "Top#") where the users can indicate how many top members they want to see.

Nexel:

Layout:

(Sort By Ranking and Filter by block D, x>1)

Result:

The procedure with the writback should be launched everytime you change the selection on the screen.

In my case for instance, if I change the selection on the company entity, then the ranking cube needs to be recalculated.

I hope it can help you!

Alessio

Dietmar Jeschkeit

Answer from lab:

the Filter on Ranking Functions is not supported.
The partner would need to find a different way to filter data.