Passing parameter value from dashboard to stored procedure
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.
Set the parameter to be public to be able to set its value from a dashboard.
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 Analysis Panel.
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.
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.
Click Filter in the toolbar, click More, and then click Single Number. This will pass a numeric value to the @StartProductID parameter.
Connect the filter to parameters in the popup that appears. In our example, we select @StartProductID.
5. Testing the dashboard
Click View in the toolbar. Nothing is retrieved with the initial parameter values we have set.
Set the filters to values, and observe the updated data retrieved.
