Passing parameter value from dashboard to stored procedure

This applies to: Managed Dashboards, Managed Reports

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.

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

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

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

Set @StartProductID to public

Set @CheckDate to public

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 Analysis Panel.

Select data from the data cube

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

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

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

Add a numeric filter