Dynamically Refresh SQL Server Reporting Services Report Date Parameters

By:   |   Comments (12)   |   Related: > Reporting Services Parameters


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.

Adding New Report

Step 2: Create Year, Month, StartDate and EndDate SSRS Report Parameters

Add Year Parameter

Add Year parameter as shown below.

Add_Year_Parameter

Specify available values for the Year parameter as shown below. You can also use a dataset for the Year parameter values.

Year_Parameter_Available_Values

Add Month Parameter

Add Month parameter as shown below.

Add_Month_Parameter

Specify available values for the Month parameter as shown below. You can also use a dataset for the Month parameter values.

Month_Parameter_Available_Values

Add StartDate Parameter

Add StartDate parameter as shown below.

Add_Start_Date_Parameter

Add EndDate Parameter

Add EndDate parameter as shown below.

Add_End_Date_Parameter

After adding the Year, Month, StartDate and EndDate parameters, the Report Data Pane looks like the below image.

Report_Data_Pane_After_Adding_Parameters

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.

Start_Date_Default_Value

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.

End_Date_Default_Value

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.

Report_Preview_With_No_Default_Value_Refresh

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.

Add_Default_Start_Date_Parameter

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.

Default_Start_Date_Parameter_Available_Value

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.

Default_Start_Date_Parameter_Default_Value

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.

Add_Default_End_Date_Parameter

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.

Default_End_Date_Parameter_Available_Value

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.

Default_End_Date_Parameter_Default_Value

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.

Report_Data_Pane_After_Adding_Default_Parameters

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.

Start_Date_Default_New_Value_Expression

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.

End_Date_Default_New_Value_Expression

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).

Report_Preview with Default Value Refresh
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ghanesh Prasad Ghanesh Prasad leads a team in Microsoft Business Intelligence and has over 5 years of experience working with SQL Server.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Thursday, November 19, 2020 - 12:40:46 AM - Raj Back To Top (87807)
Thanks Ghanesh for an excellent solution.

Further to his solution, the problem can be resolved by simply removing the Label values within the 'Available Values' option on the Default Start Date and Default End Date parameters themselves. You will see the correct date values being refreshed in these fields itself when the Year and Month param values are changed.

No need to use the Start and End Date params and no need to worry about making them internal/hidden :)

Thursday, November 19, 2020 - 12:31:04 AM - Raj Back To Top (87806)
Hi everyone,

First thanks to Ghanesh for providing this solution.

Further to his solution, no need to worry about making the Start and End Date parameters hidden/internal. In fact no need to even create these parameters.

The required refresh capability can be obtained on the Default Start Date and Default End Date parameters themselves. Simply remove the Label values from the 'Available Values' option on these parameters. This should solve the problem.

Monday, July 20, 2020 - 4:30:42 AM - Meho Back To Top (86155)

I must say this is a neat solution but unfortunately not very practical for the vast majority of cases. For this to work the "default parameters" must remain visible, something that is mostly undesirable.  

cheers

Tuesday, February 25, 2020 - 6:19:11 AM - Vignesh Back To Top (84787)

Hi Ghanesh Prasad,

If i am changing the "Select parameter visibility" property under general for "Default Start Date" and "Default End Date" to Hidden or Internal, My Start and End Date is not geting refreshed again on changing the month.

I dont want these fields visible in the UI, Can you please help me in this.

Monday, August 5, 2019 - 2:39:25 PM - Jeremy Kadlec Back To Top (81963)

Ajay,

Thank you for the comment.  With the solution shown here, if you hide the date parameters the solution does not work.  Therefore, the parameters need to be visible. Please refer to the images in Step 8 as a point of reference.

Thank you,
Jeremy Kadlec
Community Co-Leader

Wednesday, July 31, 2019 - 8:17:15 AM - Ajay Kumar Back To Top (81912)

Good tip, Ganesh.

I applied this solution but the issue is that this mechanism stops working when I hide or make the default parameters as internal. I don't want to show the default parameters to the users.

Any thoughts?

Thanks.

Ajay

Wednesday, July 12, 2017 - 9:58:34 PM - saiyashwan Back To Top (59304)

I have similar requirement where I need to allow the user to select at any time for 1 year worth of data and restrict the user to select start and end dates within the year selected in year parameter?

Any insight would really help!!

 

Tuesday, January 3, 2017 - 2:04:28 AM - Guruprashanth Back To Top (45088)

Hi Ganesh,

The solution that U hv provided works fine. But I have an issue where I wish not to show the Defalut Parameters that have been generated. If I hide those parameters then the newly selected paramter value is not being reflected in the Paramters. Can u please provide a solution for this?. Thanks in Advance.

Sunday, February 14, 2016 - 11:26:16 PM - Srinivas G Back To Top (40678)

 

Hi Ganesh,

Its very nice post.

Wednesday, December 2, 2015 - 8:44:25 AM - Brunda Mala Back To Top (40178)

Hi Ganesh,

There is another simple tip.

You can give the same expression in both Available Values and Default values property of Parameter by 'Specify values' or 'Get Values from Query'.

It will automatically refresh based on the selected values.

I did this and it worked for me.

Kind Regards,

Brunda

Sunday, August 16, 2015 - 11:28:58 AM - kishore Back To Top (38454)

Its very nice post, 

Im new to SSRS and I have one doubt, please clarify me.

why we need to create two dummy parameters, in place of creating dummy parameters whatever we done to defaultstartdate and defaultenddate can be done to the startdate and enddate parameters right? please correct me if im wrong.

Thanks alot.

Regards

Kishore K

 

Friday, August 14, 2015 - 5:42:37 AM - Hitesh Shah Back To Top (38446)

Thanks Ganesh. It is a nice post.

The default parameters are used to refresh date parameters should be for internal use only. To do so, if we make the parameters visibility to either hidden or internal then the refresh mechanism does not work.

How do we make this two things possible?

Regards,

Hitesh Shah














get free sql tips
agree to terms