How To: Group a query by a date range (Week/Day/Month/Year) in Web Access like in Console

Version 6

    Verified Product Versions

    Service Desk 7.6Service Desk 7.7.xService Desk 2016.xAsset Manager 2016.xAsset Manager 2017.xService Desk 2017.x

    Requires Access To:

    Service Desk Console

    Web Access


    How To:

    There is no feature to have queries grouping by other than the raw attribute in Web Access, meaning that if you set a query to group by a date in WebAccess it will group by the second.

    In this article, we are going to review a trick to set the query to group by week, day or year as you can in Console.


         Even if you group the query in Console and then use it in Web Access the group range will not be used.


    Step by Step:

    This can be achieved with some design changes and use of a Calculation. In this example, we'll use the Process object to be grouped by date range.


    1. Create a string (100 char) Attribute on the Process Object
    2. Set this attribute as a 'Before Read' Calculation to show the Week and Year
      It can then set your Query to Group by this attribute instead of the Creation Date and with this get the weekly grouping that you want.

    3. The Calculation that you would need to use would be as follows:

      import System

      static def GetAttributeValue(Process):

           Cal = System.Globalization.GregorianCalendar()

           Date = Process.CreationDate

           Week = Cal.GetWeekOfYear(Date, System.Globalization.CalendarWeekRule.FirstDay, DayOfWeek.Monday)

           Value = String.Format(" Week ", Date.Year, Week)

           return Value

    4. Set the calculation dependency on "Creation Date"


    We can now use this attribute to group by range for every new created record


    If you have already existing records that you want included in this, you will need to create the Calculation, make sure that it's working and then run a scheduled calculation to set the value on all OPEN processes.

              (Please note that you will not be able to run the scheduled calculation on any Closed processes).

         Once done, the calculation can be set back to "After Read".