Reporting Services Parameters

Question

How do I get this to work, because at the moment all parameters appear, I have no if I select this then select that and my multi values do not work either.

I want to be able to create a suite of parameters based on what the user selects (see below for example)

I currently have 5 Datasets - Main Dataset, Time Dataset, BDM List Dataset, Region List Dataset, Site List Dataset:

Select OccupancyDetail.CalendarYear
  ,OccupancyDetail.CalendarMonth
  ,Sum(OccupancyDetail.No_of_Nights) As No_of_Nights
  ,Sum(OccupancyDetail.Capacity) As Capacity
  ,Site.Region
  ,Site.BDM
  ,Site.SiteName
From   OccupancyDetail
   Inner Join Site On  OccupancyDetail.Site_Skey = Site.Site_Skey
Where  (OccupancyDetail.ReferenceDate = Convert(Date, Getdate()))
   And Case 
            When @Time = 'YEAR' Then Cast(CalendarYear As Varchar(4))
            Else CalendarMonth + ' ' + Cast(CalendarYear As Varchar(4))
       End In (@Dates)
   And BDM = (@BDM)
   And Region = (@Region)
   And SiteName = (@SiteName)
Group By
   OccupancyDetail.ReferenceDate,
   OccupancyDetail.CalendarYear,
   OccupancyDetail.CalendarMonth,
   Site.Region,
   Site.BDM,
   Site.SiteName

Example:

1st Parameter - ‘Reporting Level’
This is a drop down list to determine which part of the business needs to look at the report.
Company (Total results)
Region (Regional results broken down by Region)
BDM (BDM results broken down by BDM)
Site (Site results broken down by Site)

If ‘Company’ is selected then 3 parameter boxes appear, each with a dropdown option:

These parameters only appear once the 1st Parameter is selected.

2nd Parameter - ‘Time Grouping’

This will give you the choice of Year or Month

3rd Parameter - ‘Select Dates’

This is greyed out until the 2nd Parameter is completed.

If Year is selected in the ‘Time Grouping’ Parameter then the ‘Select Dates’ Parameter will give a multiple choice of which Years you want the report to produce.

For Example you can select 2011 on its own or 2011, 2012 and 2013 to view comparisons

If Month is selected in the ‘Time Grouping’ Parameter then the ‘Select Dates’ Parameter will give a multiple choice of which Months you want the report to produce.

For Example you can select Jan 2011 on its own or Jan 2011, Jan 2012 and Jan 2013 to view comparisons

4th Parameter ‘Report Type’

This will give a multiple choice of which type of report is required.

Hi Chepene I suppose that would be helpful, lol... I will update the original post. — wafw1971

Jamie F Jamie F · Accepted Answer · 2013-03-07T14:22:21

Sounds like Cascading parameters will do exactly what you need, except the later parameters will alway be visible, just disabled until the parameter they depend on is changed.

The key parts of Cascading parameters is that they can only depend on parameters before them in the parameter list of the report. (This can be reordered with the up and down arrows in the BIDS UI.) And they should get their list of available values from a Dataset that uses the earlier parameter. SSRS will know to rerun the dataset when the earlier parameter is changed.

Reporting Services Parameters

1 Answers