Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Dynamically Refresh SQL Server Reporting Services Report Date Parameters


By:   |   Read Comments (5)   |   Related Tips: > 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


Last Update:






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.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, January 03, 2017 - 2:04:28 AM - Guruprashanth Back To Top

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

 

Hi Ganesh,

Its very nice post.


Wednesday, December 02, 2015 - 8:44:25 AM - Brunda Mala Back To Top

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

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

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


Learn more about SQL Server tools