Creation of report forms for CUCM

In this article we consider examples of creating report forms using Tariscope Report Designer (hereinafter - Report Designer) for advanced parameters of CDR format from the Cisco Unified Communications Manager (CUCM). Under the advanced parameters of the CDR format from CUCM we mean those, which are not used for the purpose of the call rating. They usually are not displayed into the Tariscope view, but they are kept in the original CDR files, and you can configure Tariscope that it will process and store all CDR fields of CUCM in the Tariscope database. Information from the advanced fields can be useful to perform a variety of analyses that are different from the analysis of the costs of telephone calls. Also, this article can be useful is as an example for self-creation of the necessary report forms in the Tariscope.

Note that in addition to the Report Designer to create new reports for Tariscope you can use Microsoft Report Builder.

A few words about the Report Designer

The Report Designer is a program, which is a part of the Tariscope billing system. It is designed to modify report forms included in the installation package by the Tariscope user, or create own report forms, charts, or procedures working with the database.

The report form is a template containing the structure in the form of table, in which each of the cells can contain a text, pictures, variables, or functions. Each cell can have its own format options: text font, background color, borders. In the capacity of variables can be factors or conditions created by user, specific fields from the Tariscope database or results of processing these fields.

The report form consists of SQL queries and commands of Visual Basic Script (VBScript) with a few additional features designed specifically for this purpose. Therefore, a user who knows SQL and VBScript can edit an existing report form or create your own with any text editor. However, even for a professional programmer in VBScript such a work can cause significant difficulties, not to mention the majority of Tariscope users who are not familiar with SQL and VBScript. In particular with purpose to facilitating the work when editing or creating new forms of reports, the Report Designer was developed.

Nevertheless, it should be noted that to work with the Report Designer, users must understand the structure of the Tariscope database and how to create SQL queries to databases.

The files of report forms have the ABR extension. They are stored in the following folder: \ Program Files\SoftPI\TariScope3\Reports\

Report on the call termination causes

So let us assume that we are interested in call termination causes. There are the following causes: normal call clearing, subscriber absent, no answer from user, etc. In fact, information on the call termination causes is contained in the appropriate column of a call view. And if you apply the grouping feature to this field you can get summary information about the causes. But let try to create a special report using the Report Designer.

What do you need?

Firstly, we must accurately imagine what we want to get. For example, we want to have a table containing the names of the causes of call terminations, their codes (the codes are missing in the Tariscope views), and the corresponding number of calls. And we want to have the information for which time period the data are processed, and finally, the date the report generation.

Where to get this information?

For this, open the "Tariscope. Database schema." document. The Section 2 of this document contains a list of all the tables and views of the Tariscope database. After reviewing this list, we find that the CallsCCM table contains detailed information about the calls. Next, go to the description of this table (Section 2.13). In it we find that the code of call termination is contained in the origCause_value. Remember this name and the name of the table. Other fields in this table for this report are not interested.

Start the Report Designer. In the appeared programm window, in the File menu, select New. The program window is displayed as shown in Figure 1.

new report
Figure 1

In the left part of the window is displayed a report form. In the right part of the window (Report manifest) are displayed main parameters of the report form. We begin to create the form report from the last part.

In the Name box we enter a report name: Call termination cause codes of CUCM

Not to be confused with the file name of the report form.

In the Description box we enter a brief description of the report form. It will be displayed in the Reports mode of the Tariscope program.

The Author and Version boxes are optional.

The Link box is used to enter a link to open the website page where you can find the detailed description of the report form. If this report form can be used by different collaborators of your company, you can create the appropriate description on the your company site. In this case, specify the link to the page.

In the Create date box the current date is displayed by default.

In the Visibility list, leave the default value: Always.

The completed report manifest is shown in Figure 2.

declaration
Figure 2

To save the entered data we select the menu File → Save As. A window opens to save the file, which will reflect the contents of the Report.

Save our entries. We select in the program menu: File → Save as. The Save As window opens where a list of the Reports folder is displayed. We select the required subfolder and save the file.

Now, we set the cursor on the border between the report form and the report manifest, press the left mouse button and hide the right side of the window.

Next we slightly modify the general parameters of the report form. To do this, select Report → Report properties. The Report properties dialog box appears (Figure 3).

properties Figure 3

We change the default font settings. These settings will be applied to each cell in the table of the report form by default. If necessary, each cell can have its own font settings.

Next we enter a name of the report, add a row and enter the SQL query that will get the minimum and maximum dates that are contained in the view, on the basis of which reports will be generated. Such a SQL query is used in many forms of the report supplied with Tariscope. Therefore, it can simply be copied from any other report form:

 SELECT MIN(CallDateTime) As MinDate, MAX(CallDateTime) as MaxDate FROM $Calls$ WHERE NOT CallDateTime IS NULL

The MinDate field will contain the earliest date and time of the call in the view, and the MaxDate field will contain the most recent date and time in the view.

These fields we will display in the Report period position. Also we create the Date position of report. The current date for this position we obtain using the function: Now.

