Monday, December 2, 2013

How to have a parameter value automatically selected when another parameter value Selected in MS SSRS.

Step 1: Create cascade parameter in SSRS.
Step 2: One Parameter value is depend on another parameter value.
Step 3: The Depending parameter data set always return One Value.
Step 4:In Parameter Select-->Default Value Tab-->Get value from a Query-->Select Dependent data set--Select Value.

Example:
Dataset1:

select distinct
snapshot_WK
,cast(snapshot_Wkstart as date) as Snapshot_WKstart
,cast(snapshot_Wkend as date) as Snapshot_WKend
from sandbox..[Rawdata_Master](nolock)
order by snapshot_Wk


Dataset2:

select top 1
cast(snapshot_Wkstart as date) as Snapshot_WKstart
,cast(snapshot_Wkend as date) as Snapshot_WKend
from sandbox..[Rawdata_Master](nolock)
where snapshot_WK = @Reporting_Wk

Dataset2 is depend on Dataset1.Both data set are using for Parameter in Report. 

Result:When we select Snapshot_Wk in one parameter,other two parameter automatically filled with corresponding Wk_Start data and Wk_End Date.

1 comment: