The Form Builder in ServiceDesk has a built-in column for displaying the Age of a Ticket (Process.Age Description). Unfortunately, the column's value does not account for the Business Hours of the SLA assigned to the Ticket. The attached Functions and View will correct this.
Before we begin, familiarize yourself with the following post: Using Custom SQL in ServiceDesk Report Builder and Still Use Parameters!!! https://www.symantec.com/connect/articles/using-custom-sql-servicedesk-report-builder-and-still-use-parameters We will use this 'Custom SQL' technique to enhance your existing or new reports. Proceed as follows: 1) Create the Table function using: create-tvf_GetNewBusinessHours.txt 2) Create the Scalar function using: create-svf_GetNewAge.txt 3) Create the View using: create-v_View_NewAgeDescription.txt NOTE: These scripts USE ProcessManager, adjust accordingly. TEST QUERY: select * from View_NewAgeDescription 4) Create the Workflow Profile as discussed in the aforementioned 'Custom SQL' post. NOTE: You will be using 'View_NewAgeDescription' as the Table Name. 5) Integrate the Workflow Profile into your report as discussed in the aforementioned 'Custom SQL' post. With this, you will now see a 'New Age Description' that respects the Business Hours of the SLA assigned to the Ticket.
UPDATE: Additional Views that factor in Hold Time for the Age Description ( Dec 13, 2017 )
1) create-v_View_Hold_Seconds.txt This calculates the seconds that the Ticket has been on hold. This is an aggregate value, Business Hours are not used. 2) create-v_View_NewAgeDescriptionMinusHold.txt This replaces View_NewAgeDescription.
UPDATE: Added Business Hours test script (test_business_hours.txt). This will show the parsed DateTime values for the SLA used in the process.
Regarding the non-US date-time formats, the function GetNewBusinessHours uses the following conversion: replace(convert(varchar, getdate(), 111), '/', '-') Change the '111' to whatever format is appropriate.
Correction to above... It seems there is an issue in parsing the XML file to get the business hours used in the Get New Business Hours function. The code assumes Object 4 and 5 in the xml file are the working hours but this is not the case. The object number is dependent on the number of holiday entries in the database, and can be a much higher set of object numbers. See below the working hours are object 7 and 8.
<ObjectStorageContainer xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<SerializationVersionNumber>LogicBase Serialization Version 2.0</SerializationVersionNumber>
<TopObject>0</TopObject>
<TopObjectType>LogicBase.Core.Data.DataTypes.BusinessTimeSpanConfig</TopObjectType>
<ReferencedAssemblies>LogicBase.Core</ReferencedAssemblies>
<ReferencedAssemblies>mscorlib</ReferencedAssemblies>
<References>
<Object id="0" type="LogicBase.Core.Data.DataTypes.BusinessTimeSpanConfig">
<Values>
<Value id="holidays" type="oref" val="1" />
<Value id="weekendDays" type="oref" val="6" />
<Value id="bizHoursStart" type="oref" val="7" />
<Value id="bizHoursEnd" type="oref" val="8" />
</Values>
</Object>
<Object id="2" type="LogicBase.Core.Data.DataTypes.BusinessTimeSpanConfig+HolidayInfo">
<Value id="date" type="data" val="636579648000000000" />
<Value id="description" type="data" val="Good Friday" />
<Object id="3" type="LogicBase.Core.Data.DataTypes.BusinessTimeSpanConfig+HolidayInfo">
<Value id="date" type="data" val="636582240000000000" />
<Value id="description" type="data" val="Easter Monday" />
<Object id="4" type="LogicBase.Core.Data.DataTypes.BusinessTimeSpanConfig+HolidayInfo">
<Value id="date" type="data" val="636812928000000000" />
<Value id="description" type="data" val="Christmas" />
<Object id="5" type="LogicBase.Core.Data.DataTypes.BusinessTimeSpanConfig+HolidayInfo">
<Value id="date" type="data" val="636813792000000000" />
<Value id="description" type="data" val="Boxing Day" />
<Object id="1" type="LogicBase.Core.Data.DataTypes.BusinessTimeSpanConfig+HolidayInfo[]" hint="XSer">
<Value id="1" type="oref" val="2" />
<Value id="2" type="oref" val="3" />
<Value id="3" type="oref" val="4" />
<Value id="4" type="oref" val="5" />
<Object id="6" type="System.DayOfWeek[]" hint="System.DayOfWeek">
<Value id="1" type="data" val="Sunday" />
<Value id="2" type="data" val="Saturday" />
<Object id="7" type="LogicBase.Core.Data.DataTypes.BusinessTime">
<Value id="hour" type="data" val="8" />
<Value id="minute" type="data" val="30" />
<Object id="8" type="LogicBase.Core.Data.DataTypes.BusinessTime">
<Value id="hour" type="data" val="17" />
<Value id="minute" type="data" val="0" />
</References>
</ObjectStorageContainer>
Hi... Believe this is set for US date/time formats and not international datetime formats? When using this I get an error 'conversion of a varchar data type to a datetime data type resulted in an out-of-range value' . This is symptomatic of a date/time format mismatch and coding for US date format not International.