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.
- NOTE: Your Callback reporting database may be named differently.
- Copy and paste the SQL code below into the query window, then execute the query.
- Create a new query on the VHT_RPT database.
- 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".
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