Passing parameters to SSRS Drill down reports

When passing data between reports there are two possibilities

• Passing Data from Output Data
• Pass the Query FilterData

Please refer to my earlier post for detailed explanation of steps.

 We will look at an example for Drill Down Report Example and Passing paramaters to it. Below Two Simple Procs I created in the Database

STEP 1

CREATE PROC TestProc1
(
@Param1 INT,
@Param2 INT
)
AS
BEGIN
SELECT 100 AS 'Result'
RETURN 1
END

CREATE PROC SUBProc2
(
@VALUE1 INT,
@VALUE2 INT,
@Value3 INT
)
AS
BEGIN
SELECT @VALUE1 as 'Outout1',@VALUE2 as 'Output2', @Value3 as 'Output3'
RETURN 1
END

STEP2
Now I am going to create Report from Proc TestProc1. In the SSRS Report Server Project I created a blank report, Created DataSet and Input Parameters. I would explain it step-by-step below

STEP3 - Next Step is creating dataset using proc TestProc1

 

 STEP 4 - For the input param create a Dataset and assign values as defined below

 

STEP 5  - Parameters and DataSet would be like below



STEP6 - Assign Created Dataset for Parameters as below



STEP 7 - Assigning Default value for Parameter param1



STEP 8 - Assigning input for Param2



STEP 9 -Now for Second Report using Proc SubProc2 Dataset Creation as below



STEP 10 - After providing dataset Input parameter would be listed as below when you specify Refresh Fields in Step 9



STEP 11  - DataSet creation for parameters for Report2



 For Second Parameter Data Set creation

STEP 12 - Assigning Parameters and Dataset

STEP 13 -Assigning Dataset for parameters



STEP 14 - This is the step where we assign Sub Report. Under Output->TextBox Properties->ReportName and Parameters

For Passing Filter Criteria we specify [@Param1], for passing current result we specify [Result]



Its pretty lengthy blog but last step is the important one. It worked finally.