Display 'Top n + Other' measures using Stored Procedure

Contents[Hide]

1. Overview

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.

Sample dataset
Sample dataset

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.

Configure the stored procedure transform
Configure the stored procedure transform

Select the @topn parameter defined in the stored procedure.

  • Set the Parameter to Public
  • Set the default Value to 3

Define the settings for the transform parameter
Define the settings for the transform parameter

Preview the data from the process result.

Preview the data
Preview the data

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.

Bind the Single Number filter to @topn parameter of the table.

Bind the Single Number filter to the @topn parameter
Bind the Single Number filter to the @topn parameter

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.

View the dashboard in View mode
View the dashboard in View mode

5. See Also

Dundas Data Visualization, Inc.
500-250 Ferrand Drive
Toronto, ON, Canada
M3C 3G8

North America: 1.800.463.1492
International: 1.416.467.5100

Dundas Support Hours: 7am-6pm, ET, Mon-Fri