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

Version 6

    Verified Product Versions

    LANDESK Service Desk 7.6LANDESK Service Desk 7.7.xLANDESK Service Desk 2016.xLANDESK Asset Central 2016.xLANDESK Asset Central 2017.xLANDESK Service 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".