Creating custom report forms for CUCM

This article is intended for users of the Tariscope billing system, who use it to analyze calls made through Cisco Unified Communications Manager (CUCM) and who want to get some additional call information that is contained in CDR files but not in call views and standard Tariscope system report forms. This article may also be useful to all Tariscope users as an example of how to independently create the necessary report forms.

The CDR file from CUCM contains many fields, most of which do not make it into the Tariscope database unless the phone system is configured to store all CDR fields. In some cases, those CDR fields that are not processed may be of interest. For example, this can be useful if you want to analyze which party in a phone conversation ended the call. There is a separate article about this case on the website. 

Another example is the analysis of call completion codes. Information about this does not require storing all CDR fields of CUCM, and this issue is the subject of the following article on the site. But if you want to generate periodic reports on call completion codes, we suggest you continue reading this article, where we will look at how to create the necessary report form.

To create report forms for Tariscope, you should use the free Microsoft Report Builder program. The installation of this program can be downloaded from the Microsoft website.

It is impossible to consider all the features of this program within the scope of this article. For this, please refer to the documentation on the Microsoft website. We will consider only the main steps that need to be performed to create a report form on call termination codes. We will assume that we need to create a report form, which will be used to generate reports for a given period and will display call termination codes and the number of calls of the corresponding code. The report data should be sorted by call termination codes.

Note that the report form is based on an SQL query or an entire program using the Transact-SQL (T-SQL). In addition, you need to know where the necessary data is in the Tariscope database. This information can be found in the document “Tariscope 4.x. Database schema”. This document is provided only to official Tariscope users. To do this, send a request to the SoftPI support service.

After launching the Microsoft Report Builder program, a window appears that offers options for creating a report form using various wizards or using a “Blank Report.” If we choose the latter option, the program window will look like in Figure 1.

Figure 1

The program window contains a menu, as well as the following windows: Report Data, report forms, and Properties.

The program menu contains the following items:

• File. It allows you to open an existing report form or save a report form.
• Home. A toolbar for editing report elements is displayed, as well as the Run button to start generating the report.
• Insert. A panel with elements that can be inserted into the report form is displayed.
• View. A toolbar is displayed for selecting which application windows to display.

The Properties window displays properties that apply to the entire report form. We recommend that you set some report properties right away, such as Author, Description, and Language.

The first two parameters are informative and are not required for the task, although we recommend specifying them. However, the absence of a specific language can sometimes lead to the report not working. For example, if you are creating a report form in German, you should select the de-DE value in the Language list.

Among the report properties, also pay attention to the report size. The following parameters are used for this: ActiveSize, Margins, PageSize. You can change these parameters if necessary.

The default report form already has the Report Title section at the top and a Page Footer section at the bottom, which displays a built-in parameter that displays the execution time of the query to the SQL server. This parameter is called Execution Time.

A list of other built-in fields that can be used in a report form can be seen by opening the Build-in-Fields branch in the Report Data window.

If you are not interested in the query execution time, click on the outline of the &ExecutionTime field and delete it. If you do not plan to display any data in the footer, you can also delete it. To do this, set the Height parameter to 0 in its properties. You can also delete the Report Title section and insert a text box for the report title. To do this, select Insert in the program menu and click on the Text Box icon and select the area on the report form where this field will be located. Then enter the name of the report in it (Figure 2).

Figure 2

Let's set the report parameters: the date and time from which the call data will be analyzed, and the date and time to which the call data will be analyzed. To do this, in the New window, select Parameters and right-click. The Add Parameter item will appear, click on it. As a result, the Report Parameter Properties window will appear.

In this window, in the Name box, enter the name of the parameter. For example, for the first parameter it will be FromDate. In the Prompt box, we will set, for example, the phrase “From date, time”. And in the Data Type list, we will select the Date/Time item. Then click the OK button to save the parameter data.

Repeat similar steps for the second parameter (Figure 3).

Figure 3

The next step in creating a report form is to create an SQL query to obtain the necessary data. To do this, you need to understand where to get this data. The main call parameters are stored in the Calls table or in the viCalls view. As mentioned above, such information can be found in the document “Tariscope 4.x. Database schema”. As can be seen from the description of the Calls table in this document, the table contains the ReleaseCause field, which stores the reason code for the call termination. Also, from this table, to create an SQL query, you will need the CallDateTime field, which stores the date and time of the call start.

To create an SQL query in the report form, first, you need to specify the data source. In our case, this is the Tariscope database. To do this, in the Report Data window, select Data Sources and right-click, then click Add Data Source. The Data Source Properties window will appear (Figure 4), where you need to specify the connection parameters to the SQL server and the Tariscope database.

Figure 4

In the Name box, you can leave the name DataSource1 or replace it, for example, with Tariscope.

Select Use a connection embedded in my report.

In the Select connection type list, leave Microsoft SQL Server selected.

In the Connection string box, you need to specify the connection string to the Tariscope database. Depending on which computer you are creating the report form on, this connection string may be different.

