Home > SSRS > Dynamic Parameters, Dependent on CurrentDate in SSRS

Dynamic Parameters, Dependent on CurrentDate in SSRS

From MSDN I got this question Dynamic Parameters.

User Comment:-

I have a report requirement where I have to populate my parameters dynamically

Fiscal calander as per my company

Quarter1

September

October

November

Quarter 2

December

January

February

Quarter 3

March

April

May

Quarter 4

June

July

August

Based on this my @startDate and @EndDate Parameters should get populated depending upon today’s date

Ex: If I run today, Quarter should be 3  and startdate should be March1,2015 and enddate should be May 31 2015

Lets Create basic table as per your case, we will generate one more column which is divided by 3 to get the range.

Table

Now we can write iif expression in all three parameters based on range(Month number/3).

First Parameter : Quarter.

Default Value  expression would be like :-

=iif(month(Now())/3>=1 AND month(Now())/3 <2,“Quarter 3”,iif(month(Now())/3>=2 AND month(Now())/3 <3,“Quarter 4”,iif(month(Now())/3>=3 AND month(Now())/3 <4,“Quarter 1”,“Quarter 2”)))

Second Parameter and Third parameter are Startdate and EndDate can be written using above logic or based on first parameter value also we can write.

Second Parameter : StartDate

Default value expression would be like:-

=iif(Parameters!Quarter.Value=“Quarter 3”,cdate(“3/1/”+cstr(Year(Now()))),iif(Parameters!Quarter.Value=“Quarter 4”,cdate(“6/1/”+cstr(Year(Now()))),iif(Parameters!Quarter.Value=“Quarter 1”,cdate(“9/1/”+cstr(Year(Now()))),cdate(“12/1/”+cstr(Year(Now()))))))

Third parameter: EndDate

Default value expression would be like:-

=iif(Parameters!Quarter.Value=“Quarter 3”,cdate(“5/31/”+cstr(Year(Now()))),iif(Parameters!Quarter.Value=“Quarter 4”,cdate(“8/31/”+cstr(Year(Now()))),iif(Parameters!Quarter.Value=“Quarter 1”,cdate(“11/30/”+cstr(Year(Now()))),iif(Year(Now()) Mod 4=0,cdate(“2/28/”+cstr(Year(Parameters!StartDate.Value))),cdate(“2/28/”+cstr(Year(Now())))))))

There is one problem of setting Feb Month end date as it can be 28 or 29,  I will get back on this.

Lets run the report

DynamicParameter

Thanks
Prasad

Learn Different BI Tools
Subscribe my YouTube Channel : BI Tools

Advertisements
Categories: SSRS Tags: ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: