outbound report templates in CUIC

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: David Goyette on 29-10-2010 03:35:53 PM
Hi, I was wondering if someone knows when CUIC outbound report templates will be available.
 
Thanks!

Subject: RE: outbound report templates in CUIC
Replied by: Paige Delk on 12-11-2010 12:46:42 PM
Hi, I was wondering if someone knows when CUIC outbound report templates will be available.
 
Thanks!

Checking on availability of these in a product release - in the meantime, the SQL behind all of the 7.5 Webview reports can be found here:
http://developer.cisco.com/web/ccr/docs?p_p_id=20&p_p_lifecycle=0&p_p_state=maximized&p_p_mode=view&p_p_col_id=column-1&p_p_col_count=3&_20_struts_action=%2Fdocument_library%2Fview&_20_folderId=2699722
 
You can cut/paste the SQL into CUIC and use with minor modification.  You'll want to look for retrieve=" in the files and copy the entire SQL statement (after the quote mark and down to the last SQL statement, which is generally an Order By Clause).  Remember to modify the table and column names to remove the 'ToHalf' suffix.  Check the 8.0 schema guide for more information.

 
Some statements from the WHERE clause (in red below) need to be removed.  You'll need to create a Value List and collections (if necessary) for the campagins then assign that list to the Campaign_Query_Half_Hour.CampaignID and identify the datecol as the historical date field when creating the report definition in CUIC.
 
