Ssrs 2008 make parameter optional?

SSAS and SSRS
1

In an SSRS 2008 report, there are parameters for next school year and another parameter for current school year. The problem is there is no data for the next school year in the database until Feb. 2017. Thus the next school year parameter does not load with data and the popup message for the report says 'please enter a value for the parameter 'Calendar - upcoming year'. The parameter can not be blank'.

Thus can you tell me if one of the following options would work, how I can accomplish that task using ssrs?

  1. Based upon today's date, can I make the parameter 'Calendar - upcoming year' optional? if so, how would I complete that task?

  2. Should I create another parameter and have it select what calendar to pick (upcoming or current year)?

If so, how would I complete that task?

If my suggestions do not work, what would you do to solve my problem and how would be complete your method using ssrs 2008?

2

In SQL Server Reporting Services (SSRS) we can't enable/disable an SSRS report parameter; if we have created a parameter then the user has to provide a value for the parameter. Optional Query Parameters are not available natively in SSRS, but we can create a workaround to create a report.

If a report has an Optional Query Parameter and the user doesn't provide a parameter value and then previews the report, the report will show all of the data. If the user wants to filter the data then the user can filter by entering the parameter value. Optional Parameters can use any operators such as "=", "<", ">", "<>", “IN”, “BETWEEN”, etc.

I have prepared this tip in such a way that an SSRS beginner can also understand the problem and implement the solution. To demonstrate the solution, I will use the AdventureworksDW2008R2 database. You can download the database from codeplex.

1 Like