how to create report for last 7 days?

[lee mcdermott]

Hi 

I am trying to schedule a report so that it runs on a Friday afternoon to show all calls logged that week.

Is there any way to get it to run by using "last 7 days".

I currently have the below configured.

My problem is I need to get the weekly figures by Mid afternoon. So for example if it ran at 2pm, I will miss any calls logged between 2 and end of day at 5. Therefore not getting totally accurate figures.

What I hoped is if I schedule a report to run at 2pm every Friday  and it was for the last 7 days (i.e. the previous friday @ 2pm until the report runs on the friday @2pm) i will always be getting accurate figures in terms of a full week.

Is there anyway to configure the values below to be more granular rather than just the options available. I have been using "user prompted value" to manually select the dates and times, but ideally want to schedule it so save a repetitive job each week.

 

I hope that makes sense

 

thanks

lee

[Guest Mary]

@lee mcdermott

You can use INTERVALS in your report filter. 

If your report is run at 14:00 on Fridays, using  (h_datelogged >= DATE_SUB(CURDATE(), INTERVAL 168 HOUR)) AND (h_datelogged < CURDATE()) in your filter will return requests logged between 14:00 last Friday and 14:00 on the Friday when the report is run. 

 

[lee mcdermott]

@Mary

 

Thanks Mary thats great, I will give that a go.

 

lee

[Victor]

@lee mcdermott while the above is an option it is not the most friendly query that runs on the database... to get a weekly list of records, specifically from 7 days ago at HH:MM until today at HH:MM, you can use this (simpler) filter:

The subquery is not necessary as the filter is straightforward and you only need one to point the date logged to "current date/time - 7 days", no need to specify the date logged being less than current date/time, there won't be any records with a date logged past current date/time (future).

Mind you that the date/time DB values returned by the report are GMT/UTC (they are not converted to a timezone such as BST)

[lee mcdermott]

@Victor

 

thanks victor, i managed to get Mary's working but had to tweak it as it was listing everything logged from 7 days ago rather than from the time i run the report 7 days ago.

 

I have just tried yours and that works fine and is a little easier to type.

 

thanks

lee

[Victor]

@lee mcdermott good news

Yes, Mary's suggestion works, no doubt, but that subquery is not necessary for the purpose of the report and also (as with any subquery) is more taxing on the database.