One of the options for the connection string to the Tariscope database when creating the report form on the same computer where the SQL server is installed will be the following:

Data Source=.\\;Initial Catalog=Tariscope

To make sure that this line is entered correctly, click the Test connection button. If everything is entered correctly, you will get a window with the inscription: Connection created successfully. If you get an error during the test connection, then check the entered data and correct the error.

Click the OK button to save the database connection string settings.

In the Report Data window, select Datasets, then right-click and click Add Dataset.

The Dataset Properties window will appear (Figure 5).

  Figure 5

In the Name textbox, you can leave the name of the dataset or specify your own name, for example, CallTerminationCodes.

Select the Use a dataset embedded in my report check box. The Dataset Properties window will change its appearance slightly (Figure 6).

  Figure 6

In the Data source list, select the name of the data source that you created in the previous step. In our example, this is the Tariscope data source.

In the Query textbox, enter the SQL query that will retrieve the necessary data from the Tariscope database.

One of the SQL query options that will display the call termination reason code and the number of calls with this code for a given period would be the following:

SELECT ReleaseCause, COUNT(ID) AS TerminationCodeNumber
FROM Calls
WHERE CallDateTime BETWEEN @FromDate AND @ToDate
GROUP BY ReleaseCause
ORDER BY ReleaseCause

The first line of this query provides the selection of the call termination code (ReleaseCause) and the number of times each code occurs (TerminationCodeNumber).

The second line of the query shows that the data is selected from the Calls table.

The third line indicates that the data is selected for the period where the start of the call (the CallDateTime field) is within the time interval specified by the period start (@FromDate) and period end (@ToDate) parameters.

The fourth line provides grouping of the data by the call termination reason field (ReleaseCause).

The last, fifth line orders the query results by the value of the ReleaseCause field.

The above SQL query will work correctly if you use only one telephone system, CUCM, with Tariscope. If you have several telephone systems, but only one CUCM, then the selection of data based on its calls can be specified directly in the SQL query conditions. To do this, you need to determine what identifier CUCM has in the Tariscope system. To do this, open the Equipment page in Tariscope. The telephone system table contains identifiers (IDs) of telephone systems. Find the ID that belongs to CUCM. For example, this is ID = 320. In this case, the above SQL query should be written as follows:

SELECT ReleaseCause, COUNT(ID) AS TerminationCodeNumber
FROM Calls
WHERE CallDateTime BETWEEN @FromDate AND @ToDate
         AND PBXID = 320
GROUP BY ReleaseCause
ORDER BY ReleaseCause

In this request, the part that was added is highlighted. That is, the condition that calls are processed only from the PBX with the identifier of 320.

In that case, if you have several telephone systems and you want to receive a separate report for each of them, then you need to add another parameter to the report form, identifier of PBX. The data type for this parameter should be integer. And if we called it PBXID, then the SQL query that considers the PBX will look like this:

SELECT ReleaseCause, COUNT(ID) AS TerminationCodeNumber
FROM Calls
WHERE CallDateTime BETWEEN @FromDate AND @ToDate
        AND PBXID = @PBXID
GROUP BY ReleaseCause
ORDER BY ReleaseCause

We recommend that you always make sure that the query works correctly before writing a query in this window. To do this, you can use either the SQL-queries page in Tariscope or Microsoft SQL Server Management Studio (SSMS). When checking this, keep in mind that instead of the report form parameters, you should specify specific data. For example, we can test our last query on data for 09/01/2024 for the PBX with ID = 320. To do this, the query should look like this:

SELECT ReleaseCause, COUNT(ID) AS TerminationCodeNumber
FROM Calls
WHERE CallDateTime BETWEEN '2024-09-01 00:00:00' AND '2024-09-01 23:59:00' AND
PBXTD =320
GROUP BY ReleaseCause
ORDER BY ReleaseCause

After you have made sure that the query works correctly and you have inserted it into the Dataset Properties window, save this query by clicking OK in this window.

Now let's add a table to the report form, where the results of the SQL query will be displayed. To do this, in the Microsoft Report Builder program menu, select the Insert item, and then click on the Table -> Insert table icon on the toolbar. Then specify a place for the table on the report form. The report form will look like the one shown in Figure 7.

 Figure 7

In this case, the Properties window will display the parameters of this table. We need to link the table to the dataset that we previously created. To do this, for the DataSetName table parameter, we need to select the CallTerminationCodes dataset.

The table that we inserted contains 3 columns. But in our query, we only have two fields. Therefore, one of the table columns needs to be deleted. To do this, click on the column header row, a table border will appear, click on this border, which will cause a menu to appear (Figure 8).

Figure 8

Select Delete Columns, this column will be deleted. There are other options for deleting a column.

In the remaining columns, we will enter the headings, and in the next row we will select the data fields that will be displayed (Figure 9).

 Figure 9

