Voice-to-messaging reporting

SQL views allow you to store commonly used queries for quick access. The VoiceToMessagingDetails and VoiceToMessagingSummary views pull together data related to Voice-to-Messaging conversations to provide an overview of how the feature is being used.

These views can be executed at any time for up-to-date information, or you can add a date/time filter to pull data for a specific timeframe. Output from the views can be displayed in your database management system (DBMS), saved to file, or used by a database administrator to build custom reports.

Creating and running the views

The steps below assume you are using SQL Server Management Studio as your DBMS.

Option 1 - Manually create the views
- Create a new query on the VHT_RPT database.
  1. NOTE: Your Callback reporting database may be named differently.
- Copy and paste the SQL code below into the query window, then execute the query.
Option 2 - Import the views
- Save a copy of the SQL script below on the database server.
- Open the script with your DBMS, then execute it.
Open the Views folder nested beneath the VHT_RPT database in the Object Explorer.
Locate the view you wish to run, right-click it in the list, then click "Select All Rows" or "Select Top X Rows".

Note: If your Callback reporting database is not named "VHT_RPT", you can add a USE statement directly above the SELECT statement to ensure the views are executed on the correct database.


View Name	Description
VoiceToMessagingDetails	A breakdown of all voice interactions in which messaging was offered. Displays the queue, segment, CallID, and dates/times of call progress events. Also shows whether the messaging offer was accepted and whether a messaging error occurred.
VoiceToMessagingSummary	An overview of the total number of messaging offers presented, grouped by queue and date. Compares the total number of offers to the number of accepted offers and messaging errors.

VoiceToMessagingDetails

CREATE VIEW dbo.VoiceToMessagingDetails AS 
    SELECT
        SiteName,
        QueueGroup,
        QueueID,
        ApplicationId AS 'SegmentName',
        CALLID,
        SourceApplication,
        OpMode,
        CASE WHEN FinalFate IN ('Q7') THEN 1 ELSE 0 END AS 'ChoseMessaging',
        CASE WHEN History LIKE '%I44%' THEN 1 ELSE 0 END AS 'ChoseMessagingError',
        FinalFateTime,
        InTimeStart,
        InTimeFinish,
        InitialCBTime,
        FinalDisconnectTime,
        AgentAnswerTime,
        RouteToQTime
    FROM dbo.QDump
    WHERE SourceApplication IN ('VXML Interaction Server') AND FinalFate NOT IN ('Q8');
GO

VoiceToMessagingSummary

CREATE VIEW dbo.VoiceToMessagingSummary AS 
    SELECT 
        SiteName,
        QueueGroup,
        QueueID,
        COUNT(CALLID) AS 'TotalVoiceToMessagingOffers',
        SUM(ChoseMessaging) AS 'ChoseMessaging',
        SUM(ChoseMessagingError) AS 'ChoseMessagingError',
        YEAR(FinalFateTime) AS 'Year',
        MONTH(FinalFateTime) AS 'Month',
        DAY(FinalFateTime) AS 'Day'
    FROM VoiceToMessagingDetails
    GROUP BY SiteName, QueueGroup, QueueID, YEAR(FinalFateTime), MONTH(FinalFateTime), DAY(FinalFateTime);
GO