Extracting Data from the Default Call History

Many applications will require mining data from the Call History, for example:

• Management summary reports on call center activity.
• Aggregating data collected during polling/surveying for analysis.
• Checking completion status of outbound calls.
• Automatically extracting information collected during inbound calls.

Although Active Call Center does not provide the means for performing these operations, it does allow the user to perform these functions outside of Active Call Center using database software like Microsoft Access.

The discussion in this section is for users that are using the default Call History. If you have customized the Call History to use a different format, you will most likely not be able to use the techniques exactly as described to extract data.

Structure of the Call History Database

The default Active Call Center Call History is saved as a Microsoft Access 2000 database in the file Phone Call Log.mdb (located in the Active Call Center program folder). There are two tables in the Phone Call Log database:

• The "Calls" table, which is a sequential list of all processed calls. There are three fields in this table: CallID, Date, and Folder.
• The "CallData" table, which stores all the call-specific data. Fields in the CallData table are CallID, Description, Value, and Audio.

A one-to-many relationship exists between records of the Calls table and the CallData table through the CallID field.

Linking to the Call History

Since the default Call History is a Microsoft Access 2000 database, any software that can read the Access 2000 MDB database formats can also read the default Call History. Although the Call History may be accessed directly, we recommend indirect access to reduce the chances of accidentally altering its file format.

Use one of the following techniques to indirectly access the Call History:

• Create a copy of the Call History file (Phone Call Log.mdb) and manipulate the copy. This is a short-term solution.

Or

• Create a database in which the Call History tables are linked into the new database. This is a better solution since the linked database updates automatically as the Call History is updated.

Create a database with linked tables in Microsoft Access 2000/2002 as follows:

Start Microsoft Access.

From the Access menu bar choose File then click New. Create a new database.

Link the Call History tables by choosing File ... Get External Data ... Link Tables.

When prompted for the "Link" file, select Phone Call Log.mdb from the Active Call Center program folder.

On the Link Tables dialog, click Select All and then click OK. Two linked tables should appear in the database: CallData and Calls.

The linked database can now be used to create reports, queries, and other custom functions without disturbing the format of the original Call History database.

Building Reports and Queries

Build Call History reports or queries externally in a database application like Microsoft Access. A nearly infinite variety of queries and reports can be created to satisfy virtually every requirement.

One commonly used query shows one phone call per line with the data fields running across in columns in a tabular format. Create such a query in Microsoft Access 2000/2002 by following these steps:

Start Microsoft Access and open the linked database created above.

From the Access menu bar, choose Insert ... Query.

On the New Query dialog box, select Design View and click OK.

A new blank query should now be on the screen. When the Show Table dialog appears, click Close.

From the Access menu bar, choose View ... SQL View.

A box titled "Query1: Select Query" or similar with "SELECT;" printed in the box will appear. Erase what's in this box and replace it with with the text below:

TRANSFORM First(CallData.Value) AS FirstOfValue
SELECT Calls.CallID, Calls.Date, Calls.Folder
FROM CallData INNER JOIN Calls
ON CallData.CallID = Calls.CallID
GROUP BY Calls.CallID, Calls.Date, Calls.Folder
PIVOT CallData.Description;

From the Access menu bar, choose Query ... Run.

After viewing the query, save it or modify it as required.