Cisco Unified Contact Center Express Historical Reporting Administrator and Developer Guide, Release 9.0(2) - Creating custom reports for Unified CCX [Cisco Unified Contact Center Express]

Contents
Creating custom reports for Unified CCX
Creating custom historical reports
Creating a report
Before you begin
Create a report using Crystal Reports Version 11
Create a report definition file
Update the reports catalog file
Permissions to the stored procedure
Viewing stored procedures for existing historical reports
Troubleshooting
New report does not appear on the Report Type drop-down list
Generating a custom report fails with Invalid name error
Creating Custom Cisco Unified Intelligence Center Reports
Creating Custom Unified CCX Stock Reports
Comparison between Historical Reporting Client and Cisco Unified Intelligence Center
Creating custom reports for Unified CCX

Creating custom historical reports
Creating Custom Cisco Unified Intelligence Center Reports
Creating Custom Unified CCX Stock Reports
Comparison between Historical Reporting Client and Cisco Unified Intelligence Center
Creating custom historical reports

You can create your own Unified CCX historical reports and add them to the Report Type drop-down list on the Unified CCX Historical Reports main window.

When you create a custom report, you follow these general steps:

Use Crystal Reports, a generally available third-party application, to create and save the report.

Create an XML report definition file for the report.

Make sure that the report generates properly.

You must use Crystal Reports Version 11 or 11.5 (Professional or Developer Edition) to create custom historical reports.

This chapter contains these topics:

Creating a report

Create a report definition file

Update the reports catalog file

Creating a report
Create a report definition file
Update the reports catalog file
Permissions to the stored procedure
Viewing stored procedures for existing historical reports
Troubleshooting

Creating a report

The following sections describe how to create a custom report:

Before you begin

Create a report using Crystal Reports Version 11

Before you begin
Create a report using Crystal Reports Version 11

Before you begin

Before you create a new report, you need to create a DSN from client machine to Informix Server. To create a DSN to Informix server, HRC must be installed on the client machine where you need to create reports. Follow these steps to create the DSN:

Procedure
Step 1   Go to Control Panel > Administrative Tools > Data Sources.

Step 2   Select the System DSN tab. ODBC Data Source Administrator window is displayed.

Step 3   Click Add to create a new dsn.

Step 4   The Create New Data Source window is displayed. From the list of available data sources, select IBM INFORMIX ODBC DRIVER. Click Finish.

Step 5   The ODBC Driver Setup window is displayed. Enter a unique DSN name. Enter a suitable description. Click Apply.

Step 6   Select the Environment tab. Check the Use Server Database locale check box. Ensure that the entry for Fetch Buffer size field is 32767. If default entry is other than 32767, then manually change it to 32767. Click Apply.

Step 7   Select the Connection tab. In the Server Name field, enter the instance name of Informix server of the setup.
Follow these naming conventions when creating an instance name:

Add the letter "i" as a prefix to the instance name, if the host name starts with a number.

Enter the instance name after converting all upper case letters to lower case.

Replace hyphens with underscore.

Append the letters "_uccx" to the instance name.

For example, if the host name is "851UCCX-Ha-Node1", enter "i851uccx_ha_node1_uccx" in the server name field.

In the host name field, enter either host name or IP address of Unified CCX server. In service field, enter 1504, which is the TCP port number. In the Protocol field, choose onsoctcp. In the database field, enter "db_cra" which is the database name that stores historical data. In the userId field, enter "uccxhruser" which is the default Historical Reporting user. The password for this user can be set at the Password Management page of the Unified CCX Administration web interface. The password management page appears under tools menu of appadmin. In the password field of ODBC Driver set up page, enter the password, which is set at the Password management page by the administrator. Click Apply & Test connection.

Step 8   Ensure that the "Test Connection was successful" message is displayed. You can view the newly created DSN in the list of DSNs. Close the window.
Tip
You can use the same DSN to create any number of reports.

Create a report using Crystal Reports Version 11

To create a new report, use Crystal Reports Version 11 or 11.5 (Professional or Developer Edition), a generally available third-party application, to create two versions of the report. The versions will be identical, except that one version will contain charts.

Tip

Build the report with charts first and save it with the appropriate name for a report with charts as explained in Step 12. Then, delete the charts and save the modified report with the appropriate name for a report without charts as explained in Step 12.

For more information about creating reports with Crystal Reports, refer to your Crystal Reports documentation. For information about the tables and records in the Unified CCX database, see Unified CCX Database Schema.

Procedure
Step 1   Start Crystal Reports and choose File > New > Standard Report.
The Standard Report Creation Wizard Data window displays.

