We know now we have Top N and Bottom N feature in Run time in the context menu of the Crosstab as Filter by Measure. Many people ask whether this Top N or Bottom N is possible by default or in Design time or in script. The best way recommended and many will agree is to have conditions in the backend to handle the Top/Bottom N requirement.

But, just in case if you still need to do it in Design Studio, here is one way to implement Top N or Bottom N using BIAL script for any combination of a dimension and a measure. Using this method, it is also possible to display the total of the selected measure for the top N members of the selected dimension. For simplicity the example here demonstrates Top 5 and Bottom 5 for a dimension and measure combination.

After we create a data source in an application we have the option of creating simple calculations in the data source. We need to add a dynamic calculation on the desired measure and select Rank Number. The dynamic calculation feature was added in the Design Studio 1.6 release. You can add the Rank Number for the measures in the source query (backend) itself and then create a data source on this query.

Create Rank in Initial view of the data source

Now in the On Startup event we can write the following script to open the report to show only the Top 5 products by sales on initial load by default. Click on the image to view the script.

Top 5 products – initial load

This script makes use of the rank measure that we just created. It extracts the rank value for each product and compares it with n, which is equal to 5 in our case. Any member with rank lesser than or equal to 5 will be selected for filtering the data source.

Or if we wanted to show the Bottom 5 products by sales then we can write the following script (click on the image to view the script) :

Bottom 5 products – initial load

This is similar to what we did for the Top 5 with only a minor difference. Here we will not be comparing the rank with n; instead we will compare it with n subtracted from the total number of members of that dimension. For example if total members = 50 then we compare rank with 45 and any member with rank greater than 45 will be selected for filtering.

Based on the script above we can enable Top N or Bottom N members of any dimension by a selected measure. We may need a few other components to make Top/Bottom selection, dimension selection, measure selection, and most importantly the value for N.

One problem with this approach is limitations with the ability to pass dynamic JSON pairs. Since this is based on JSON, we may have to write a script for every combination of dimension and measure that we provide in the respective selectors. Also we will have to add rank, in the initial view, for each measure that we provide in the selector.

Below is an example to demonstrate the logic for the scenario we just described. Here we have a script for Top N/Bottom N for 2 different dimensions (Product and Country) by sales. If we provide a selector for measures then we will just need another global variable that holds the technical name of the corresponding rank measure. We can then replace the rank measure in the getData function by the new global variable. Click on the image to view the script.

Top N/Bottom N scenario for 2 different dimensions

Here are a few screen shots of the application in runtime. The chart always shows the Top N/Bottom N items and for reference the table below shows all the data with a rank for each member.

Top 5 Products by Sales – initial load

The screen shot below shows the result for Top 5 countries by sales, after making changes in the selectors above and applying it to the data source using the script.

Top 5 Countries by Sales

The screen shot below shows the result for Bottom 5 Countries by Sales.

Bottom 5 Countries by Sales

The screen shot below shows the result for Top 8 Countries by Sales.

Top 8 Countries by Sales

If you are using BW as backend you may take a look at the this link to get better performance results: https://visualbi.com/blogs/design-studio/design-studio-tips-and-tricks/switch-bex-conditions-on-demand-from-sap-businessobjects-design-studio/

 

Subscribe to our Newsletter

5920 Windhaven Pkwy
Plano TX 75093.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com