AutoRefresh fails after midnight on reports with date parameter

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » AutoRefresh fails after midnight on reports...

AutoRefresh fails after midnight on reports with date parameter

Rate Topic

Display Mode

Topic Options

Author

Message

Andy Gerard

Posted Tuesday, September 04, 2012 9:26 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, November 12, 2012 4:33 PM
Points: 24, Visits: 105

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

Post #1353992

Andy Gerard

Posted Wednesday, September 19, 2012 7:48 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, November 12, 2012 4:33 PM
Points: 24, Visits: 105

bump. still stumped here...

Post #1361354

robert.gerald.taylor

Posted Monday, September 24, 2012 9:06 AM

Right there with Babe

Group: General Forum Members
Last Login: Yesterday @ 11:10 AM
Points: 740, Visits: 785

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

Post #1363559

Gazareth

Posted Tuesday, September 25, 2012 7:57 AM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 1:53 AM
Points: 1,474, Visits: 2,342

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.

Post #1364051

Andy Gerard

Posted Wednesday, September 26, 2012 7:41 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, November 12, 2012 4:33 PM
Points: 24, Visits: 105

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...

Post #1364681

Gazareth

Posted Wednesday, September 26, 2012 8:29 AM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 1:53 AM
Points: 1,474, Visits: 2,342

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

Post #1364738

Andy Gerard

Posted Wednesday, September 26, 2012 8:31 AM

Grasshopper

Group: General Forum Members
Last Login: Monday, November 12, 2012 4:33 PM
Points: 24, Visits: 105

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

Post #1364741

Gazareth

Posted Wednesday, September 26, 2012 9:12 AM

UDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 1:53 AM
Points: 1,474, Visits: 2,342

No problem, hope one of them works!

Post #1364774

Andy Gerard

Posted Thursday, October 11, 2012 1:23 PM

Grasshopper

Group: General Forum Members
Last Login: Monday, November 12, 2012 4:33 PM
Points: 24, Visits: 105

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

Post #1371749

« Prev Topic | Next Topic »

Permissions