(v23) Display 'top N + other' measures using stored procedure

Display 'top N + other' measures using stored procedure

This applies to: Managed Dashboards, Managed Reports

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 an SQL data source.

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

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.

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.

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.

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

Preview the data from the process result.

Check in the data cube.

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.

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.