Cisco Unified Contact Center Express Historical Reporting Administrator and Developer Guide, Release 9.0(2)
Creating custom reports for Unified CCX
Downloads: This chapterpdf (PDF - 1.49MB) The complete bookPDF (PDF - 4.43MB) | The complete bookePub (ePub - 377.0KB) | Feedback

Creating custom reports for Unified CCX

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:

  1. Use Crystal Reports, a generally available third-party application, to create and save the report.
  2. Create an XML report definition file for the report.
  3. 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

The following sections describe how to create a custom report:

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

          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. The version of the standalone Unified Intelligence Center should be the same or older than the Unified Intelligence Center that is embedded in Unified CCX. 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.