Report using something like startoflastmonth

[chrisnutt]

Hi All,

I am trying to create some reports and some widgets that will return details about the previous calendar month. In Supportworks I would do something like:

DateField > startoflastmonth AND DateField < endoflastmonth

I'm trying to find any documentation about if there is something similar in Service Manager reports and widgets but am so far coming up short. Is this because that functionality no longer exists?

Thanks

Chris

[TrevorKillick]

Hi @chrisnutt

We are working on some exciting new features around Reporting and using variables such as startoflastmonth within reports is included, i can't confirm when they are going to be available but as soon as i have some news i will update this thread, i can at least confirm this is in our 90 day window of future changes.

Kind Regards

Trevor Killick 

[Guest]

Hi @chrisnutt

In the interim whilst we are waiting for the new functionality, there are ways you can do this using some standard MySQL statements and the "Use Custom Criteria" option. 

For example, if I was looking for All Requests Logged Last Month (without specifying the specific dates) we can do this using the where clause:

h_datelogged >= LAST_DAY(NOW() - INTERVAL 2 MONTH) + INTERVAL 1 DAY
AND
h_datelogged < LAST_DAY(NOW() - INTERVAL 1 MONTH)

This is configured in reporting as per my screenshot below. This is using a function to get the current date and from that working out the first and last days of last month. 

We don't currently have this documented anywhere as its more standard SQL functions, but I will work on creating an FAQ in the next few weeks and posting back to this post. 

 

Kind Regards

 

Bob

 

 

[chrisnutt]

Thank you @Bob Dickinson