Skip to main content

Default values for a date parameter in SSRS

We can use sql queries or some of the cool expressions available in SSRS to set default date parameters:


Set First Day of previous week (Monday)
=DateAdd(DateInterval.Day, -6,DateAdd(DateInterval.Day, 1-Weekday(today),Today))


Set Last Day of previous week (Sunday)
=DateAdd(DateInterval.Day, -0,DateAdd(DateInterval.Day, 1-Weekday(today),Today))


Set First Day of Current Week (Monday)
=DateAdd("d", 1 - DatePart(DateInterval.WeekDay, Today(),FirstDayOfWeek.Monday), Today())


Set Last Day of Current Week (Sunday)
=DateAdd("d" ,7- DatePart(DateInterval.WeekDay,Today(),FirstDayOfWeek.Monday),Today())


Set First Date of last month
=DateAdd("m", -1, DateSerial(Year(Now()), Month(Now()), 1))

Set Last date of last month
=DateAdd("d", -1, DateSerial(Year(Now()), Month(Now()), 1))

Set First date of current month
=DateSerial(Year(Now()), Month(Now()), 1)

Set Last date of current month
=DateAdd("d",-1,(DateAdd("m", 1, DateSerial(Year(Now()), Month(Now()), 1))))


--
Labels:

Comments

  1. REAGAN CAESARDecember 29, 2011 at 9:45 AM

    Hi Hardik.. I have a requirement where i need to get the users to select from a dropdown. the Dropdown will contain the weekstart date - weekenddate.The user will not be able to see the same week or previous weeks also he should be able to see just 4 weeks in the dropdown and not more than that .I require the solution in asp.net in C# > could anyone help me..my id is caesar.reagan@gmail.com quick help is really appreciated

    ReplyDelete
  2. AditiJuly 25, 2013 at 10:28 AM

    Thanks a ton!! These were very helpful!!

    ReplyDelete
  3. AnonymousFebruary 25, 2014 at 10:40 AM

    Really helpful! Thanks a ton!! - Pradeep Dash

    ReplyDelete

Post a Comment

Popular posts from this blog

ALL as a parameter value in ssrs oracle sql

In some cases as per the requirements you will need to add 'ALL' as an independent parameter value (not a multivalue parameter) 1) Create a new dataset to populate parameter values Ex: select distinct city from table1 where city is not null        union       select 'ALL' as city from dual       order by 1 2) Modify your main dataset query Ex: where (city = :city or 'ALL' = :city) Thank you
Post a Comment
Read more

_x003C_none_x003E_ FlatFile Error in SSIS

If you are receiving this error in your output file, It is because of the value in the text qualifier property (Text Qualifier = <none>).  When you export the data in a file, you might see the _x003C_none_x003E_ (x003C = "<" and none and x003E = ">") with your original field value. • Do not open the connection manager editor once you finalize everything and just clear out the <none> value  from the property window on the right side (Do not open show editor) and save it. You’re done. • Other way around, You can also define the Expression value for the text qualifier and provide (“”) as a value in the expression. Go to the property --> Expression --> Select the Text qualifier from the drop down list --> provide the expression value (“”)
1 comment
Read more