Step 2   In the Standard Report Creation Data Wizard window, expand Create New Connection and then expand OLE DB (ADO).
The OLE DB Provider window displays.

Step 3   In the OLE DB Provider window, highlight Microsoft OLE DB Provider for ODBC Drivers in the Provider list, click Next.

Step 4   Select the DSN that was created to Informix Database of Unified CCX Server. Click Next. The Connection Information window displays.

Step 5   In the User ID field, enter uccxhruser, which is the default UserID to connect to Informix. In the Password field, enter password for the uccxhruser user that is set at the Password Management page of Unified CCX Admnistration web interface. Ensure that the default database is db_cra.

Step 6   Click Next. The Advanced Information window displays.

Step 7   Click Finish. The Standard Report Creation Wizard displays.

Step 8   Double-click the Add Command button that is displayed under the dsn name that was created for Informix server. The Add Command To Report window displays. Enter the sample query for the report that needs to be created.
Tip
Ensure that start date and end date of the query are selected properly so that some sample data will be available to display in the report template after completion of preparation of the custom report.

Step 9   Click OK and then click Next. The sample image below shows a query for stored procedure 'sp_agent_summary'.

Step 10   The available fields for the report will be displayed in a separate window. The available fields are generated based on the stored procedure that is used to create the report. Select the required fields that need to be displayed in the report and click Next. The available fields are the fields that are returned by the stored procedure ‘sp_agent_summary’.

Step 11   For optional settings, follow the instructions provided by the Crystal Reports wizard to complete setting up the report. Refer to your Crystal Reports documentation for more information

Step 12   Save the report in the following directory under the directory in which you installed the client system. (By default, the client system installs in the Program Files directory.) Replace Language with the name of the appropriate language directory.
Unified CCX Historical Reports\ Report Templates\Language

Name the report as follows:

Category_ReportDisplayName_ReportFormat_Locale.rpt

Replace Category, ReportDisplayName, ReportFormat, and Locale with appropriate values as explained in Table 1.

Table 1 File name components

Component

Explanation

Valid values

Category

Unified CCX application package with which to associate this report. Users with access to this package will be able to generate this report.

CCR—Unified CCX Enhanced or Unified CCX Premium

ICD—Unified CCX Standard

IVR—Unified IP IVR

EEM—Unified CCX email

ReportFormat

Whether the report includes charts.

Chart—Report includes charts

Table—Report does not include charts

ReportDisplayName

You can use any name. This name will be replaced with the name specified by vFileName in the reports catalog file line that you add for this report.

If a name has more than one word, separate each word with an underscore_.

—

Locale

Locale ISO string. Specifies the language in which you created the field names, headings, and other static items in the report. Consists of an ISO language code followed by an underscore (_) and then an ISO country code.

Languages installed on the Unified CCX Historical Reports client system. For example:

en_US—English

da_DK—Danish

de_DE—German

es_ES—Spanish

fr_FR—French

it_IT—Italian

ja_JP—Japanese

ko_KR—Korean

nl_NL—Dutch

pt_BR—Portugese

ru_RU—Russian

sv_SE—Sweedish

zh_CN—Simplified Chinese

zh_TW—Traditional Chinese

To see the languages installed on the client system, look at the folder names in the Unified CCX Historical Reports\ Report Templates directory.

The following is an example of a report name:

CCR_My_New_Report_Chart_en_us.rpt

In this example:

Users who have access to the Unified CCX Enhanced application package will be able to generate this report.

The report will appear as My New Report in the Report Type drop-down list on the Unified CCX Historical Reports main window.

The report includes one or more charts.

The report is designed to run on a system with a system locale of English.

The extension .rpt indicates that this file is a report file.

Now you are ready to create a report definition file as described the Create a report definition file.

Create a report definition file

A report definition file is in XML format and contains information that the Unified CCX Historical Reports client system needs to generate a report.

As the following steps show, when you create a report definition file for a custom report, you begin with an existing Unified CCX Historical Reports definition file. You save the existing file under a new name, and then you modify the new file as needed. You may find it convenient to begin with the CCR_agent_Summary_en_us.xml file because this file contains examples of most of the XML tags.

To create a report definition file, follow these steps:

Procedure
Step 1   Use a Windows text editor to open an existing report definition file.
Report definition files are located in the Unified CCX Historical Reports\ Report Templates\Language directory under the directory in which you installed the client system. (By default, the client system installs in the Program Files directory.) Language is the name of the directory in which you saved the report as described in the Creating a report.

