reporting-servicesreportbuilder3.0

Report Builder 3.0 date parameter with specific time

I have an end-of-shift report I'm trying to automate. I am trying to create a preset start and end date parameter for shift schedules. StartDate: (AM Shift = Today @ 06:00) & (PM Shift = Yesterday @ 18:00) EndDate: (AM Shift = Today @ 18:00) & (PM Shift = Today @ 06:00). Since there are two shifts, I don't want to hardcode my date/time range into my query. And since the shifts are unchanging, I don't want to just set the calendar and give the user options to change the date/time range. I changed the parameter type to date/time and am attempting to fix the expression for available values to these predetermined date/times. Because I do not want the user to change the date/time range and only select the specific shift schedule, I am having difficulty getting the expression to work with both date and time separately. All of the below do not work, but are what I've tried. Examples specifically for Yesterday at 18:00.Any assistance would be greatly appreciated. Thank you in advance.

Solution

  • You cannot use CDate like this, it's easier to start with a date and then add or subtract from it.

    for example

    =DateAdd("H", 18, DateAdd("D", -1, Today()))

    The second DateAdd gives us Yesterday and because we use Today() rather than Now() it gives us yesterday at time 00:00:000, now all we have to do is add 18 hours to it and we get yesterday at 6pm

    If you want to base this on a parameter (called shift in this example) then you can do something like this

    Start -

    =IIF(Parameters!shift.Value = "AM" , 
    DateAdd("H",  6, Today()), 
    DateAdd("H", 18, DateAdd("D", -1, Today()))
    )

    End -

    =IIF(Parameters!shift.Value = "AM", 
    DateAdd("H", 18, Today), 
    DateAdd("H", 6, Today)
    )