Problem
You created a report in SQL Server Reporting Services that has four parameters Year, Month and two Calendar Date Picker parameters StartDate and EndDate. The default values for StartDate and EndDate are determined by the Year and Month parameters. So when a new Month or Year is selected the value for StartDate should be the first day of the Month and the EndDate should be the last day of the Month. When you set default values for StartDate and EndDate the parameter values are correct the first time, but if you change the Month or Year the values for StartDate and EndDate don’t change. In this tip we show how to solve this problem.
Solution
In this tip first I will explain the problem with an example and later demonstrate the solution. This tip assumes that you have previous real world work experience building a simple SSRS Report with parameters. To demonstrate the solution, I will use AdventureworksDW2008R2 sample database for SQL Server and SQL Server 2008R2 Reporting Service.
Step 1: Create New SSRS Report
Let’s create a sample report for demo purposes. Please change the report name to Sample_Report. You can refer to the below image.
Step 2: Create Year, Month, StartDate and EndDate SSRS Report Parameters
Add Year Parameter
Add Year parameter as shown below.
Specify available values for the Year parameter as shown below. You can also use a dataset for the Year parameter values.
Add Month Parameter
Add Month parameter as shown below.
Specify available values for the Month parameter as shown below. You can also use a dataset for the Month parameter values.
Add StartDate Parameter
Add StartDate parameter as shown below.
Add EndDate Parameter
Add EndDate parameter as shown below.
After adding the Year, Month, StartDate and EndDate parameters, the Report Data Pane looks like the below image.
Step 3: Set Default Value for StartDate and EndDate Report Parameters
Set Default Value for StartDate Parameter
The default value for the StartDate parameter should be the First Day of the Month, this default value will depend on the Month and Year parameter values. Please use the below expression to set the default value.
=Cdate(Parameters!Month.Label+Parameters!Year.Label)
You can refer to the below image.
Set Default Value for EndDate Parameter
The default value for the EndDate parameter should be the Last Day of the Month, this default value will depend on the Month and Year parameter values. Please use the below expression to set the default value.
=dateadd("d",-1,cdate(Dateadd("m",1,Cdate(Parameters!Month.Label+Parameters!Year.Label))))You can refer to the below image.
Step 4: Default does not get refreshed for StartDate and EndDate Report Parameters
We have set the default values for both the StartDate and EndDate report parameters, let’s preview the report.
As you can see from below image (1), when we preview the report for 2015 Year and Jan Month parameter values the default values for StartDate and EndDate are set correctly, but when we change the Month parameter value to Feb the StartDate and EndDate parameter default values do not refresh, you can refer to below images (2 and 3).
This is our problem that we need to solve, the default values do not get refreshed for the cascaded Calendar Date Picker. In the next steps we will solve this problem.
Step 5: Add Dummy Parameters
We have to add two dummy parameters, one for StartDate and another for EndDate.
Add DefaultStartDate Parameter
Add a new DefaultStartDate parameter as shown below. This parameter value will be used as the source for the StartDate Calendar Date Picker parameter later.
Specify available values for DefaultStartDate parameter using the expressions shown below. This expression gives the first day of the month based on the Month and Year parameter values.
Label Expression
=Left(Cdate(Parameters!Month.Label+Parameters!Year.Label),10)
Value Expression
=Cdate(Parameters!Month.Label+Parameters!Year.Label)
You can refer to the below image.
The default value for the DefaultStartDate parameter should be the First Day of the Month. Please use the below expression for the default value. This default value will depend on the Month and Year parameter values.
=Cdate(Parameters!Month.Label+Parameters!Year.Label)
You can refer to the below image.
Add DefaultEndDate Parameter
Add a new DefaultEndDate parameter as shown below. This parameter value will be used as the source for the EndDate Calendar Date Picker parameter later.
Specify available values for the DefaultEndDate parameter using the expressions shown below. This expression gives last day of the month based on the Month and Year parameter values.
Label Expression
=Left(dateadd("d",-1,cdate(Dateadd("m",1,Cdate(Parameters!Month.Label+Parameters!Year.Label)))),10)Value Expression
=dateadd("d",-1,cdate(Dateadd("m",1,Cdate(Parameters!Month.Label+Parameters!Year.Label))))You can refer to below image.
The default value for the DefaultEndDate parameter should be the Last Day of the Month. Please use the below expression for the default value. This default value will depend on the Month and Year parameter values.
=dateadd("d",-1,cdate(Dateadd("m",1,Cdate(Parameters!Month.Label+Parameters!Year.Label))))You can refer to the below image.
Step 6: Change Parameter Ordering
We have successfully added two dummy parameters DefaultStartDate and DefaultEndDate. DefaultStartDate and DefaultEndDate parameter values will be used as the source for the StartDate and EndDate Calendar Date Picker parameters respectively. StartDate and EndDate Calendar Date Picker parameter values are dependent on DefaultStartDate and DefaultEndDate parameters, so we have to change the parameter ordering. DefaultStartDate and DefaultEndDate parameters should come before StartDate and EndDate parameters. You can refer to the below image.
Step 7: Set New Default Expression for StartDate and EndDate Parameters
We will use the DefaultStartDate parameter value as the default value for StartDate parameter. Please use the below expression to set the new default value for the StartDate parameter.
=Cdate(Parameters!DefaultStartDate.Value)
You can refer to the below image.
We will use the DefaultEndDate parameter value as the default value for the EndDate parameter. Please use the below expression to set the new default value for the EndDate parameter.
=Cdate(Parameters!DefaultEndDate.Value)
You can refer to the below image.
Step 8: Report Preview
We have done all of the necessary changes, now let’s preview the report.
As you can see from the below image (1), when report was previewed with 2015 Year parameter value and Jan Month parameter value then the StartDate and EndDate Calendar Date Picker default values were set correctly.
When the Month parameter value was changed to Feb then the StartDate and EndDate parameter values were also refreshed, you can refer to below image (2 and 3).
When the Year parameter value was changed to 2016 then the StartDate and EndDate parameter values were refreshed, you can refer to the below image (4).
Next Steps
- Check out Calendar Date Picker for MDX based SSRS report here.
- Check out other tips on SQL Server Reporting Services Parameters.
- Check out all of the SQL Server Business Intelligence Tips and Tricks.
- Check out cascaded parameters in SSRS here.
Ghanesh Prasad is a Microsoft BI lead developer for an industry leading Indian IT Service Company in Bangalore. He has over 5 years of work experience with Microsoft Business Intelligence and expertise in SQL Server, SQL, T-SQL, SSRS, SSIS, SSAS, and MDX languages. In his organization he is responsible for developing end to end data loading solutions, cube development, report development, migration, testing, deployment and maintenance.