Passing parameter value from dashboard to stored procedure

Contents[Hide]

1. Overview

This article demonstrates how to pass parameter values from a dashboard to a stored procedure. In this sample, a numeric filter is used in the dashboard. If you prefer to use a hierarchy/member filter, you need to use a Bridge Parameter.

2. Prerequisites

Read the following articles:

3. Data

The Stored Procedure uspGetBillOfMaterials from Adventure Works for SQL Server 2012 database is used in this example.

CREATE PROCEDURE [dbo].[uspGetBillOfMaterials]
    @StartProductID [int],
    @CheckDate [datetime]
AS
BEGIN
...

4. Steps

Here are the detailed steps on how to pass parameter values from a dashboard to a stored procedure:

  1. Create a new Data Cube.
  2. Go to the Explore window and locate your SQL Server data connector. Expand its "Programmatic Structures" folder.
  3. Drag and Drop the [dbo].[uspGetBillOfMaterials] stored procedure to the canvas.
  4. Click on the Stored Procedure transform node and select Configure.
  5. Click Define Parameters.
  6. Click Edit Parameter icon to rename the parameter, change the default value, or set it to public.

  7. Set the parameters to public since we would like to expose them on the dashboard. This will allow end users to select the filter values in the dashboard and pass them to the stored procedure.

    Set @StartProductID to public
    Set @StartProductID to public

    Set @CheckDate to public
    Set @CheckDate to public

  8. Create a new dashboard, expand the Data Cube folder.
  9. Drag and drop the measure ListPrice to the dashboard canvas.
  10. Drag and drop the dimension ComponentDesc to ROWS in the Data Binding Panel.

  11. Add Date filter. This will pass date value to the @CheckDate parameter.
    • From the menu select Filter->Single Date.

    • From the parameter dialog, you can set/update the filter and script names, default values, and the columns and/or stored procedure parameters you want associated with this filter. For this sample, we can just keep the defaults (leave @CheckDate} checked.

  12. Add Numeric filter. This will pass numeric value to the @StartProductID parameter.
    • From the menu select Filter->Single Number.

    • From the parameter dialog, you can set/update the filter and script names, default values, and the columns and/or stored procedure parameters you want associated with this filter. For this sample, you need to check @StartProductID.

  13. Testing
    • View the dashboard. Nothing is retrieved by default.

    • Set date and numeric values to the filters:

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