Ranking (Top N) in SAP BusinessObjects Design Studio 1.6
A common question here on SCN is how can I rank in Design Studio? The community has offered options, but now you can do it easily in Design Studio 1.6 at runtime.
Below I am using the new Ready-to-Run template
I’ve selected “Data Discovery and Visualization” ready to run template. Notice on the right of the above it describes the options with this template.
Notice it remembers my recently used queries
Above is the result
To select top 5 budgets, as an example, I select desired characteristic (dimension) and select Filter by Measure
So now I can select the top 5
I select the desired measure
I can also select Bottom N
In this example, I select top 5
The above shows the result – top 5 budgets
Hi Tammy, thanks for this blog. I have a question. Is there a way to use "Filter by Measure" by script?
Hi Patty - to my knowledge, it is not possible to do this by scripting at this time. Thank you for reading and commenting.
Hi,
in case you are on BW and have BEx conditions defined in the query, then there is a method to
* list all conditions -> method: getMeasureFilters()
* activate and deactivate -> method: setMeasureFilterActive()
of course, for that you have to either:
* have it defined in the query
* use Analysis Office and Smart Copy (this one is also for HANA based data sources)
* use old good Bex Web and define it, saving it as query view
Karol
Hi,
can we use 'Filter by Measure' in design mode, I want to show Top 10 Customer by default.when report is executed.
Thanks,
kashif Ali Khan
Only at runtime now, not design time
Hi,
as of today, it is not possible on designtime - this is correct.
one idea-workaround..
in 1.6, you can make following.
* in data source, sort by your Measure descending.
* take the scorecard component, bind it, define and adjust to have only 10 rows.
-> now you always will see the top10.
if you do not want to change pages in scorecard, you can use CSS and make the "Paginator" area invisible by CSS.
Karol
Hi,
i had observed that in crosstab if we enable pixel based scrolling and set row limit to 10 we can display only 10 rows from datasource ,by sorting the DS we can display top N or bottom N. now i wonder if we can take those 10 displayed rows out of crosstab and didsplay in chart..pls give your insights into this
Thanks Tammy
Hi,
If TOP N is static then below code will work.
var topn=DS1.getMembers("dimension",statictopn);
DS1.setFilters("dimension",topn);
now we can assign DS1 for chart or any other component.
Please make sure sort function apply in DS1
Hi Sreeni,
By definition a Top N ranking requires a reference to a measure, so I'm not sure how your suggested script would work?
Regards,
Mustafa.
Hi Sreeni,
I think the way you get members it will return in Alphabetical Order but not in order of the ranking. How does this approach work for you?
Yes that's right Trang Nguyen it is getting Alphabetical order.
use below code using SDK util package
Create DATAITERATOR and assign Data source DS1 & Maintain Sorting order in DS1 for measure.
var rows=DATAITERATOR_1.getRows(0,n);
var topn="";
rows.forEach(function(element, index) {
if(index==0)
{ topn=element.getDimensionValueKey("DIM"); }
else{ topn=topn+";"+element.getDimensionValueKey("DIM"); }
});
DS1.setFilterExt("DIM", topn);
Thanks,
Sreeni.
We are on Lumira 2.0 SP2. And I do not have the ready to run template that you have highlighted. Can you suggest if I need to import any extensions ?
Hi Tammy,
Thanks for the Blog. I am using Lumira Designer 2.1 SP0. Tried to find this Filter by Measure option for a cross tab at run-time. But it is not there. Can you please guide how to do this in Lumira Designer ?
Hi Anita - please select the dimension and then right click to bring up the context menu
Thanks for your quick reply Tammy. Tried it (Filter by Measure) still not in the list. Is there a setting which needs to be turned on to get that option ?
Hi did you select the column of the Dimension or the measure - you get different options depending on where you click in the crosstab
br
René