Post reply

AutoRefresh fails after midnight on reports with date parameter

  • September 4, 2012 at 9:26 am

    #267253

    Hi

    We use some reports in our NOC which have date parameters with defaults set to the current day. These reports are set to autorefresh at given intervals configured in the RDL. When midnight rolls around, the reports stop refreshing properly.

    Is there some way for the report to update the date-based parameters after midnight so it can autorefresh properly?

    Thanks

    --Andy

  • September 19, 2012 at 7:48 am

    #1539570

    bump. still stumped here...

  • September 24, 2012 at 9:06 am

    #1541463

    Would your report work if it ran at 12:01am? What does the report's query look like? It's hard to speculate why this might not work without more info.

    Rob

  • September 25, 2012 at 7:57 am

    #1541875

    Use an expression on the default that will set the value of the parameter to the previous day from midnight until (e.g.) 8am?

    Something like:

    =iif(DatePart("h", now()) < 8, today()-1, today())

    or the equivalent in T-SQL if you're doing it from within the report.

  • September 26, 2012 at 7:41 am

    #1542407

    The query is a simple select with a StartDate and EndDate constrained by the RDL parameter. I can try the suggestion of using a formula in the parameter default, but from what I understand, the parameter defaults do not get reset when a report auto-refreshes...

  • September 26, 2012 at 8:29 am

    #1542456

    Ah, I see. Don't know for definite the behaviour of the default parameters under auto-refresh, but I'd hope they get re-evaluated at each refresh.

    Guess you'll have to try it & see.

    If not, you can put the change into the T-SQL, which should definitely refresh every time.

    Assuming you have something like this:

    WHERE DateField >= @StartDate

    AND DateField < @EndDate

    Would become:

    WHERE DateField >= CASE WHEN DatePart(HH, GETDATE()) BETWEEN 0 AND 7 THEN DATEADD(DD, -1, @StartDate) ELSE @StartDate END

    AND DateField < CASE WHEN DatePart(HH, GETDATE()) BETWEEN 0 AND 7 THEN DATEADD(DD, -1, @EndDate) ELSE @EndDate END

  • September 26, 2012 at 8:31 am

    #1542459

    Thanks, I will give both options a try! Appreciate the help.

  • September 26, 2012 at 9:12 am

    #1542484

    No problem, hope one of them works!

  • October 11, 2012 at 1:23 pm

    #1548330

    I've been so sick and then so swamped I haven't been able to spend a minute on this until now!

    The code examples listed will set the date of the parameter to the previous day if it is between midnight and 7am. What I'm trying to do is as of midnight, have it know that the day has changed when the AutoRefresh period rolls around. Not have it think it's still yesterday. As of midnight, we want the reports to automatically start displaying today's data. Currently, as of midnight, it gets stuck on the dates that are listed in the parameters, which equal yesterday's date.

    When the reports are pulled up, they get defaults in the date fields filtering the return to today's data. After midnight, they only show yesterday's data because the date field parameters are not refreshed to the current day. They remain static and therefore after midnight have yesterday's date.

    Hope that makes this clearer.

    Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply