How can I group specific entity members?

Adriana Mitillo

Hello,

 

I created a dataview with about 1000 rows ordered by quantity.

I need to see the first 3 rows with the related data and a 4° row with the sum of the 997 remaining members.

How can I do? 

 

thank you 

Helmut Heimann

Hi Adriana Mitillo,

 

put shortly, you can't.

Originally, BOARD offers no possiblity of a "rest" or "lumb-sum". You would need 2 Dataviews to achieve your aim, one with the top 4 and the second with those top 4 excluded.

You can vote for this idea: Add sum of remaining values in addition to total 

 

Kind regards,

Helmut

Previous Member

Hi,

 

As said by Helmut Heimann,  the "drag and drop" easy feature doesn't exist until now. It has been requested in an idea already. You can vote it up if you like. The link provided in the message above is to my mind a duplicate idea, so you can vote both up in doubt

 

To solve your question now,i see two approaches.

1. you cannot do it without additional data modelling effort, e.g. creating a dummy member (remaining items) of your entity, a dedicated cube for reporting the top 3 + remaining values, extract layouts by procedure created for this purpose, created datareaders to reload the extracted CSVs (top 3 on the one hand, total on all members on the other hand), then using ODBC datareader to substract the top 3 sum to the overall sum and load the result on the dummy member... this is not straightforward and takes time.
2. You could also solve this "out of BOARD, prior to BOARD" by only writing the appropriate SQL queries on the data source systems feeding BOARD, if applicable, and load in the cube the already "sorted out" top 3 customers. This SQL query work then.

Helmut Heimann

Hi Adriana Mitillo,

 

in addition to what interested typ explained--another way to achieve your aim (if those top 4 always remain 4) could be using NEXEL (you might want to look here for more information: Nexel ). But, this will mean quite some effort in implementing (and after all, it's just an idea that came to my mind).

 

Kind regards,

Helmut

Alexander Kappes

Hi Adriana Mitillo, Hi Helmut Heimann,

 

the idea with Nexel is working, I implemented in other Projects with success.

 

But I also used 2 DataViews, one for the top 3 and another one, which uses Nexel Gross View to subtract the TOP3 from the Total of all dimensions.

 

Hope it helps

 

regards

 

Alex

Previous Member

Hi Adriana Mitillo, Helmut Heimann Alexander Kappes interested typ

It is possible to create the report you desire with current Board functionality. Just needs a rule and some algorithm work.

I created a solution for a client in 2014 and wrote a technical note for this; I have implemented it several times successfully.

I have submitted the note + sample application to the 'Inspirations' section, with the title "How to create view with Top X values + 'Others' grouped in one value". 

Hopefully, it will be approved soon.

 

Robert

Previous Member

Hi Adriana Mitillo,

 

I'd make three cubes with refer-to for the first three lines. Then for the computation of the rest you need to add another cube without refer to. Hide that cube. Now add an algorithm that substract the latter with the former e.g. =d-c-b-a

The disadvantage is that now this is displayed by columns instead of by rows. You can change that with the 'align vertical' setting. See also How to create a report with several cubes grouped in lines? There might be some caveats though.

 

BR, Ray

Previous Member

I'd make three cubes with refer-to for the first three lines

This is a solution which works only when the top 3 members never change. In other cases (top 3 best products, best customers, whatever), the solution is not applicable.