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

_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

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

Keep Column Headers Visible while scrolling in SSRS 2008

When you try to set up Tablix property for keep header visible while scrolling, you will see that it is not working in SSRS 2008 or SSRS 2008 R2. You need to take some extra steps to set up this property 1)       Uncheck keep header visible while scrolling tab under tablix property                            2)       Go to Grouping and Click on the right side arrow (Advanced Mode) 3)       Select the first Static Property in Row Groups (In your report first row should display column headers) 4)       Make Fixed Data = True                  5)       Run the report and you will get column headers visible while scrolling Hope it helps! Thanks
8 comments
Read more