SSRS parameters based on selection?

Question

I hope someone could help with this?

I am building a report based on a stored procedure that has the following clause in it

where accountingperiod = @Accountingperiod or createddate between @Startdate and @Enddate

@Accountingperiod will be in the following Format 'Oct 2017' @Start and @Enddates are just generic date formats.

I basically want my report to load and have the option to select whether the user wants to filter the report using the @Accountingperiod parameter or the date parameter (as both bring back entirely different data). Once the user has selected which one is to be used, the parameters (for example, @accountingperiod) will be available for selection.

How can this be achieved? Apologies if this is a silly question, I am quite new to SSRS!

Thank you

Alan Schofield Alan Schofield · Accepted Answer · 2018-01-03T15:28:00

There are several ways to do this. The simplest would be to do it in the Stored Procedure and just test if Accounting Period has been set, if so use that, if not use the date range but that might not be intuitive for the user as they could select an accounting period AND a date range without realising that the date range would be ignored.

The most elegant solution would be to set the start and end dates based on the accounting period and have another entry in your accounting period list called 'Date Range' or 'Any' etc.

This assumes you can lookup or calculate the start and end dates for each accounting period. For the sake of simpicity in this example we'll assume that you have a table (say myAccPeriodTable) with the periods and date ranges in, something like this..

AccPeriodID AccPeriod StartDate  EndDate
      1     2017-Q1   2017-01-01 2017-03-31
      2     2017=Q2   2017-04-01 2017-06-30
      3     2017=Q3   2017-07-01 2017-09-30
etc...

Create a dataset (lets call it dsAccPeriods) with the following query

SELECT AccPeriodID, AccPeriod FROM myAccPeriodTable 
UNION SELECT -1, 'Use Date range'
ORDER BY StartDate

You Accounting period Parameter (assume this is called @AccPeriodID) Available Values would be from a query and point to dsAccPeriods

The Value would be the AccPeriodID field and the Label field would be AccPeriod

Now create two more datasets say, dsDefaultStartDate and dsDefaultEndDate with the following queries respectively.

SELECT ISNULL(StartDate, 1999-01-01) AS StartDate From myAccPeriodTable WHERE AccPeriodID = @AccPeriodID

and

SELECT ISNULL(EndDate, 2020-12-31) AS EndDate From myAccPeriodTable WHERE AccPeriodID = @AccPeriodID

For your 'StartDate' parameter (assume its called @StartDate) the Default Value would simply point to the dsDefaultStartDate dataset and do the same with @EndDate pointing that to dsDefaultEndDate.

Now when the user selects a start date, the two data parameters should be populated with hte correct dates so your Stored Proc can simply filter based on the @StartDate and @EndDate parameters. If the user chooses the use Date Range option then the default dates will be set to whatever dates you specified in the dsDefaultStartDate and dsDefaultEndDate datasets.

Of course there is a chance that the user selects a real accounting period and then changes the date ranges. There are things you can do to avoid this but one step at a time !

I've written this answer without testing anything so there could be some mistakes but hopefully it will point you in hte right direction. If you have any issues leave a comment.

SSRS parameters based on selection?

1 Answers