Report Builder - Set datetime parameter

Question

I have a report that has parameters StartDate and EndDate. I would like the EndDate parameter's time part to default to the end of the day when selected from the drop down.

For instance, if a user selects 5/15/2008 from the dropdown, the value shown in the box should be '5/15/2008 23:59:59' not '5/15/2008 12:00:00'

Its pretty easy to do this in .Net using the event model and a line of code but in Report Builder 2.0, what would I need to do?

Is there code that I need to write for this or did I miss some funky expression that could handle this?

Thanks.

AboutDev

Answer 1

Use the parameter in a DATEADD() expression in your dataset.

Rather than

...WHERE end_date = @end_date

do something like this:

...WHERE end_date = DATEADD(ms, -3, @end_date + 1)

That will go forward a day (the +1), then go back 3 milliseconds, to the last instant of the day recordable by a datetime.

Answer 2

It's been awhile since I've used SSRS, so bear with me. You'll have to do a little translation, but here's what I've done in the past.

When you define your EndDate parameter, create an additional parameter named EndDateEOD, after EndDate in your list of parameters. Make this parameter a hidden value, and set it to the last moment of the day, similar to the way that Jeremy calculates it.

Then you can use @EndDateEOD in your report query where you have @EndDate now.

When StartDate is selected, you could have EndDate default to its value, so that EndDateEOD will automatically be set to the end of the start date.

Answer 3

I would suggest setting the default parameter in the Report Parameters section. You can get to this from Report > Report Parameters.

This allows you to set a non-queried default. There you can enter an expression like

=DateAdd(Microsoft.VisualBasic.DateInterval.Second ,-1,dateadd("d",1,Today))

That should give you a default for the end of today.

Edit: Really only useful for a single default value.