Step 2   Before you make any changes, choose File > Save As to save the file under a new name. Save the file in the directory that contains the original file, and give the new file the following name: Category_ReportDisplayName_Locale.xml
Replace Category, ReportDisplayName and Locale with the same values that you used when you created the report as described in the Creating a report.

Step 3   Edit the new file by changing the appropriate XML tag attributes or tag values as explained in Table 1. Make sure to surround all XML attribute values with quotation marks.
There are other values in this file. Do not change these values.

Table 2 Report definition XML attributes and tags

XML tag or attribute

Explanation

Report

Defines the report by describing its SQL command, parameter filters, and sort criteria. Includes these attributes:

ReportName

ReportCategory

BasetemplateFile

ReportName

Name of the report as it will appear in the Report Type drop-down list on the Unified CCX Historical Reports main window.

For release 3.0(2) and later, you can specify any name for the report.

BasetemplateFile

This value should be the same as the value that you specified for ReportDisplayName when you created the report file as explained in the Creating a report.

ReportCategory

Unified CCX application package with which to associate this report. This value should be the same as the value that you specified for Category when you created the report file as explained in the Creating a report. Users with access to this package will be able to generate this report.

Valid values:

"CCR"—Unified CCX Enhanced or Unified CCX Premium

"ICD"—Unified CCX Standard

"IVR"—Unified IP IVR

ReportDescription

Brief description of the report. This description will appear on the Unified CCX Historical Reports main window when the report is selected from the Report Type drop-down list.

ReportSQLCommand

SQL command (query or stored procedure) upon which the report is based. You can follow this name with any number of parameters. When this report is generated, the Unified CCX Historical Reports client will replace each parameter with its value.

Each parameter must follow these conventions:

A parameter must begin with a dollar sign ($)

A parameter may contain only alphabetic characters and underscores (_)

At a minimum, the SQL command requires the following parameters:

$StartDate—First date of the date range for which to generate information in the report, as specified by a user

$EndDate—Last date of the date range for which to generate information in the report, as specified by a user

$SortBy—Sort method that a user specifies for the report

If the command includes characters that the XML processor might interpret as markup characters, use the standard XML entity references for the characters or use hexadecimal character references. Refer to your XML documentation for more information.

ReportParameterList

Defines each parameter that is specified in the SQL command (see the ReportSQLCommand tag), except for $StartDate, $EndDate, and $SortBy.

MutualExclusive

Defines whether the filter parameters specified in the ReportParameterList field are mutually exclusive. If the parameters are mutually exclusive, the filter parameter that appears in the Filter Parameter field on the Unified CCX Historical Reports Detailed Report Settings window will be used in the SQL command when the report generates. Other parameters will be set to their default values as specified by the ParamDefaultValue attribute. If the parameters are not mutually exclusive, all filter parameters that are set will be used in the SQL command when the report generates.

Valid values:

"True"—Parameters are mutually exclusive

"False"—Parameters are not mutually exclusive

SQLParameter

Defines a SQL parameter that is specified in the ReportSQLCommand tag. There must be one SQLParameter tag for each parameter that is defined in the ReportSQLCommand tag, except for $StartDate, $EndDate, and $SortBy. (See the ReportSQLCommand tag for information about the $StartDate, $EndDate, and $SortBy parameters.) Includes these attributes:

ParamName

ParamDefaultValue

Includes these XML tags:

ParamNameText

ParamHelpText

Either DBListToList or TextBox

ParamName

Defines the name of the parameter as specified in the ReportSQLCommand tag. Must match the parameter name exactly, including the leading dollar sign ($).

ParamDefaultValue

Default value of the parameter specified in the SQLParameter field. Used if a value is not specified when setting are made for the report. Also used if the MutualExclusive attribute is set to true and this parameter does not appear in the Filter Parameter field on the Unified CCX Historical Reports Detailed Report Settings window when the report generates.

ParamNameText

Name of the filter parameter that appears on the Filter Parameter drop-down list on the Unified CCX Historical Reports Detailed Report Settings window.

ParamHelpText

Brief description of the parameter. This description appears as a tool tip when you place the cursor over a filter parameter name in the Filter Parameter field on the Unified CCX Historical Reports Detailed Report Settings window.

DBListToList

Specifies that filter parameters can be selected using the Available or the Selected list on the Unified CCX Historical Reports Detailed Report Settings window. Filter parameters in the Available list come from the SQL tag or from the SourceListValues tag. Contains these attributes:

GUIComponentName

SingleSelection

Contains these tags:

Either SQL or SourceListValues

selectedListOperations

GUIComponentName

Defines the name of the parameter selection interface. You must specify a name, but this attribute is reserved for future use.

