Display 'Top n + Other' measures using Stored Procedure
This article shows how to display Top/Bottom N records and group the remaining records as 'Others', summed up as a single value for SQL data source. This can be done by creating a stored procedure on the data source and modifying the input parameter using a bridge parameter.
2. Set Up the Data
The data should belong to a SQL relational database. The goal in the sample below is to show the Top N Sales using a Single Number filter on the dashbaord and the sum of the rest of the Sales as Others.
2.1. Create Stored Procedure
Create a new stored procedure on the relational SQL data source with a parameter which receives the number of top/bottom records we want to display. A stored procedure for the sample data above can be created like below:
create procedure [dbo].[sp_TopNPlusOther] @topn int as begin select [SalesRep], [SalesActual] from ( select top (@topn) [SalesRep], sum([SalesActual]) as [SalesActual] from [dbo].[myTable] group by [SalesRep] order by [SalesActual] desc ) as alias union all select '(OTHER)', sum([SalesActual]) from [dbo].[myTable] where [SalesRep] not in ( select top (@topn) [SalesRep] from [dbo].[myTable] group by [SalesRep] order by SUM([SalesActual]) desc ) end
Run the query to save this Stored Procedure.
3. Create a Data cube
After creating a new data connector to the database with the above created stored procedure, create a new data cube.
Drag and drop the stored procedure sp_TopNPlusOther onto the data cube canvas.
Double click the stored procedure transform to configure thr parameter(s).
Click on Define Parameters.
Select the @topn parameter defined in the stored procedure.
- Set the Parameter to Public
- Set the default Value to 3
Preview the data from the process result.
Check in the data cube.
4. Using the Data cube on the Dashboard
On an existing or new dashboard, drag and drop the above created Data Cube.
From the Filters in the toolbar, select a Single Number filter.
Connect the Single Number filter to @topn parameter of the table.
By default, the table will show top 3 Sales and the sum of the rest of the Sales. Switch to View mode and change the value in the Single Number filter to change the number of top Sales that are visible to the user.