outbound report templates in CUIC

Version 1
    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