SingleSelection

Specifies whether the DBListToList tag allows a single selection or multiple selections from a drop-down list of values.

Valid values:

"1"—DBListToList allows a single selection from a list of values

"0"—DBListToList allows multiple selections from a list of values

SQL

Defines the SQL command used to generate values that appear in the Available list on the Unified CCX Historical Reports Detailed Report Settings window.

SourceListValues

Defines values that appear in the Available list on the Unified CCX Historical Reports Detailed Report Settings window. Contains one or more ListOption tags. Refer to the CCR_Priority_Summary_Activity_en_us.xml file for an example of how this tag is used.

selectedListOperations

Specifies the operation that is included with the values in the Selected list on the Unified CCX Historical Reports Detailed Report Settings window when the report is generated. Valid operations are AND and OR.

When the report is generated, the value of the SQL parameter containing a DBListToList tag will be Operation|Value1, Value2, Value3, . . . , where Operation is AND or OR, and Value1, Value2, Value3, and so on are the values in the Selected list.

TextBox

Specifies that filter parameters can be selected in the Enter Parameter Value field on the Unified CCX Historical Reports Detailed Report Settings window. Contains these attributes:

GUIComponentName

Type

Type

Specifies whether the value entered in the Enter Parameter Value field is a number or text.

Valid values:

"INT"—Positive integer value

"STRING"—Text value

ReportSortBy

Defines a list of fields by which a user can sort the report. This list will appear in the Sort Report By drop-down list on the Unified CCX Historical Reports Detailed Report Settings window. Contains one or more ListOption tags.

ListOption

Defines a list of options. Contains these attributes:

OptionsSelected

OptionValue

OptionsSelected

Specifies the value that is selected by default from the list of options specified by the ListOption tag. The last ListOption tag with an OptionsSelected value of True will be displayed.

ListOption tags are included in SourceListValues and ReportSortBy.

OptionValue

Specifies the value of this list option. This value is used when the SQL command executes.

Step 4   Save the file and exit the editor.

What to Do Next

Now update the Reports Catalog File as described in the Update the reports catalog file.

Update the reports catalog file

The reports catalog file is in XML format and maps each report definition file to the associated report name that appears on the Report Type drop-down list. If you create a new report definition file, you must update the reports catalog file.

The reports catalog file is located in the Unified CCX Historical Reports\ Report Templates\Language directory under the directory in which you installed the client system. (By default, the client system installs in the Program Files directory.) Language is the name of the directory in which you saved the report as described in the Creating a report.

The reports catalog file is named as follows:

hrcReportCatalog_Locale.xml

For an explanation of Locale, see Table 1.

To update the reports catalog file for a new report definition file, follow these steps:

Procedure
Step 1   Use a Windows text editor to open the reports catalog file.

Step 2   Add a line in the reports catalog file for the new report definition file. Enter the line in the following format, and place it immediately before the line </ReportCatalog>, which appears at the end of the file. There must be a space before pFileName and a space before vFileName.
<ReportFileName pFileName="Category_ReportDisplayName_Locale.xml" vFileName="ReportName"/>

Replace Category, ReportDisplayName and Locale with same values that you used when you created the report and the report definition file. See Table 1 for an explanation of these values.

Replace ReportName with the same name that you specified with the ReportName XML attribute in the Report Definition file. See the explanation of the ReportName attribute in Table 1.

Here is an example of a line in the reports catalog file for a report that will appear on the Report Type drop-down list in US English locals as My New Report:

<ReportFileName pFileName="CCR_My_New_Report_en_us.xml" vFileName="My New Report"/>

Step 3   Save the file and exit the editor.

Your new report should appear on the Report Type drop-down list the next time you start the Unified CCX Historical Reports client. If the report does not appear on this list or if the report does not generate properly, see the Troubleshooting.
Permissions to the stored procedure
You can create stored procedures using third-party tools such as SQuirreL SQL Client and AGS Server Studio. Once the procedure has been created, provide the necessary execution privileges to the uccxHrUserRole.

Note

The historical reporting client executes the stored procedure under this role. If the permission is not provided, you will not be able to execute the procedures from the client system.

This permission can be provided by executing the following SQL statement using the SQL tool you have used to create the procedure(s).
grant execute on <your procedure name> to 'uccxHrUserRole';
Viewing stored procedures for existing historical reports

You can view the existing Historical Reporting stored procedures using third-party tools such as SQuirreL SQL Client or AGS Server Studio. Use "uccxhruser" as the username to connect to db_cra database.

Troubleshooting

The following sections provide information that can help you troubleshoot custom reports:

