question

vsuresh avatar image
vsuresh asked

SSRS date parameter

In SSRS reports I'm trying to have a date parameter with a drop down selection of dates like current date, start of month, prior month start. These will be defined using an expression. And then a custom date for which I want the user to be able to select from the date picker. Is this possible? How can I do it?

Thanks

ssrs bids
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

You could probably do this with date expressions in the parameter, but honestly, why? You could set the most used value as the default to make things easier on the user, but they should be competent enough to pick the 1st of this month or last month if that's what they need. Adding in the code to make it display that way seems like unnecessary work.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vsuresh avatar image
vsuresh answered

Hi Jon,

Thank you for replying. The reason I wanted to do this is because I want to schedule the report using subscriptions - on the first of the month the monthly report should pull up prior month info; the daily report should pull up current month to date info. I also want to give the user the ability to run the report for any start and end dates using the date picker, not typing in the date.

I am fairly new to SSRS. I thought I could add multiple default values and then be able to select them in the subscription, but I was getting an error, only one default value allowed. So I now tried this : created another parameter called Date Range with a drop down list of values - PriorMonth, CurrentMonth and Custom Dates.

For the startdate in the default values I used this expression:

=IIF(Parameters!DateRange.Value="PriorMonth",DateValue(DateAdd("M",-1,DateAdd("D",-(Day(Now)-1),Now))),DateValue(DateAdd("D",-(Day(Now)-1),Now)))

I did the similar thing for the end date - so that I get the prior month end date and todays date for currentmonth. This way I was able to schedule the monthly and daily reports and get the date picker for custom dates. It is working as intended and I have my subscriptions scheduled.

Thanks again

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jon Crawford avatar image
Jon Crawford answered

Ok, glad you figured it out, but you should also be able to just set the parameter value in the subscription itself without having to set as a default for the purpose of the subscription.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

vsuresh avatar image
vsuresh answered

Thanks Jon. I will look into what you are suggesting, maybe I overlooked something, if it is as simple as that. Thanks

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.