Contact Centers, CRM Customer Engagement

 View Only
  • 1.  Extracting CMS Reports using VBA

    Posted 02-15-2024 07:56 AM

    Hey everyone. I'm new to Avaya CMS Supervisor. I'll explain my scenario here. I have 3 reports that I do daily with an interval of 1 hour. Is there a way to automate the extraction of those reports to CSV format and save them to a folder (override the file)? I can link that Excel file to the reporting one with formulas to extract what's needed.
    I'm using 3 interactive scripts (If I use automatic scripts, It asks for a location every time to save in the PDF format). 

    Any help would be appreciated. :)

    Shaharyar Zulfiqar Ali
    Intrum Justitia

  • 2.  RE: Extracting CMS Reports using VBA

    Posted 02-17-2024 06:58 AM

    Hello!  We weren't able to figure out a good way to do this and ultimately ended up working with our data warehouse team to extract CMS data into our data warehouse.  We now use Tableau or PowerBI for our historical reporting.  This also allowed us to keep our interval data longer and get better trending.


    Desirae Allen

    Manager, IT – Contact Centers


  • 3.  RE: Extracting CMS Reports using VBA

    Posted 02-20-2024 09:30 AM
      |   view attached
      1. Do you have the Designer Report option: In CMS, Commands-Reports-Historical then scroll down below Vector. Do you see 'Designer'. If you have Designer Reports, you can report whatever you want. For example, multiple skills or multiple agents.
      2. You said report 'hourly'. Do you mean hourly-interval reports. Or are you trying to run a report every hour?
      3. You are trying to run three reports. When you create the acsauto file and go to save it, CMS presents an option to overwrite an existing file with the same name or append to the existing one. Appending effectively chains the two (or more) scripts together so you can run multiple reports in one request,
      4. You can select comma as the field separator (for csv format). 
      5. I uploaded a file re #3.
      6. Are you good with Excel Pivot Tables and/or Excel macros? If you are, you will have fun with Designer Reports.
      7. You can edit the acsauto to form a file name. You need to write a batch command to put this in the Windows Task Scheduler:
      Typical cmd file:
      rem Runs ob_sp_ski_all_datav5 for date = -1. This is format power bi uses. 1/2/2024
      cd C:\mmc\cms1\WHScripts
      Modified ACSAUTO example:
      'Start with acsauto from your system
      ' (your IP)
      Public Sub Main()
      '## Your parameters from an acsauto you created
         On Error Resume Next
      'Edit with Notepad. Insert stuff to create date named file
      'Path for exported file
      'Minus 1 is yesterday (dy)
      dy=-1 'for yesterday
      if len(mm)=1 then mm="0" & mm
      if len(dd)=1 then dd="0" & dd
      file1=dpath1 & "spsk_1-99_" & yyyy & mm & dd & ".txt" 
         cvsSrv.Reports.ACD = 1
         Set Info = cvsSrv.Reports.Reports("Historical\Designer\ob_sp_ski_all_datav5")
         If Info Is Nothing Then
        If cvsSrv.Interactive Then
        MsgBox "The report Historical\Designer\ob_sp_ski_all_datav5 was not found on ACD 1.", vbCritical Or vbOKOnly, "Avaya CMS Supervisor"
            Set Log = CreateObject("ACSERR.cvsLog") 
        Log.AutoLogWrite "The report Historical\Designer\ob_sp_ski_all_datav5 was not found on ACD 1."
        Set Log = Nothing
        End If
         b = cvsSrv.Reports.CreateReport(Info,Rep)
         If b Then
            Rep.Window.Top = -60
            Rep.Window.Left = -60
            Rep.Window.Width = 20580
            Rep.Window.Height = 11220        
                              Rep.TimeZone = "default"
      ' Our Designer Report gets data for skills 1-99
      'This script uses variables instead of fixed values for Dates, Skills & file1. 
      'file1: True, True, True indicates settings for Export Null Values; Include Labels & Headers & Export Time as seconds
            Rep.SetProperty "Dates", dy  
            Rep.SetProperty "Splits/Skills","1-99"
            Rep.SetProperty "Times","00:00-23:30"
            Rep.ReportView.Add "G0,0,0;0,2,0","Grid1"
            b = Rep.ExportData(file1, 9, 0, True, True, True)
                    If Not cvsSrv.Interactive Then cvsSrv.ActiveTasks.Remove Rep.TaskID
            Set Rep = Nothing
         End If
         End If
         Set Info = Nothing
      '## cvs_cmd_end
      End Sub
      Good luck,
      John O'Brien

      John O'Brien
      Self Employed Consultant/Engineer
      Staten Island NY


    1. 4.  RE: Extracting CMS Reports using VBA

      Posted 02-23-2024 09:06 AM


      Thank you so much for this. I understand it much better now. 

      Just one more off topic question - IS there a way/a report to find the calls abandoned from AVAYA CMS? Unique calls abandoned for a certain time period. For example, someone calls multiple times in a certain period but it counts it as unique. Is there an identifier for that?

      Shaharyar Zulfiqar Ali
      Intrum Justitia

    2. 5.  RE: Extracting CMS Reports using VBA

      Posted 02-23-2024 11:51 AM
        |   view attached
      My reply keeps on getting wiped out.

      Try Commands-Historical-Other-Call Records. This report extracts call
      records for the period start-date & start time TO stop-Date &
      Stop-Time IF THE CALL_REC table has not yet been overwritten, You
      would then need to search (or order by) Disposition. This report gives
      disposition by name 'ABAN'. The call_rec table actually uses a
      number. See the attached Excel re the few fields in this report vs.
      about 97 fields in the call_rec table. you should research the
      contents of the call_rec table to understand how the multiple records
      comprising a call link together.

      Google Avaya CMS Call Records Report Description for info on fields
      in the above report.

      See attached Excel for more info.

      Again, you can do much better if you have and use Designer Reports or ODBC.



      cms_call_rec_fields.xlsx   12 KB 1 version