Display 'top N + other' measures using stored procedure

Contents[Hide]

1. Overview

This article shows an example of how to use a stored procedure in a data cube with a parameter for selecting the top 'N' records and grouping the rest as 'Others' for a SQL data source.

Note
Metric sets have settings that can perform the same task: see Displaying top/bottom records.

2. Set up the data

The data in this example is a SQL relational database, with the goal of showing the Top 'N' Sales using a Single Number filter on the dashboard to determine the number, with the rest of the values summed as a single Others item.

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 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, then click 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 to allow it be set from a dashboard
  • 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.

Choose Filters in the toolbar, then select a Single Number filter.

Connect the Single Number filter to the @topn parameter under the table.

Connect the Single Number filter to the @topn parameter
Connect 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.

View the dashboard
View the dashboard

5. See Also

Dundas Data Visualization, Inc.
400-15 Gervais Drive
Toronto, ON, Canada
M3C 1Y8

North America: 1.800.463.1492
International: 1.416.467.5100

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