Custom Formatting

Document created by cdnadmin on Jan 24, 2014
Version 1Show Document
  • View in full screen mode
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.

Attachments

    Outcomes