The report form is dispayed as shown in Figure 4.

report form1
Figure 4

Then we create the main query that will group the data by the origCause_value field and count the number of calls to a specific code:

SELECT origCause_value, COUNT(origCause_value) as CauseNumber FROM CallsCCM WHERE CallID IN (SELECT ID FROM $Calls$) GROUP BY origCause_value ORDER BY origCause_value

origCause_value after the SELECT clause means that you need to select this field.

The COUNT(origCause_value) clause counts the number of calls for the specific code and it is saved in the CauseNumber field.

The data are selected from the CallsCCM table.

The WHERE clause specifies conditions for data selection. If you process data from one CUCM the expression beginning from WHERE to GROUP, you can no specify. In case, you process data from a few CUCMs, in order to in the CallsCCM table to find data relating only to the calls that are displayed in the view, you must enter the expression: CallID IN (SELECT ID FROM $ Calls $)

After GROUP BY clause we specify the field on which data should be grouped.

Finally, in order to the data to be sorted by termination code is specified the field after the ORDER BY.

The report form takes on a form as shown in Figure 5.

report form2
Figure 5

Next, in the header portion of SQL query (in the structure of the report is highlighted in red) we enter the title of the report.

In the line of the body of the table, which located between the lines highlighted in red and green, we enter the fields of the query.

In line of outcomes (highlighted in green) the total number of calls will be counted. To do this, the right of the table header, enter: = MarkRow

This means that the calculation will be carried out beginning from the line below it. And in the final row, we enter: = SumCol(3) that means that a sum of value of the rows is calculated in the third column. The numbering of columns is displayed at the top of the report window.

Finally the report form is ready (Figure 6).

report form3 Figure 6

Start the report generation in the Tariscope program and get the result as shown in Figure 7.

result_report Figure 7

Looking at the results, you can want to add a description of the call termination cause to the report.

Currently, this information is not in the database, so you can solve the problem by writing a small code into VBScript. Let's make a two-dimensional array containing the termination codes and their descriptions, and create a function that returns a description of the cause of call termination on the basis of this argument, which is the termination code. To create the code you need to select in the program menu: Report → Common script code. The Global code window appears where we should enter the script code. An example of this window with the entered code is shown in Figure 8.

code Figure 8

In the shown example the array was entered only a part of the codes and their descriptions. For universal use this report form, enter all possible termination codes and their descriptions.

There may be other embodiments of the desired function.

Now we back to the form of a report and add another column, which will display the description of the call termination. Report form will look as shown in Figure 9.

report_form4
Figure 9

As seen in Figure 8, the function that returns a description of the call termination cause is named as CodeDescription.

Therefore, this is specified in the table (Figure 9), and its argument is the value of the termination code origCause_value: $CodeDescription(#origCause_value#)$

Re-build the report. The new report is shown in Figure 10.

If necessary, this form can be improved further, for example by changing the sorting field with Termination code on the Calls number, adding the Percent column, where the percent of calls to a specific termination code of the total number of calls is displayed, etc.

result_report2 Figure 10

Report on used codecs

In some cases, the codecs, which are used for making calls, may be of interest for administrators of Cisco Unified Communications Manager. This information can be useful for example to identify the causes with voice quality, occupied bandwidth, etc.

Let's create the report form that will be displayed:

codec code;
codec description;
number of calls with specific codec.

To reduce the time to create a form, let's use the above considered report form as a template.

Let's open the report form, shown in Figure 8 and save it under a different name. Let's change the report manifest and name of report.

The first SQL query that finds the period of report we use without changes.

Let's proceed to the main report query. Before you make any changes in it we will open the "Tariscope. Database schema" document, and in Section 2.13 we will find a field that contains information about the codec. This is origMediaCap_payloadCapability.

Next we make changes to the request which forms data for a table with the results:

SELECT origMediaCap_payloadCapability, COUNT(origMediaCap_payloadCapability) as CodecNumber FROM CallsCCM WHERE CallID IN (SELECT ID FROM $Calls$) GROUP By origMediaCap_payloadCapability ORDER BY CodecNumber DESC

The query selects the origMediaCap_payloadCapability field and counts a number of rows with specific value of the field COUNT(origMediaCap_payloadCapability) as CodecNumber. Data are selected from the CallsCCM table. The query selects only those rows which have the appropriate rows in the view that is used to create the report: WHERE CallID IN (SELECT ID FROM $Calls$) GROUP By origMediaCap_payloadCapability. Results of query sorts by the CodecNumber field. Next, we change the code (menu: Report → Common script code) as shown in Figure 11.

codec_code
Figure 11

Further we make the appropriate changes to the table of the report form. As a result we have a form as shown in Figure 12.

codecs_report
Figure 12

Then we create a report based on the created form. An example of the generated report codecs is shown in Figure 13.

codecs_result
Figure 13

In conclusion, we note that the users of Tariscope and Cisco Unified Communications Manager can create any reports on data obtained from CDR files using these examples.