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, but the steps are similar for most data types. If you prefer to use a hierarchy/member filter rather than a textbox to enter string values, you would need a bridge parameter.

2. 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
...

3. Set up data cube parameters

In a new data cube, go to the Explore window and locate your data connector. Expand its Programmatic Structures folder, then drag and drop the [dbo].[uspGetBillOfMaterials] stored procedure (in our example) to the canvas.

Select the Stored Procedure transform and choose Configure in the toolbar.

In the configuration dialog, click Define Parameters, and click the Edit icon next to each parameter that's being used.

Edit the parameter
Edit the parameter

Set the parameter to be public to be able to set its value from a dashboard.

Set @StartProductID to public
Set @StartProductID to public

Set @CheckDate to public
Set @CheckDate to public

4. Connect dashboard filters

Create or edit a dashboard, then find and expand the data cube we created in the Explore window.

In our example, we will drag and drop the measure ListPrice to the dashboard canvas, then drag and drop the dimension ComponentDesc to Rows in the Data Binding Panel.

Select data from the data cube
Select data from the data cube

Click Filter in the toolbar, then select Single Date. This will be used to pass a date value to the @CheckDate parameter.

Add a Single Date filter
Add a Single Date filter

In the popup that appears, you can select which measures, dimensions, or and/or stored procedure parameters you want associated with this filter. In our example, @CheckDate was checked automatically.

Connect the date filter
Connect the date filter

Click Filter in the toolbar, then Single Number. This will pass a numeric value to the @StartProductID parameter.

Add a numeric filter
Add a numeric filter

Connect the filter to parameters in the popup that appears. In our example, we select @StartProductID.

Connect the numeric filter
Connect the numeric filter

5. Testing the dashboard

Click View in the toolbar. Nothing is retrieved with the initial parameter values we have set.

Default parameter values
Default parameter values

Set the filters to values, and observe the updated data retrieved.

Set the parameters using filters
Set the parameters using filters

6. 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