Custom Formatting

Version 1
    This document was generated from CDN thread

    Created by: Raymond Lynn on 24-11-2011 05:37:45 AM
    I want to display a field as a two/three digit percentage with no digits after the decimal point but am unable to work out the required Custom Format
     
    If I use the standard format
     
    12,345.68% it displays 100.00% but I want 100% For values less than 100% I would like to display 99%, 45% etc
     
    I have also tried to do this in a Formula field but have no documentation about the valid syntax. Iam running CUIC 8.0(1)
     
     
     
     

    Subject: RE: Custom Formatting
    Replied by: Raymond Lynn on 25-11-2011 11:10:47 AM
    The easiest method you can use is for the field that you need this percentage to be a whole number is to modify the output from the SQL Query to and just have the 2 digits accounted for.
    Then running a percentage etc will not matter in the format you want. I am not aware of standard way to do it if you have multiple digits and want to use percentage. You can use ROUND Function and its variants of FLOOR or CIELING to get the 2 digit values in the query.

     
    I was really hoping to do it in the formula field as the SQL is quite complicated already. Is there a document anywhere that describes the syntax that you can use in the Formula or is it just a case that you can only do simple mathematics?

    Subject: RE: New Message from Raymond Lynn in Contact Center Reporting (CCR) - Conta
    Replied by: Avinash Bhagwan on 25-11-2011 11:29:00 AM
    You can try by referencing the link below. It should not be bad since you need to round the output of the result.

    http://www.mssqltips.com/sqlservertip/1589/rounding-functions-in-sql-server/


    From: Cisco Developer Community Forums [mailto:cdicuser@developer.cisco.com]
    Sent: Friday, November 25, 2011 10:11 AM
    To: cdicuser@developer.cisco.com
    Subject: New Message from Raymond Lynn in Contact Center Reporting (CCR) - Contact Center Reporting: RE: Custom Formatting

    Raymond Lynn has created a new message in the forum "Contact Center Reporting":

    --------------------------------------------------------------
    The easiest method you can use is for the field that you need this percentage to be a whole number is to modify the output from the SQL Query to and just have the 2 digits accounted for.
    Then running a percentage etc will not matter in the format you want. I am not aware of standard way to do it if you have multiple digits and want to use percentage. You can use ROUND Function and its variants of FLOOR or CIELING to get the 2 digit values in the query.


    I was really hoping to do it in the formula field as the SQL is quite complicated already. Is there a document anywhere that describes the syntax that you can use in the Formula or is it just a case that you can only do simple mathematics?
    --
    To respond to this post, please click the following link:

    <http://developer.cisco.com/web/ccr/forums/-/message_boards/view_message/4801791>

    or simply reply to this email.

    Subject: RE: Custom Formatting
    Replied by: Avinash Bhagwan on 25-11-2011 09:03:29 AM
    The easiest method you can use is for the field that you need this percentage to be a whole number is to modify the output from the SQL Query to and just have the 2 digits accounted for.
    Then running a percentage etc will not matter in the format you want. I am not aware of standard way to do it if you have multiple digits and want to use percentage. You can use ROUND Function and its variants of FLOOR or CIELING to get the 2 digit values in the query.

    Subject: RE: Custom Formatting
    Replied by: Luis Yrigoyen on 28-11-2011 11:52:31 AM
    I was having sort of the same problem plus some "divide by zero" errors; below is what I did.
    Cast it as a float, multiply by 100 and then, with the LEFT function I only show 5 digits.  This works for my specific need, maybe you can do the same.
    This is from one of my report definitions.




    **************************************
    SET ARITHABORT OFF
    SET ANSI_WARNINGS OFF

    select

    CT.CallTypeID,--EnterpriseName,4) as DNIS,
    CT.EnterpriseName,
    HALF.ans8,
    HALF.ans30,
    HALF.ans100,
    HALF.ans130,
    HALF.ans200,
    HALF.ans300,
    HALF.ans500,
    HALF.ans1000,
    HALF.ans2000,
    HALF.Handle_Time,
    TotalCalls = (HALF.ans8 + HALF.ans30 + HALF.ans100 + HALF.ans130 + HALF.ans200 + HALF.ans300 + HALF.ans500 + HALF.ans1000 + HALF.ans2000),
    TotalLessThan30 = (HALF.ans8 + HALF.ans30),
    PerLess85 = LEFT(ISNULL(CAST((HALF.ans8 + HALF.ans30) AS FLOAT)*100 / (HALF.ans8 + HALF.ans30 + HALF.ans100 + HALF.ans130 + HALF.ans200 + HALF.ans300 + HALF.ans500 + HALF.ans1000 + HALF.ans2000),0),5) + '%'
     

    from
    Call_Type CT
    INNER JOIN
    (Select
      CallTypeID,
      Count(CallTypeID) as NumOfCalls,
      SUM(AnsInterval1) as 'ans8',
      SUM(AnsInterval2) as 'ans30',
      SUM(AnsInterval3) as 'ans100',
      SUM(AnsInterval4) as 'ans130',
      SUM(AnsInterval5) as 'ans200',
      SUM(AnsInterval6) as 'ans300',
      SUM(AnsInterval7) as 'ans500',
      SUM(AnsInterval8) as 'ans1000',
      SUM(AnsInterval9) as 'ans2000',
      SUM(HandleTimeHalf) as Handle_Time
    From Call_Type_Half_Hour
    where
      CallTypeID in ( :list_calltype )
      AND
             DateTime >= :start_date 
      AND 
             DateTime <= :end_date
       
    Group by CallTypeID) HALF
    on
    CT.CallTypeID = HALF.CallTypeID

    ORDER BY
    CT.EnterpriseName
    *****************************

    Subject: RE: Custom Formatting
    Replied by: Raymond Lynn on 07-12-2011 06:31:08 AM
    I was having sort of the same problem plus some "divide by zero" errors; below is what I did.
    Cast it as a float, multiply by 100 and then, with the LEFT function I only show 5 digits.  This works for my specific need, maybe you can do the same.
    This is from one of my report definitions.




    **************************************
    SET ARITHABORT OFF
    SET ANSI_WARNINGS OFF

    select

    CT.CallTypeID,--EnterpriseName,4) as DNIS,
    CT.EnterpriseName,
    HALF.ans8,
    HALF.ans30,
    HALF.ans100,
    HALF.ans130,
    HALF.ans200,
    HALF.ans300,
    HALF.ans500,
    HALF.ans1000,
    HALF.ans2000,
    HALF.Handle_Time,
    TotalCalls = (HALF.ans8 + HALF.ans30 + HALF.ans100 + HALF.ans130 + HALF.ans200 + HALF.ans300 + HALF.ans500 + HALF.ans1000 + HALF.ans2000),
    TotalLessThan30 = (HALF.ans8 + HALF.ans30),
    PerLess85 = LEFT(ISNULL(CAST((HALF.ans8 + HALF.ans30) AS FLOAT)*100 / (HALF.ans8 + HALF.ans30 + HALF.ans100 + HALF.ans130 + HALF.ans200 + HALF.ans300 + HALF.ans500 + HALF.ans1000 + HALF.ans2000),0),5) + '%'
     

    from
    Call_Type CT
    INNER JOIN
    (Select
      CallTypeID,
      Count(CallTypeID) as NumOfCalls,
      SUM(AnsInterval1) as 'ans8',
      SUM(AnsInterval2) as 'ans30',
      SUM(AnsInterval3) as 'ans100',
      SUM(AnsInterval4) as 'ans130',
      SUM(AnsInterval5) as 'ans200',
      SUM(AnsInterval6) as 'ans300',
      SUM(AnsInterval7) as 'ans500',
      SUM(AnsInterval8) as 'ans1000',
      SUM(AnsInterval9) as 'ans2000',
      SUM(HandleTimeHalf) as Handle_Time
    From Call_Type_Half_Hour
    where
      CallTypeID in ( :list_calltype )
      AND
             DateTime >= :start_date 
      AND 
             DateTime <= :end_date
       
    Group by CallTypeID) HALF
    on
    CT.CallTypeID = HALF.CallTypeID

    ORDER BY
    CT.EnterpriseName
    *****************************


     
    Yes this what I have had to do. It's just a shame that you cannot use a Custom Format to do it automatically. Does Custom Format even work? Thanks for your help

    Subject: RE: Custom Formatting
    Replied by: P_ M_ on 10-01-2012 08:51:11 AM
    I want to display a field as a two/three digit percentage with no digits after the decimal point but am unable to work out the required Custom Format
     
    If I use the standard format
     
    12,345.68% it displays 100.00% but I want 100% For values less than 100% I would like to display 99%, 45% etc
     
    I have also tried to do this in a Formula field but have no documentation about the valid syntax. Iam running CUIC 8.0(1)
     
     
     
     


    So this is how I do it in 7.5.x. Steps  might be slightly different in 8.x. Go into field map, field properties, choose formatting tab, choose custom and in the Custom Format String enter ###0%. The zero is imporant as CUIS displays 100% as 0% without it. 

    Subject: RE: Custom Formatting
    Replied by: P_ M_ on 10-01-2012 08:53:15 AM
    So this is how I do it in 7.5.x. Steps  might be slightly different in 8.x. Go into field map, field properties, choose formatting tab, choose custom and in the Custom Format String enter ###0%. The zero is imporant as CUIS displays 100% as 0% without it.

    Subject: RE: Custom Formatting
    Replied by: Raymond Lynn on 10-01-2012 12:24:52 PM
    Thanks for getting back to me. I think that they must have changed something in 8.x as i tried ###0% and it did not work. No matter what I put in the Custom Format I get the following

    100.0 instead of 100%
    66.66666666666667 instead of 66%
    0.0 instead of 0%

    even if I copy the standard format 12,345.68% I get the above.