New report does not appear on the Report Type drop-down list

Generating a custom report fails with Invalid name error

New report does not appear on the Report Type drop-down list
Generating a custom report fails with Invalid name error

New report does not appear on the Report Type drop-down list

After you create a report and create a report definition file, the following files should exist in the Unified CCX Historical Reports\ Report Templates\Language directory under the directory in which you installed the client system. (By default, the client system installs in the Program Files directory.) These files must follow these naming conventions exactly or the report will not generate. For an explanation of Language, Category, ReportName, and Locale, see Table 1.

Category_ReportName_Chart_Locale.rpt

Category_ReportName_Table_Locale.rpt

Category_ReportName_Locale.xml

In addition, an entry for the report should exist in the reports catalog file.

If the report does not appear on the Report Type drop-down list on the Unified CCX Historical Reports main window or if the report does not generate,

Make sure that the entry for the report in the reports catalog file is in the correct format and that this file is located in the correct directory.

Make sure that the report files and the XML report definition file have the correct names and are located in the correct directory.

If the report files and the XML report definition file have the correct names and are located in the correct directory but the report does not generate or you receive an error message when you select the report from the Report Type drop-down list, perform the following steps to locate the problem. You perform these steps on the computer on which you are trying to generate the report.

Procedure
Step 1   Edit the hrcConfig.ini configuration file and set the logLevel parameter to 3.
For information about the hrcConfig.ini file and how to edit it, refer to Cisco Unified Contact Center Express Historical Reports User Guide.

Step 2   Stop and restart the Unified CCX Historical Reports client so that your change to the hrcConfig.ini file takes effect.

Step 3   Try again to generate the new report.

Step 4   Open the Historical Reports log file and find the SQL command used to generate the report. Check the command for errors, and make necessary corrections in the XML report definition file.
For information about the Historical Reports log file, refer to Cisco Unified Contact Center Express Historical Reports User Guide.

Generating a custom report fails with Invalid name error

Attempting to generate a scheduled custom report fails with the error "Invalid name" when the showUserNameOnReport parameter is set to 1 in the Historical Reports hrcConfig.ini file. To work around this problem, add the label @$UserName to the .rpt report file that you create with Crystal Reports. To determine where to place this attribute, look at the definition file for any of the Cisco-provided historical reports.
Creating Custom Cisco Unified Intelligence Center Reports

Note
The procedure for creating the custom stored procedure for Unified Intelligence Center reports is similar to creating custom stored procedures in the Historical Reporting Client (see Permissions to the stored procedure). For more information on Unified Intelligence Center reporting system, see the Administration Console User Guide for Cisco Unified Intelligence Center, available here: http://www.cisco.com/en/US/products/ps9755/prod_maintenance_guides_list.html.

Custom Unified CCX reports are created on a standalone Cisco Unified Intelligence Center server with an appropriate license. See Creating Custom Unified CCX Cisco Unified Intelligence Center Reports, available here: https://communities.cisco.com/docs/DOC-31936.

Creating Custom Unified CCX Stock Reports

Unified CCX is installed with stock report templates that display historical data that has been stored in the Unified CCX database.

To customize the existing Unified CCX stock reports, see User Guide for the Cisco Unified Intelligence Center Reporting Application, available here: http://www.cisco.com/en/US/products/ps9755/products_user_guide_list.html.

Comparison between Historical Reporting Client and Cisco Unified Intelligence Center

This section lists out the differences between the Historical Reporting Client and Cisco Unified Intelligence Center.

Table 3 Comparison between Historical Reporting Client and Cisco Unified Intelligence Center

Description

Historical Reporting Client

Unified IC

Client

Thick client, need installation

Web based client

Auto refresh

No

Yes

Supported format for exporting reports

pdf, xls, and doc

Only xls for grid and jpg for chart

Supported format for scheduled reports

pdf, xls, and doc

Through email – pdf and xls

Remote location

No

Only xls

Support of chart view for scheduled report

Yes

No

Filters

Only one filter can be selected apart from mandatory filters

Only one filter can be selected apart from mandatory filters

Customization of views for stock reports

Need Crystal license to change the views

Can be done using report edit feature

Custom reporting

Need Crystal license

Need stand alone Cisco Unified Intelligence Center with premium license

Perma link

No

Yes

Thresholds

No

Yes

Dynamic sorting of headers

No

Yes

Dashboards

No

Yes

User permissions

No

Fine grained security permissions/roles

Relative Filters

No

Yes

Summary Reports Creation

No

Yes

Note

The number of records per report supported by Cisco Unified Intelligence Center is 8000.