Starting a discussion to compile formulas that are commonly needed for typical Service Desk reporting. Hopefully this will become a big time saver for those who are not creating reports daily. - CABI 3.2 (SP5)
////////////////////////////////////////////////////////
///////////Hyperlinks
I/R/P
https://[SD Server]/CAisd/pdmweb.exe?OP=SEARCH&FACTORY=cr&SKIPLIST=1&QBE.EQ.ref_num=([Ref Num])
Contact:
https://[SD Server]/CAisd/pdmweb.exe?OP=SEARCH&FACTORY=cnt&SKIPLIST=1&QBE.EQ.id=([Customer Id])
Change:
https://[SD Server]/CAisd/pdmweb.exe?OP=SEARCH&FACTORY=chg&SKIPLIST=1&QBE.EQ.chg_ref_num=([Chg Ref Num])
IRPC combined hyperlink final hyperlink since we can’t see it in BOXI after first creating:
https://[SD Server]/CAisd/pdmweb.exe?OP=SEARCH&FACTORY=(If(Substr([Ticket Number];0 ;1)="C") Then("chg") Else("cr"))&SKIPLIST=1&QBE.EQ.id=[Ticket Id Number]
Configuration Item:
https://[SD Server]/CAisd/pdmweb.exe?OP=SEARCH&FACTORY=nr&SKIPLIST=1&QBE.EQ.id=([CI id for hyperlink])
[CI id for hyperlink] variable:
=Substr([Uuid] ; 3 ; 32)
////////////////////////////////////////////////////////
///////////Prompt Value Measure:
="Date Range : "+ UserResponse("Enter Time Stamp(Start):")+" to "+ UserResponse("Enter Time Stamp(End):")
="Group(s) : "+ UserResponse("Enter value(s) for Group Name:")
////////////////////////////////////////////////////////
///////////Examples for using where
=Count([Ref Num]) Where ([Type Symbol] = "Incident")
=Count([Ref Num]) Where (([Type Symbol] = "Incident") And ([Sla Violation] = 1))
=Count([Ref Num]) Where (([Type Symbol] = "Incident") And ([Priority Symbol] = "1 - HIGH"))
="Incident Service Level Target Met : " + ((([Count Incidents]) -(Count([Ref Num]) Where (([Type Symbol] = "Incident") And ([Sla Violation] = 1))))/[Count Incidents])*100 + "%"
////////////////////////////////////////////////////////
///////////month and year grouping
=Month([Time Stamp])+" / "+FormatDate(ToDate(FormatNumber(Year([Time Stamp]);"####");"yyyy"); "yyyy")
////////////////////////////////////////////////////////
///////////Date Conversion
=[Changes].[Month]+"-"+FormatDate(ToDate(FormatNumber([Changes].[Year]; "####");"yyyy"); "yyyy")
=FormatDate(ToDate(FormatNumber([Changes].[Month]; "##");"mm"); "mm")+"-"+FormatDate(ToDate(FormatNumber([Changes].[Year]; "####");"yyyy"); "yyyy")
=FormatDate(ToDate(FormatNumber([Changes].[Month]; "MM")+"-"+(ToDate(FormatNumber([Changes].[Year]; "YYYY")
=ToDate("24/10/2010 00:00:00" ; "d/M/yyyy hh:mm:ss")
////////////////////////////////////////////////////////
///////////Convert number to month name:
=Month(ToDate(FormatNumber([Changes].[Month];"00") ;"MM"))
////////////////////////////////////////////////////////
///////////Display Full Name of Ticket Type
=If([Ticket Type]="I";"Incident";
If([Ticket Type]="R";"Request";
If([Ticket Type]="P";"Problem";
If([Ticket Type]="C";"Change"
))))
Ticket Type
=Substr([Ticket Number]; 0 ; 1)
Merge the Ref Nums to “Ticket Number”
////////////////////////////////////////////////////////
///////////Running Sum for Pareto Charts
=RunningSum([Incident Count])/Sum([Incident Count]) In Report *100