How to get end last month report from Service manager


What is the query if I need to return the "Last Full Month" records?
Let's say we are in Febuary & I want to pull all the data from January?

  • Hi
    Please refer to SM programming guide, you can use functions like tod(), month and year.
    My idea is to ignore the date and get records with
    month of open.time = month of today - 1 month
    and check for the year

    here is an example:
    month(open.time)=month(tod()-'30 00:00:00') and year(open.time)=year(tod()-'30 00:00:00')
    please note that for this one, if you are on March 1st, it will return January data because, well -30 days will get you back to Jan not Feb.
    you can use this and improve it.
  • fast update:
    actually you can do jscall in a report. so here is your solution:
    have a script library, for this example "testlib"
    get this function inside:
    function lastmonth()
    var dte=new Date();
    return dte;

    on your report use this query:

    month(open.time)=month(jscall("testlib.lastmonth")) and year(open.time)=year(jscall("testlib.lastmonth"))
  • Verified Answer

    you can use this query for example if it's based on the open.time field:
    (month(open.time)=month(tod())- 1 and year(open.time)=year(tod()))
  • You can use the the startofmonth() function. For example, lets say its Feb and you want to display all incidents resolved in the past previous 4 months;you can use this query to display all results for start to end of months for Oct, Nov, Dec, and Jan:

    resolved.time>=startofmonth(tod() - '120 00:00:00') and resolved.time<startofmonth(tod())


    Feel free to include additional conditions to filter out records.