After that, you can check the report generation. To do this, click the Run icon on the program toolbar. As a result, the program will look like shown in Figure 10.

Figure 10

In the From date, time box, enter the date and time of the start of the period.

In the To date, time box, enter the date and time of the end of the period.

In the PBX ID box, enter the PBX identifier. For example: 320

Click the View Report button. If everything is done correctly, the report will be displayed.
An example of such a report is shown in Figure 11.

Figure 11

 We got the result we expected. But the report can be improved. For example, specify the period for which it contains data; add a description of the call termination code, specify the total number of calls, and so on.

Let's add data rOK.egarding the period for which the report is generated. This data is contained in the FromDate and ToDate parameters that we created. Add a Text Box to the report form by analogy with how we added a table. Select this text position on the report form and right-click. A menu will appear in which you need to select the Expression value. This opens the Expression window. In this window, you need to enter an expression, an example of which is shown in Figure 12, and click.

Figure 12

If you then run the report, the report will now contain data for the period shown in the example in Figure 13.

Figure 13

Now let's add a summary of the number of calls that are included in the report. To do this, click on the report form on the table row where the data is displayed. Right-click, in the menu that appears, select: Insert Row -> Outside Group – Below. A new row will appear. In this row, in the Termination Code column, you can enter, for example: Totals, and in the Number column, right-click and select Expression from the menu. In the Expression window, enter the following expression:

SUM(CInt(Fields!TerminationCodeNumber.Value))

This expression will summarize the value of the TerminationCodeNumber field using the SUM function, which was previously converted to an integer using the CInt function. Having generated a report on these changes, we will get the result, an example of which is shown in Figure 14.

Figure 14

Now let's look at how to add a description of call termination codes to the report. Such information is directly in the Tariscope system code, but it is not in the database. Therefore, one of the options is to add such information to the Tariscope database. To do this, you need to have a list of codes and their descriptions. The list of codes and their descriptions in Ukrainian are in the article on our website in the Table 1.

Copy the data from this table and paste it into Excel Sheet. The first row should contain the column names (Figure 15).

Figure 15

Save this data in a csv file with the name: CallTerminationCauseCodes.csv
The name of this file can be anything, but it should not coincide with any name of the tables in the Tariscope database.

To create a table with termination codes and their description, we will use the Microsoft SQL Server Management Studio (SSMS) program. You need to connect to the SQL server with the Tariscope database in this program. Select the Tariscope database row, right-click and in the menu that appears, select: Tasks -> Import Flat File. The Import Flat File ‘Tariscope’ window will open. Click Next. The window will look like the one shown in Figure 16.

Figure 16

Click Browse and select the created csv file. In the New table name box, a file name will be suggested. If desired, you can change this name. Click Next. The window will display a preview of the data. If everything is satisfactory, click Next. The program window will display the structure of the table being created, an example of which is shown in Figure 17.

Figure 17

If desired, you can make the Code field as Primary Key.

Click Next, then Finish. If the import from the csv file is successful, the following the Operation Complete message will appear. After that, you need to update the data and check if a new table has been created, in our example it is dbo.CallTerminationCauseCodes.

Now you need to change the SQL query of the report form to consider the data of this table in the report. One of the options for doing this is given below:

SELECT ReleaseCause, Min(Description) AS Description, COUNT(ID) AS
TerminationCodeNumber
FROM Calls
JOIN CallTerminationCauseCodes ON Code = ReleaseCause
WHERE CallDateTime BETWEEN @FromDate AND @ToDate
         AND PBXID = @PBXID
GROUP BY ReleaseCause
ORDER BY ReleaseCause

After saving this query, you need to add a display of the Description field value in the report form table. To do this, select the Number column and right-click. In the menu that appears, select: Insert Column -> Left. In the column header, write Description. In the values ​​row, select Description (Figure 18).

Figure 18

If we now generate this report, we will get a result like that shown in Figure 19.

Figure 19

You can further improve this report form depending on the requirements for it.

If this report is planned to be generated automatically on a schedule using the Tariscope Tasks, then you need to make changes to the SQL query again so that the report automatically generates the specified period relative to the current time, since it will be impossible to specify them as parameters, and directly, without a parameter, specify the PBX identifier. Depending on the period for which you plan to generate the report, the SQL query may differ slightly. If, for example, you plan to generate reports by call termination codes every hour, then in the report form you should remove the parameters, and write the SQL query as follows:

SELECT ReleaseCause, Min(Description) AS Description, COUNT(ID) AS
TerminationCodeNumber
FROM Calls
JOIN CallTerminationCauseCodes ON Code = ReleaseCause
WHERE CallDateTime BETWEEN DateAdd(hh, -1, GetDate()) AND GetDate()
       AND PBXID = 320
GROUP BY ReleaseCause
ORDER BY ReleaseCause

In this query, you must change the value of the PBXID field from 320 to your CUCM ID. The current time is determined by using the GetDate function. The time that is one hour earlier than the current time is determined using the DateAdd function.