CA Service Management

Expand all | Collapse all

BOXI Formula Cheat Sheet

  • 1.  BOXI Formula Cheat Sheet

    Posted Jun 20, 2014 12:18 PM

    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



  • 2.  Re: BOXI Formula Cheat Sheet

    Broadcom Employee
    Posted Jun 20, 2014 01:18 PM

    Fred...........

     

    Thanks for sharing your CABI/BOXI report writing experise with the rest of the community.

     

    Much appreciated!



  • 3.  Re: BOXI Formula Cheat Sheet

    Posted Jun 23, 2014 12:43 AM

    Great! Thanks for sharing Fred



  • 4.  Re: BOXI Formula Cheat Sheet

    Posted Jun 23, 2014 01:48 AM

    thanks for sharing



  • 5.  Re: BOXI Formula Cheat Sheet

    Posted Jun 23, 2014 02:26 AM

    Great! Thanks Fred