Jump to content

how to create report for last 7 days?


Recommended Posts

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.

image.thumb.png.5f0166d44e5c3c5a6524dd6da45b06c3.png

image.thumb.png.18ad450c490ab86caddeb396e7415333.png

 

I hope that makes sense

 

thanks

lee

Link to post
Share on other sites

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

image.png.97c4e86e1c6cf43eae6dcfd705fb3c33.png

 

Link to post
Share on other sites

@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:

image.png

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)

  • Like 1
Link to post
Share on other sites

@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

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...