retrieve="  SELECT Campaign.CampaignName,  
        datecol=convert( datetime, convert( varchar(10), CQRHH.DateTime, 112 )), 
        customer_answered= sum( isnull(CQRHH.VoiceDetectToHalf,0)) +  sum( isnull(CQRHH.WrongNumberCountToHalf,0)) + sum( isnull(CQRHH.CustomerNotHomeCountToHalf,0)) +  sum( isnull(CQRHH.CustomerAbandonDetectToHalf ,0)) + sum( isnull(CQRHH.CallbackCountToHalf,0)) +  sum( isnull(CQRHH.PersonalCallbackCountToHalf,0))+ sum(isnull(CQRHH.AbandonDetectToHalf,0)) + sum(isnull(CQRHH.AbandonToIVRToHalf,0)),
        customer_not_answered= sum( isnull(CQRHH.AnsweringMachineDetectToHalf,0)) + sum( isnull(CQRHH.BusyDetectToHalf,0))  +sum( isnull(CQRHH.NoAnswerDetectToHalf,0)) + sum(isnull(CQRHH.CancelledDetectToHalf,0)),
        problem= sum( isnull(CQRHH.FaxDetectToHalf,0)) + sum( isnull(CQRHH.NoDialToneDetectToHalf,0))+ sum( isnull(CQRHH.NoRingBackDetectToHalf ,0))+ sum( isnull(CQRHH.SITToneDetectToHalf,0)),
        didnotdial= sum( isnull(CQRHH.AgentClosedDetectToHalf,0)) + sum( isnull(CQRHH.AgentRejectedDetectToHalf,0)),
         Voice =sum( isnull(CQRHH.VoiceDetectToHalf,0)),  
         Busy = sum(isnull(CQRHH.BusyDetectToHalf,0)),  
         No_Answer =sum( isnull(CQRHH.NoAnswerDetectToHalf,0)),  
         No_Ringback = sum(isnull(CQRHH.NoRingBackDetectToHalf,0)),  
         No_Dialtone = sum(isnull(CQRHH.NoDialToneDetectToHalf,0)),  
         Fax =sum( isnull(CQRHH.FaxDetectToHalf,0)),  
         Network_IVR = sum(isnull(CQRHH.NetworkAnsMachineDetectToHalf,0)),  
         Answering_Machine =sum( isnull(CQRHH.AnsweringMachineDetectToHalf,0)),  
         SIT_Tone = sum(isnull(CQRHH.SITToneDetectToHalf,0)),  
         Agent_Rejected =sum( isnull(CQRHH.AgentRejectedDetectToHalf,0)),  
         Agent_Closed = sum(isnull(CQRHH.AgentClosedDetectToHalf,0)),  
         Canceled = sum(isnull(CQRHH.CancelledDetectToHalf,0)),  
        wrongnumber= sum(isnull(CQRHH.WrongNumberCountToHalf,0)),
        customer_not_home = sum(isnull(CQRHH.CustomerNotHomeCountToHalf,0)),
       Callback = sum(isnull(CQRHH.CallbackCountToHalf,0)), 
    personal_callback= sum(isnull(CQRHH.PersonalCallbackCountToHalf,0)),
         DialerAband = sum(isnull(CQRHH.AbandonDetectToHalf,0)),  
         Abandon_To_IVR = sum(isnull(CQRHH.AbandonToIVRToHalf,0)),  
         Customer_Abandon = sum(isnull(CQRHH.CustomerAbandonDetectToHalf,0)),  
         HitRate = ISNULL((sum( isnull(CQRHH.VoiceDetectToHalf,0)) + sum( isnull(CQRHH.WrongNumberCountToHalf,0)) + sum( isnull(CQRHH.CustomerNotHomeCountToHalf,0)) +  sum( isnull(CQRHH.CustomerAbandonDetectToHalf ,0)) + sum( isnull(CQRHH.CallbackCountToHalf,0)) +  sum( isnull(CQRHH.PersonalCallbackCountToHalf,0)) + sum(isnull(CQRHH.AbandonDetectToHalf,0)) + sum(isnull(CQRHH.AbandonToIVRToHalf,0)) + sum(isnull(CQRHH.AbandonDetectToHalf,0)) + sum(isnull(CQRHH.AbandonToIVRToHalf,0)) ) *1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),
        PerAband= ISNULL((sum(isnull(CQRHH.AbandonDetectToHalf,0)) + sum(isnull(CQRHH.AbandonToIVRToHalf,0))) *1.0 / (sum( isnull(CQRHH.VoiceDetectToHalf,0)) +  sum( isnull(CQRHH.WrongNumberCountToHalf,0)) + sum( isnull(CQRHH.CustomerNotHomeCountToHalf,0)) +  sum( isnull(CQRHH.CustomerAbandonDetectToHalf ,0)) + sum( isnull(CQRHH.CallbackCountToHalf,0)) +  sum( isnull(CQRHH.PersonalCallbackCountToHalf,0))+ sum(isnull(CQRHH.AbandonDetectToHalf,0)) + sum(isnull(CQRHH.AbandonToIVRToHalf,0)) ),0),
        
     perVoice =ISNULL(sum( isnull(CQRHH.VoiceDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)), 0),
        perBusy = ISNULL(sum(isnull(CQRHH.BusyDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),
        perNo_Answer =ISNULL(sum( isnull(CQRHH.NoAnswerDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),  
                perNo_Dialtone = ISNULL(sum(isnull(CQRHH.NoDialToneDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)), 0), 
         perFax = ISNULL(sum( isnull(CQRHH.FaxDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)), 0), 
          perNetwork_IVR = ISNULL(sum(isnull(CQRHH.NoRingBackDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),  
          perAMD =ISNULL(sum( isnull(CQRHH.AnsweringMachineDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),  
         perSIT_Tone = ISNULL(sum(isnull(CQRHH.SITToneDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0), 
          perCanceled = ISNULL(sum(isnull(CQRHH.CancelledDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),  
         perwrongnumber= ISNULL(sum(isnull(CQRHH.WrongNumberCountToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),
         percustomer_not_home = ISNULL(sum(isnull(CQRHH.CustomerNotHomeCountToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),
         perCallback = ISNULL(sum(isnull(CQRHH.CallbackCountToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),
         perpersonal_callback= ISNULL(sum(isnull(CQRHH.PersonalCallbackCountToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),
         perDialerAban = ISNULL(sum(isnull(CQRHH.AbandonDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)), 0), 
          perAbandon_To_IVR = ISNULL(sum(isnull(CQRHH.AbandonToIVRToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),
         perCustomer_Abandon = ISNULL(sum(isnull(CQRHH.CustomerAbandonDetectToHalf,0))*1.0 / SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0)),0),
         Attempts =  SUM(ISNULL(CQRHH.ContactsAttemptedToHalf,0))

    FROM Campaign,  
         Campaign_Query_Rule_Half_Hour CQRHH
   WHERE ( Campaign.CampaignID = CQRHH.CampaignID ) and 
         ( ( CQRHH.CampaignID in ( :list_campaign ) ) AND 
         ( CQRHH.DateTime >= :start_date ) AND 
         ( CQRHH.DateTime <= :end_date ) ) 

Group BY Campaign.CampaignName, convert( datetime, convert( varchar(10), CQRHH.DateTime, 112 ))
    ORDER BY Campaign.CampaignName, convert( datetime, convert( varchar(10), CQRHH.DateTime, 112 ))
 
 
Value List SQL:
SELECT CampaignID as ID, EnterpriseName as VALUE FROM Campaign

Subject: RE: outbound report templates in CUIC
Replied by: Touchbase Support on 24-06-2011 05:34:48 PM
Do the webview sql templates contain the summaries as well?

Subject: RE: outbound report templates in CUIC
Replied by: Luis Yrigoyen on 12-09-2011 06:11:07 PM
Check this post.  I posted some outbound templates.
http://developer.cisco.com/web/ccr/forums/-/message_boards/view_message/4257067

Attachments

    Outcomes