Migrating webview to CUIC

Version 1
    This document was generated from CDN thread

    Created by: Amer rajai Sha'er on 14-07-2012 02:50:25 PM
    Hello,
     
    Everyone
    is running around with this problem since cisco stops the webview , the CUIC is a great reporting solution but the only thing CUIC  is weak
    with is the stock reports , not only they don't include all the  reports mostly needed but they can only be found in the half hour  intervals
    (no consolidated reports)
    After months of working and developing i finally found out a way to migrate the webview reports to CUIC in a
    database query (not only anonymous block) and here are the detailed steps:
    1- If you have a webview server (version 8.0 preferred or earlier) go to  drive c: then folder icm\custom
    2- Under the custom you will find folders for each type of reports (agent  per team , agent per agent, calltype , skill group , etc.....)
    3- Choose whatever report you want to migrate to CUIC (in this scenario we will choose the calltype22 report)
    4- The file that is located under the caltyp folder is name caltyp22.srd
    5- open the file using notepad
    6-when you open the file you will find along list of data , you should ignore most of it , just search for (retrieve=") , after that you will
    find a SELECT , copy all the data from there until you reach (" arguments=) (of course don't copy the " arguments=)
    7- paste the copied data into a new notepad , search for all the values (112) (it is usually beside the Datetime parameter) replace it with 101
    8- delete everything that has the : like this
    Call_Type.CallTypeID in ( :list_calltype ) AND
    CTHH.DateTime >= :start_date  AND
     CTHH.DateTime <= :end_date
    Any sql command that has the : remove it completely.
    9- Copy the updated data and create a new report definition
    10- use database query and historical datasource (because the report we are doing now is historical , if you decided to migrate a realtime use the
    real-time datasource)
    11- paste the updated notepad file into query .
    12-at the end of the page there is a create fields button, click on it and the report should be verified correctly and the fields are created.
    13- after that go the next tab which is the fields , search for calltype (in other reports you may  need the agent team , agent, skill group,
    etc... search for the ID value not the value it self) , click on the edit properties , below beside the value list choose the call types from
    the drop down menu and click on update.
    14- go to the last tab which is the properties , choose the Key Criteria Field to be the call type (call type) and click on the historical checkbox below it , choose from Historical Key Field the Datetime value then click on save at the top of the report definition.

    Now your report definition is ready , all what you have to do is to go and create a new report and assign this report definition to it , be aware
    that in case of creating a new report , all the fields will be visible so go to the edit view and edit the report with only the need values for you.

    I forgot to add that the above way is only possible with a CUIC premium version (standard won't have this option)
    also the data for the times (like average handle and ASA) would be in normall decimal (like 425) so all what you have to do is to go to the
    report definition , choose the fields tab , search for asa , click on edit formating , in the fromat field choose time (HH:MM:SS) value , also
    for the SL if you need , you may also change it to the % in the same way.
    One last thing , if you need a footer (which is the total, average or max value) in the summary raw , edit the footer in the same place under the format to the needed type of footer.
     
    Hope this helps.
     
    Amer Sha'er

    Subject: RE: Migrating webview to CUIC
    Replied by: Amer rajai Sha'er on 03-08-2012 01:20:31 AM
    Hello Greg,

    Try this :

    SELECT fname=Skill_Group.EnterpriseName,
    mrd = Media_Routing_Domain.EnterpriseName,
    fdate=convert(datetime, convert( varchar(10), Skill_Group_Half_Hour.DateTime , 101 ) ),
    ftime=Skill_Group_Half_Hour.DateTime,
    fte_signon=(ISNULL(Skill_Group_Half_Hour.LoggedOnTimeToHalf,0)) * 1.0 / 1800,
    fte_idle=(ISNULL(Skill_Group_Half_Hour.NotReadyTimeToHalf,0)) * 1.0 / 1800,
    fte_avail=(ISNULL(Skill_Group_Half_Hour.AvailTimeToHalf,0)) * 1.0 / 1800,
    fte_talking=(ISNULL(Skill_Group_Half_Hour.TalkTimeToHalf,0)) * 1.0 / 1800,
    fte_other=(ISNULL(Skill_Group_Half_Hour.BusyOtherTimeToHalf,0)) * 1.0 / 1800,
    p_start=convert(datetime, :start_datetime),
    p_end=convert(datetime, :stop_datetime),
    fte_wrapup=(ISNULL((Skill_Group_Half_Hour.WorkReadyTimeToHalf +Skill_Group_Half_Hour.WorkNotReadyTimeToHalf ),0)) * 1.0 / 1800,
    fte_hold=(ISNULL(Skill_Group_Half_Hour.HoldTimeToHalf,0)) * 1.0 / 1800,
    fte_reserve=(ISNULL(Skill_Group_Half_Hour.ReservedStateTimeToHalf,0)) * 1.0 / 1800,
    fte_interrupted = (ISNULL(Skill_Group_Half_Hour.InterruptedTimeToHalf,0)) * 1.0 / 1800
    FROM Skill_Group,
    Skill_Group_Half_Hour, Media_Routing_Domain
    WHERE ( Skill_Group.SkillTargetID = Skill_Group_Half_Hour.SkillTargetID ) and
    (Media_Routing_Domain.MRDomainID = Skill_Group.MRDomainID)
    ORDER BY Media_Routing_Domain.EnterpriseName ASC,
    Skill_Group.EnterpriseName ASC,
    Skill_Group_Half_Hour.DateTime ASC

    Amer

    Subject: RE: Migrating webview to CUIC
    Replied by: GREG GREBIN on 02-08-2012 05:12:27 PM
    I was able to use some of these steps and migrate WebView reports to CUIC V8.5.2 using Anomynous block. I could use the complete qry from "retrieve" to "Arguments" without removing the sections that contained a " : "

    Step 8 needs a little more explaining. I was unable to get the dates and calltypes to work using this method with a "database" query report.

    Subject: RE: Migrating webview to CUIC
    Replied by: Amer rajai Sha'er on 02-08-2012 05:58:05 PM
    Hello,

    It should work exactly as i post in the beginning , can you please share what is the report that you were trying to migrate ?

    Amer

    Subject: RE: Migrating webview to CUIC
    Replied by: GREG GREBIN on 02-08-2012 06:15:15 PM
    The WebView report I had trouble migrating to CUIC was perskg08. Here is the origninal SQL from the Webview report, please post what you would put in the CUIC Database Qry. Thanks-

    SELECT fname=Skill_Group.EnterpriseName,
       mrd = Media_Routing_Domain.EnterpriseName,  
             fdate=convert(datetime, convert( varchar(10), Skill_Group_Half_Hour.DateTime , 112 ) ),  
             ftime=Skill_Group_Half_Hour.DateTime,  
             fte_signon=(ISNULL(Skill_Group_Half_Hour.LoggedOnTimeToHalf,0)) * 1.0 / 1800,  
             fte_idle=(ISNULL(Skill_Group_Half_Hour.NotReadyTimeToHalf,0)) * 1.0 / 1800,  
             fte_avail=(ISNULL(Skill_Group_Half_Hour.AvailTimeToHalf,0)) * 1.0 / 1800,  
             fte_talking=(ISNULL(Skill_Group_Half_Hour.TalkTimeToHalf,0)) * 1.0 / 1800,  
             fte_other=(ISNULL(Skill_Group_Half_Hour.BusyOtherTimeToHalf,0)) * 1.0 / 1800,  
             p_start=convert(datetime, :start_datetime),  
             p_end=convert(datetime, :stop_datetime),  
             fte_wrapup=(ISNULL((Skill_Group_Half_Hour.WorkReadyTimeToHalf +Skill_Group_Half_Hour.WorkNotReadyTimeToHalf ),0)) * 1.0 / 1800,  
             fte_hold=(ISNULL(Skill_Group_Half_Hour.HoldTimeToHalf,0)) * 1.0 / 1800,  
             fte_reserve=(ISNULL(Skill_Group_Half_Hour.ReservedStateTimeToHalf,0)) * 1.0 / 1800,   
       fte_interrupted = (ISNULL(Skill_Group_Half_Hour.InterruptedTimeToHalf,0)) * 1.0 / 1800   
        FROM Skill_Group,  
             Skill_Group_Half_Hour, Media_Routing_Domain 
       WHERE ( Skill_Group.SkillTargetID = Skill_Group_Half_Hour.SkillTargetID ) and 
             ( ( Skill_Group.SkillTargetID in ( :perskg_list ) ) AND 
             ( Skill_Group_Half_Hour.DateTime >= :start_datetime ) AND 
       (Media_Routing_Domain.MRDomainID = Skill_Group.MRDomainID) AND
             ( Skill_Group_Half_Hour.DateTime < :stop_datetime ) )  
    ORDER BY Media_Routing_Domain.EnterpriseName ASC,  
             Skill_Group.EnterpriseName ASC,  
             Skill_Group_Half_Hour.DateTime ASC

    Subject: RE: Migrating webview to CUIC
    Replied by: Isidro Fernandez on 07-08-2012 04:17:53 AM
    Hi Amer, Everyone,
     
    Would you be able to create agent22 through this method and attach it to the forum? The issue I'm finding on the ones already available is that the report doesn't show the totals for each agent, just the totals per skillgroup and for all the agents together. I haven't got the Premium license yet so I cannot test it myself, this small issue is stopping as to go to 8.5 and get rid of Webview.
     
    Thanks in advance
     
    Isidro