User Guide for the Cisco Unified Intelligence Center Reporting Application Release 9.1(1)
Report Definitions
Downloads: This chapterpdf (PDF - 2.39MB) The complete bookPDF (PDF - 6.58MB) | Feedback

Report Definitions

Report Definitions

Each report has a Report Definition, which represents how data is retrieved from the data source for that report template.

In addition to specifying how data is retrieved (by a simple MS SQL query, stored procedure query, real time streaming or an anonymous block query), a Report Definition contains the dataset that is obtained. This includes the fields, filters, formulas, refresh rate, and key criteria field for the report.


Note


Unified Intelligence Center installs a stock Report Definition for each report template.

When you create a custom report template from scratch, first select its Report Definition. If the custom template requires a custom Report Definition, create the Report Definition before you create the template.

Access to the Report Definitions interface is controlled by license type and user role. You must have a premium license and a Report Definition Designer user role to open this drawer.

Users with a standard license cannot access the Report Definition drawer to create or edit Report Definitions. They can, however, see and select from a list Report Definitions when they create a report. The Report Definitions they see are the stock Report Definitions and any Report Definitions for custom reports that were created or were imported into their database.

Users with premium license

  • With Report Definition Designer user role, can access the Report Definition drawer.
  • Can view and Save As the stock Report Definitions; for example, to change the refresh rate, and to create new filters and formula fields in the Save As copies.
  • Can create new Report Definitions.

Note


All actions on the Report Definitions interface are based on User Role and on the user's object Permissions for report definitions and for Categories.

See also Troubleshooting Report Definitions.

Related References

Available Report Definitions Page

This page opens when you click the Report Definitions drawer. Use it to manage (edit, create, delete, and rename) Report Definitions. It shows the available Report Definitions, organized in categories. Categories are represented by folder icons. Report definitions are represented by page icons.

The default Report Definitions Imported Report Definitions under it, in an expandable tree format, are all the subcategories and Report Definitions that are defined. These include the Stock folder, with its subfolder of UCCE Report Definitions.

Figure 1. Available Report Definitions

You can rename the default Report Definitions category. You cannot delete it or move it.

Looking at the various stock Report Definitions can be helpful when you design your own.

Actions From Report Definitions Page

  • Open a Report Definition—Click the Report Definition icon.
  • Import Definition—Click Import Definition to open the Import Definition page.
  • Refresh—Click this to update and see changes to Report Definitions or categories.
  • Work with Categories—Actions are:
    • create sub-category from a category or a sub-category
    • delete sub-category
    • rename sub-category
    • set permissions for the sub-category
  • Create a Report Definition. Right-click a category or sub-category and select Create Report Definition.
  • Edit a Report Definition. Right-click a Report Definition and select Edit.
  • Save As—opens the Save As window. Right-click a Report Definition and select Save As.You can save your report definition with a new name, description, report definition category and permissions.
  • Delete a Report Definition. Right-click a Report Definition and select Delete.
  • Rename a Report Definition. Right-click a Report Definition and select Rename.
  • Export a Report Definition. Right-click a Report Definition and select Export.
  • Help—opens online help for the page.
  • Drag and Drop—you can drag and drop Report Definitions and sub-categories.
Related References

Create a Report Definition

This page opens when you right-click a category or sub-category and select Create Report Definition.

Procedure
    Step 1   Enter a name.
    Step 2   Enter a description.
    Step 3   Set the default permissions for My Group.
    Note   

    My Group refers to the Report Definition owner's default group. If this default group is the All Users group, the option to set permissions for non-administrative users is disabled. Only administrative users can set permissions for the All Users group.

    Step 4   Click OK. This opens the Edit Report Definition page.


    Note


    • Custom Report Definitions are not supported by Cisco Systems.
    • Unified Intelligence Center documentation does not explain how to form SQL queries. Your support provider is not prepared to troubleshoot queries.
    • Customers are responsible for the performance of their queries and are advised not to run queries against large datasets.

    Related References

    Save As

    Use this page to clone (save as) an existing Report Definition with a new name and to specify the Report Definition category under which you want it to display.

    Table 1 Fields on the Save As Page

    Field

    Explanation

    Name

    The new name for the Report Definition

    Description

    Enter a description for the Report Definition.

    Save To

    Saves the report in the selected category or subcategory.

    Default Permissions

    Set the permissions for My Group.

    Note   

    My Group refers to the Report Definition owner's default group. If this default group is All Users, the option to set permissions for non-administrative users is disabled. Only administrative users can set permissions for the All Users group.

    Actions on This Page

    • OK—saves your entries and closes the page.
    • Cancel—closes the page without saving your entries.

    Edit Report Definition

    This page opens when you create a new Report Definition and when you right-click an existing Report Definition and select Edit. This page provides a list of default formatting masks.


    Note


    A change to a Report Definition affects all reports that use it.

    Tabs on this page are Data Source, Fields, Parameters (used for Anonymous Blocks and Stored Procedures only), and Properties.

    All tabs display a message noting that reports are affected if you edit a Report Definition.

    The available formats depend on the data type of the field as set in Edit Field Properties. For example, for numeric values the drop-down menu offers possible display formats for numeric values. Selecting (Custom) from this list applies the format string supplied in the Custom Format String to the value returned. The custom formats supported are as follows:

    1. String data type: Adds a string before the string value. For example, if there is a String field called name where it lists the name of the doctors and if you want to append Dr. to the doctors' names in the report, then use custom format for this field and enter the value Dr. The resulting report displays all names as Dr. XXXX.
    2. Decimal data type: Unified Intelligence Center supports decimal data formatting in accordance with the Java decimal formatting rules. The following table lists a few examples of custom formats. The # symbol indicates a digit or nothing if there is no digit present. The digit shows a digit or 0 if there is no digit present:

    Data

    Format

    Value

    123

    123456

    000123

    123456.789

    ###,###.###

    123,456.789

    123456.789

    ###.##

    123456.79

    123.78

    123456.789

    000123.780

    12345.67

    $###,###.###

    $12,345.67

    12345.67

    $1234.56

    $12345.67

    123

    0.0E+0

    1.2E2

    123

    0.0E+00

    1.2E02

    123

    Rs 1234

    Rs 123a

    Complete Data Source tab

    This tab specifies the query information that is used for the Report Definition.

    In the query for a stored procedure and for an anonymous block of a report if multiple queries return multiple results, Unified Intelligence Center returns an error message “The statement did not return a result set”. Click Show Error Detailsto see this error message. If Unified Intelligence Center returns this error message for a stored procedure, include SET NOCOUNT ON as the first statement in the query for the stored procedure on the MS SQL server. For an anonymous block, do the same in the report definition.

    Use the Database Query, Anonymous Block, or Stored Procedure query types to retrieve data from a database. Most stock templates are based on Database Query. Two stock templates are based on Anonymous Block. No stock templates are based on Stored Procedures.

    Complete this tab as follows:

    Procedure
      Step 1   Select the Query Type from these options: Database Query, Anonymous Block, and Stored Procedure.

      Unified Intelligence Center displays appropriate fields based on the Query Type that you select.

      Step 2   Select the Query Type from these options: Database Query, Anonymous Block, or Stored Procedure.

      Unified Intelligence Center displays appropriate fields based on the Query Type that you select.

      Step 3   Select a data source for a report definition from the Data Source drop-down menu. This drop-down menu shows all configured data sources.

      Selecting a Data Source populates the Data Source Type and the Data Source Status fields. If the Data Source Status shows Offline, then troubleshoot the connection.

      Step 4   Select a data source for a report definition from the Data Source drop-down menu. This drop-down menu shows all configured data sources.

      Selecting a Data Source populates the Data Source Type and the Data Source Status fields. If the Data Source Status shows Offline, then troubleshoot the connection.

      Step 5   Under each topic, select the fields that you want in the Report Definition and click Create Fields.

      Unified Intelligence Center sorts field names alphabetically.

      Step 6   For Anonymous Blocks and Stored Procedures, open the Parameters tab, where you can edit parameter properties.
      Step 7   For Database Query, Anonymous Blocks, and Stored Procedures, open the Fields tab, where you can edit field properties and create drill-down links to reports.

      Actions on this page:

      • Save—Saves the values you enter on the page. Save is enabled for custom Report Definitions. You cannot edit and then save a stock Report Definition.
      • Save As—Opens the Save As dialog (prompting for name, description, category, and permissions), and saves a copy of the Report Definition.
      • Cancel—Closes the page without saving your changes.
      • Refresh—Refreshes the page.

      Query Database

      Most stock templates are built from Database queries.

      Database queries populate the Fields tab and the Properties tab, but not the Parameters tab.

      To complete the Data Source tab for a database query:
      Procedure
        Step 1   Use the Query field to review or edit the SQL query for an existing report or to build a SQL query for a new report. You can type the query or paste a query you create in Query Analyzer or similar tool.
        Step 2   Click Create Fields to validate the query syntax and to populate the Fields tab. Each field represents a column in the database.

        Related References

        Complete Data Source Tab for Anonymous Block

        Anonymous Blocks are queries with both parameters and fields. Agent Historical All Fields and Agent Not Ready Detail reports are built from Anonymous Blocks. To complete the Data Source tab for an anonymous block:
        Procedure
          Step 1   In the Anonymous Block Field, enter or paste the anonymous block.

          Example:Here is a sample anonymous block:

          select SkillTargetID, EnterpriseName from Agent where SkillTargetID = :paramName

          Parameter names in the anonymous block must have a colon followed by the parameter name; for example :paramName or :anotherParamName. The colon at the beginning of the parameter name is substituted with the at sign (@).

          Step 2   Click Create Parameters to create parameters. A message informs you that the parameters were successfully created. A panel displays under the Create Parameters button. This panel displays the parameter name, and for each name shows Data Type 'String' and Value 'test'.
          Step 3   For each parameter in the panel, select the correct Data Type. A sample value for this data type appears in the Value text box. This value is pasted into the anonymous block instead of the parameter name to validate it and create fields.
          Step 4   Click Create Fields to validate the query and create fields. A message informs you that the fields were created.
          Step 5   Open the Fields tab to review them.

          For Example

          • Enter this anonymous block
            Figure 2. Anonymous Block Example 1 of 6



          • Click Create Parameters:
            Figure 3. Anonymous Block Example 2 of 6



          • Set the Data Type:
            Figure 4. Anonymous Block Example 3 of 6



          • Click Create Fields.
            Figure 5. Anonymous Block Example 4 of 6



          • Open the Fields tab:
            Figure 6. Anonymous Block Example 5 of 6



          • Open the Parameters tab:
            Figure 7. Anonymous Block Example 6 of 6



          Complete Data Source Tab for Stored Procedure

          Stored Procedures are stored in the data source. No stock templates are built from Stored Procedures.

          To complete the Data Source tab for a stored procedure:

          Procedure
            Step 1   In the Stored Procedure field, enter a name for the Stored Procedure that you have already created for your data source.
            Note   
            • Unified Intelligence Center supports stored procedures for both MS SQL and Informix data sources.
            • The database user for the data source containing the Stored Procedure must have permissions to execute the Stored Procedure.
            Step 2   Click Create Parameters to create parameters. A message confirms that the parameters were successfully created.

            A panel displays under the Create Parameters button. This panel displays the parameter name, and the corresponding Data Type and a test Value.

            A sample value for this data type appears in the Value text box. This value is pasted into the stored procedure instead of the parameter name to validate it and create fields.

            Step 3   Click Create Fields to validate the query and create fields. You see a message that the fields were created.
            Step 4   Open the Fields tab to review the results.

            For example:

            • Complete the fields in Datasource tab
              Figure 8. Stored Procedure Example 1 of 6



            • Click Create Parameters:
              Figure 9. Stored Procedure Example 2 of 6



            • Set the Data Type:
              Figure 10. Stored Procedure Example 3 of 6



            • Click Create Fields.
              Figure 11. Stored Procedure Example 4 of 6



            • Open the Fields tab:
              Figure 12. Stored Procedure Example 5 of 6



            • Open the Parameters tab:
              Figure 13. Stored Procedure Example 6 of 6




            Related References

            Fields Tab

            Use this tab to manage the fields in a Report Definition.

            For a new Report Definition, this tab is empty until you enter the query on the Data Source tab and click Create Fields.

            For an existing Report Definition, this tab lists the available fields and allows you to create formula and filter fields and edit field properties and field formatting.
            Figure 14. Report Definition Fields Tab

            There are three types of fields:

            • Query Fields—Query fields represent a field in a database table. You cannot create or delete a query field.
            • Formula—These are custom fields that compute and return a value.
            • Filter—These are custom fields that can appear on the advanced reporting options tab on the Filter page.

            Click the radio button at the left of each field to select that field for editing.

            You can edit field properties and formatting for all field types.

            Table 2 Rows on This Page

            Field

            Description

            Name

            The database name of the field.

            Display Name

            The name of the field as it appears in the report grid.

            Type

            The type of field (Query Field, Filter Field, or Formula Field).

            Data Type

            The data type of the field.

            Note    You cannot change the data type for a parameter.

            Actions from this page:

            • Create—opens a drop-down menu for selecting the type of field you want to create. Options are Formula Field and Filter Field.
            • Edit Properties—enabled when a row is selected. Expands the page to reveal Edit Field Properties .
            • Edit Formatting—enabled when a row is selected. Expands the page to reveal Field Formatting.
            • Drilldowns—enabled when a row is selected. Click to display the All Drilldowns panel where you can view, create, edit, or delete a drilldown for the field.
            • Delete—enabled when a row is selected, prompts for a confirmation and then deletes the field.

              Note


              You can delete format and filter fields only.
            • Save—saves your changes on all tabs.
            • Cancel—cancels your changes on this tab.
            • Refresh—updates the page to show changes on the Fields tabs.
            • Help—opens online help.
            • X on the tab heading—closes the page.

            Create Filter Field

            Create a filter field if you want to add a filtering value to the advanced filters tab on the Filter page.

            Filter fields reduce the amount of data that is used to generate a report, They do not display as columns in the generated report.

            Procedure
              Step 1   Click Create from the Fields tab and select Filter.

              The new filter field is added to the end of list of Fields. Scroll down the page to locate it.

              Step 2   Select Edit Field Properties and complete the fields to define a required Display Name and Data Type for the field. Complete optional Edit Field Properties fields such as entering the formula syntax, referring to the User Guide for the Cisco Unified Intelligence Center Reporting Application and the online help for explanations.
              Step 3   Click Update to save the field properties.

              Create Formula Field

              Create a formula field if you want the report to include a computed field that does not exist in the list of fields.

              Procedure
                Step 1   Click Create from the Fields tab and select Formula.

                The new formula field is added to the end of list of Fields. Scroll down the page to locate it.

                Step 2   Select Edit Field Properties and complete the fields to define a required Display Name and Data Type for the field.

                Complete optional Edit Field Properties fields, referring to the online help, if required.

                Step 3   Click Update Field to save the field properties.
                Step 4   Scroll up and select Edit Field Properties for the formula field.
                Step 5   Click Update Field to save the field formatting.

                Best Practices for Formulas

                A formula field is an expression that uses operators to perform a calculation on database fields. You can define a formula field to appear as a column in a report. You can also create a formula for a column footer.

                The operators you can use in a formula are the basic arithmetic operators: + for addition, - for subtraction, * for multiplication, / for division, and ( ) parentheses for grouping operands.

                The database field values you can use in a formula can be any data type (Boolean, Date, Decimal, or String).


                Note


                • Each value in a formula represents a single field in the database. For example, to create a formula that adds AbanHold and AbanRing, the syntax must be ${AbanHold} + ${AbanRing}. If you enter ${AbanHold + AbanRing}, the formula fails because there is no database field named AbanHold + AbanRing.
                • Dates must be enclosed in double quotes. The system interprets "10/10/2010" as a date. It interprets 10/10/2010 as a mathematical operation. To find the date difference between two date fields, Unified Intelligence Center provides the formula DateDiff(${DateField1},${DateField2}). This formula compares two date fields and returns the difference of seconds in decimal. If DateField1 is earlier than DateField2, it returns the difference as a negative number.
                • The Key Criteria field cannot be a formula field.
                • A formula cannot use other formulas in its formula.
                • If you enter a formula in a footer, the footer cannot interpret a specific database field. The footer interprets the field name as an array for the database column to which the field belongs. For example, this expression in footer formula ${FIELD1} + 1 is interpreted as ${FIELD1 : FIELDn} + 1.

                Related Information

                Formula Syntax Examples

                Correct syntax

                • ${num1} + ${num2)
                • ${num1} - ${num2)
                • (${num1}) / (${num2}) * (${num3})
                • (${num1}) / (${num2}) / (${num3} * ${num4}) - ${num1}
                • DateDiff(${DateField1},${DateField2})
                • ${str1} + ${str2) You can use + with strings to concatenate them.

                Incorrect syntax

                • ${str1} - ${str2) - You cannot subtract strings. You also cannot multiply or divide them.
                • ${str1} + ${num1) You can add a num and a string only if the database has a null constraint on the number.
                • ${str1 + str2} str1 + str2 is not a field name.

                Edit Field Properties Page

                Actions
                The editable properties for a field vary, based on whether the field is a Query, Filter, or Formula field.
                Figure 15. Report Definitions Edit Field Properties Page

                Field Property

                For Field Type

                Definition

                Name

                All

                The default database name appears in this field.

                Display Name

                All

                By default, the Display Name is the same as the Name.

                You can edit it.

                This is the name for the column header by which this field appears on the report.

                Description

                All

                Enter a description for this field.

                Data Type

                Formula fields

                From the drop-down menu, select the data type for the field. Options are Decimal, String, Number, Date, and Boolean.

                This setting determines the options that are displayed for this field on the Formatting Tab.

                Data Clause

                Query and Filter fields only

                Identifies which column in the dataset is bound to this field (SQL query and Filter fields only). The SQL Parser uses this value when retrieving data from the database.

                Note   

                Do not change the data clause.

                Value List

                Query and Filter fields only

                From the drop-down menu, select the Value List for this field. When a field is associated with a Value List, report users can filter the report with one or more fields from that Value List or its Collections.

                Note    Only fields of type string and decimal can be associated with a Value List.

                Allow to show if invisible

                Query and Formula fields only

                Check this if the field appears on the list of Available fields in the Grid Editor. If this is unchecked, the field does not appear in the Available fields panel.

                Available in Filter

                All

                Check this box to add the field to the Advanced Filters tab.

                Available fields

                Formula

                Select a field from the drop-down list and click Insert Field to insert it into the formula text box.

                Fields on this list appear by Name and not by Display Name,

                Formula

                Formula

                Enter operators to construct a formula for the fields you have selected.

                For reports that use dynamic headers (headers whose content includes dynamic content), provide the SQL field name as part of the header name in the Report definition field properties page. The column name is surrounded by curly braces so that users can easily find dynamic content within header.

                • Update—saves your Edit Properties selections for that field only. To keep the changes, you must save (or Save As) the Report Definition.
                • Cancel—closes without saving your changes.
                Related References
                Related Information

                Field Formatting

                You can edit the formatting for Query fields and Formula fields. There are two format options for a field:

                Format

                Provides a list of default formatting masks.

                The available formats depend on the data type of the field. For example, for numeric values, the drop-down menu offers all possible display formats for numeric values.

                Selecting (Custom) from this list applies the format string supplied in the Custom Format String to the value returned.

                Footer

                The formula to use in the footer. Options are None, Avg, Sum, Count, Min, and Custom Formula.

                Figure 16. Report Definition Edit Field Format

                Actions:

                • Update—saves your Edit Properties selections for that field only. To keep the changes, you must save (or Save As) the Report Definition.
                • Cancel—closes without saving your changes.

                Parameters Tab

                Actions From This Page

                Use this tab to edit and reorder the parameters created from a Stored Procedure or an Anonymous Block. The parameters are used as filters when you generate the report.

                Table 3 Rows on This Page

                Field

                Description

                Name

                The name of the parameter.

                Relative Date Range

                The Relative Date range is specified using Star Date and End Date options. For example, last week, last month, last year, and year to date are some examples of Relative Date Range.

                Display Name

                The display name of the parameter that a user can modify and that appears in the filter page

                Data Type

                The data type of the parameter: Date, Decimal, Boolean, or String.

                • Edit—select a parameter and click Edit to expand the page to display edit fields. Change these fields to modify the way the parameter is presented to the user for input and passed to the database for processing.
                • Reorder arrows—select a parameter and click an arrow to move the parameter up one, to the top of the list, down one, or to the bottom of the list. Doing this changes their order in the report filter.
                • Save—saves your changes on tabs.
                • Cancel—cancels your changes on this tab.
                • Refresh—updates the page to show changes another user might have made to this tab
                • Help—opens online help.
                • X—closes the page.

                If you reorder the parameters, click the Save Order button before leaving the page or your changes will be lost.

                Enabling Relative Date and Days Filtering for Anonymous Blocks and Stored Procedures

                Using the parameter explained below you can populate the filter tabs for reports based on Anonymous blocks and stored procedures.

                To indicate a relation between different parameters, the display name of the parameter must be same.

                Figure 17. Report Definition Parameters Tab

                To enable a Relative Date Range, select Start Date option and enter a display name and select End Date option and enter the identical display name. For example, you can define two parameters: Agent_Login_Start_Date and Agent_Login_End_Date with the same display name: Agent_Login_Date.

                Where Agent_Login_Start_Date is identified as the starting date and Agent_Login_End_Date as the ending date for the Relative Date Range input.

                The related dates in a range should have the same display name and in this case the display name is Agent_Login_Date. The dates appearing under the same display name are grouped together and shown as a relative date range. A single stored procedure or anonymous block can have as many such pairs as required.


                Note


                Do not enter the same display name for more than one pair of parameters. If you enter the same display name for more than one pair of parameters, then Unified Intelligence Center displays only the first two parameters together in one pair. Unified Intelligence Center does not display the third and fourth parameters in a pair. For example, if you provide Agent_Login_Date display name to Agent_Login_Start_Date and Agent_Login_End_Date and then change the display name of Log_Out_Interval_Start_Date and Log_Out_Interval_End_Date parameters from Last_Login_Date to Agent_Login_Date, then Unified Intelligence Center does not display the parameters: Log_Out_Interval_Start_Date and Log_Out_Interval_End_Date in a pair.


                In Relative Date Range, you can also specify an optional days of the week parameter, using Days parameter to the stored procedure or anonymous block. Days parameter is not mandatory. This parameter must be:

                1. Of string type
                2. Prefixed with the same display name as other parameters in the same date range.
                3. Appended with _Days

                For example, for the Days parameter, you can define the display name as AgentLoginDate_Days, which is appended with _Days and with the same display name AgentLoginDate as defined for the two parameters: AgentLoginStartDate and AgentLoginEndDate.

                Related References

                Enable Relative Date

                To enable Relative Date and Days Filtering for Anonymous Blocks:

                Procedure
                  Step 1   Navigate to the Data Source tab in Report Definitions.
                  Step 2   Create two datetime parameters with the name Agent_Login_Start_Date and Agent_Login_End_Date in the anonymous block.
                  Step 3   In the Parameters tab, click Edit.
                  Step 4   Modify the display name to Agent_Login_Date for both the parameters.

                  Both the parameters are paired together.

                  Step 5   Click Save.
                  Note    In the report that uses this report definition, the two parameters appear as a pair.

                  Edit Parameter Properties

                  The first seven fields on this page are the same for all parameters, regardless of data type.

                  Name

                  The name of the parameter.

                  Display Name

                  Change the display (user-friendly) name of the parameter.

                  Description

                  Enter a description for the parameter.

                  Data Type
                  You cannot change the data type for a parameter.
                  Hard-coded value

                  Enter a hard-coded value to be passed as a parameter value when running a report based on an anonymous block or stored procedure report definition. To pass null as a hard-coded value, leave this text box blank and check Pass NULL for empty string. Entering a hard-coded value will hide this parameter in the filter page.

                  Required

                  Check or uncheck to indicate if the parameter is required on the report filter.

                  Pass Null for empty string

                  This field is enabled only if the parameter is not required or has no value. If the parameter is populated, then this field is disabled, and the value is passed. The remaining fields on this page vary, based on the data type.

                  For DateTime

                  Select a date format from the drop-down list.

                  Table 4 For Decimal

                  Value Delimiter

                  Select the character to be placed between each value when a value list is passed to the stored procedure/anonymous block

                  Value List

                  From the drop-down menu, select a Value List to associate this parameter with that list.

                  Quote Values

                  Check this checkbox to surround the parameter with two additional single quotes when the value is passed to the stored procedure/anonymous block. The first single quote is used to escape the second single quote.

                  Table 5 For String

                  Value Delimiter

                  Select the character to be placed between each value when a value list is passed to the stored procedure/anonymous block

                  Value List

                  From the dropdown, select a Value List to associate this parameter with that list.

                  Value Prefix

                  The value prefix defines how the parameter will appear in the filter.

                  Quote Values

                  Check this to surround the parameter value with two additional single quotes when the value is passed to the stored procedure/anonymous block. The first single quote is used to escape the second single quote.

                  Update Field saves your Edit Properties selections for that field only. To keep the changes, you must save (or Save As) the Report Definition.

                  Edit Properties Tab

                  Use this tab to establish or change data settings for the report template.


                  Note


                  For XMPP-based Real-Time reports, under the properties tab, only the following fields are enabled: Description, Author, Performance, and Permissions.

                  Complete this tab as follows:

                  Procedure
                    Step 1   In the Description field, enter text to describe the data settings.
                    Step 2   In the Version field, enter the version of report definition entity currently deployed in Unified Intelligence Center.
                    Note   

                    The version can be composed of decimal points. It should not start or end with a decimal point (for example, X.Y.).

                    Valid version number examples: 8.9 or 11.15.

                    Step 3   In the Author field, enter the name of the template provider that created the entity.
                    Step 4   For reports based on simple queries, select a key criteria field from the Key Criteria Field drop-down list.

                    This field is required if you intend to filter the report.

                    The Key Criteria field is not enabled for reports based on Anonymous Blocks or Stored Procedures.

                    Note   

                    Although users can run a report based on a simple query if the Report Definition does not have a Key Criteria field defined, the report runs with the default filter and might be very large.

                    Step 5   Check the Historical check box if the report is an historical report. Leave this check box blank for real-time reports.
                    Step 6   In the Refresh Rate field, enter the refresh rate for this report in seconds. This is the rate at which the report is automatically refreshed. The minimum refresh rate is 15 seconds for real-time reports and 900 seconds for historical reports. You cannot enter values less than the defaults.
                    Step 7   In the Historical Key Field, enter or modify the historical key field used for the date and time intervals for the report.

                    Although historical reports can run if this field is left blank, the report returns all data for all dates and might be very large. Only fields of date format are available in the Historical Key Field drop-down list.

                    Step 8   In the Default Permissions area, set permissions for My Group.
                    Note    My Group refers to the Report Definition owner's default group. If this default group is the All Users group, the option to set permissions for non-administrative users is disabled. Only administrative users can set permissions for the All Users group.
                    Figure 18. Report Definition Data Settings Tab


                    Unified Intelligence Center SQL Syntax

                    Guidelines

                    • You cannot use comments in an SQL query.
                    • A database query must contain a select statement followed by one or more fields. For example: SELECT [fields] FROM [tables] WHERE [...] This sample query: select CallTypeID, TimeZone from Call_Type_Interval where TimeZone = 240 creates fields CallTypeID and TimeZone.
                    • You cannot use SELECT*, instead you must list all the fields you want to be returned in a SQL query
                    • An Anonymous Block must be a valid SQL statement that returns a result set. It may contain parameters named :[paramName], where a colon is always the first character of the parameter name and [paramName] is a remaining part of the parameter name. The parameter values entered by a user are substituted into the body of the anonymous block in place of the corresponding parameter names.
                    • Informix and SQL Server Stored Procedures are supported. Stored Procedures must return a result set. For Stored Procedures, parameters are used to pass the values when making a stored procedure call to the database to obtain the result set.
                    • Using the Datediff() function in a Where clause causes performance issues.
                    • There can be no unnamed fields in an SQL query. Each field needs an alias.
                    • Alias names must be unique.
                    • Informix stored procedures must contain a returning statement, and for each data type in the returning statement, there must be a corresponding alias specified with the letters AS. For example: RETURNING CHAR(32) AS returnID, CHAR(32) AS returnName, INTEGER AS returnRefreshrate, BOOLEAN as returnHistorical; And not: RETURNING CHAR(32, CHAR(32, INTEGER, BOOLEAN; If a user fails to provide an alias, the field name will just be fieldN, where N is the index of unnamed field, such as field1, field2, and so on.
                    • Every SQL query must have a WHERE clause, even if it serves no purpose.
                    • Informix stored procedure parameter names are prefixed with the 'at' character: @param1, @param2 ...

                    Supported Data Types for Fields and Parameters

                    • BIGINT, DECIMAL, DOUBLE, FLOAT, INTEGER, NUMERIC, SMALLINT, REAL, TINYINT
                    • CHAR , LONGNVARCHAR, LONGVARCHAR, NCHAR, NVARCHAR, VARCHAR
                    • DATETIME
                    • BOOLEAN, BIT

                    Special Keywords for the SQL Parser (with Sample Queries)

                    • ALL (SQL Server or Informix)—SELECT ALL CallTypeID from Call_Type_Interval
                    • DISTINCT (SQL Server or Informix)—SELECT DISTINCT CallTypeID from Call_Type_Interval
                    • TOP (SQL Server)—SELECT TOP 5 CallTypeID from Call_Type_Interval
                    • FIRST (Informix)—SELECT FIRST 5 ID FROM CUICDATASETINFO
                    • UNIQUE (Informix)—SELECT UNIQUE NAME FROM CUICGRID

                    Unified Intelligence Center supports these aggregate functions for both Informix and SQL Server: SUM, COUNT, MIN, MAX, and AVG.

                    In cases where a report definition field is an aggregate function (such as sum(CallsHandled), and that field is a key criteria field or an advanced filter, the supported syntax is:

                    
                    SELECT (fields)FROM [tables]
                    WHERE [...]
                    GROUP BY [...]
                    HAVING [...] optional
                    ORDER BY [...] optional
                    
                    

                    Sample query:

                    select CallTypeID, TimeZone, sum(CallsHandled) as total, avg(CallsHandled) as average
                    from Call_Type_Interval
                    where TimeZone = 240
                    group by CallTypeID, TimeZone
                    having sum(CallsHandled) in(3, 5, 13) and avg(CallsHandled) > 0
                    order by CallTypeID
                    
                    

                    Drilldowns

                    Users with the Premium license and access to Report Definitions can create drilldowns.

                    Drilldowns allow you to create links from one report grid to another so that you can launch a sub-report from within the current report window. You cannot drilldown from or to a chart or a gauge.

                    You can create a drilldown for any field in a report that is not a grouped field. To determine if a field is a grouped field, open Grid Editor, then click Grouping.

                    There is no limit to the number of drilldowns you can define for a field.


                    Note


                    • If several users have added different drilldowns to a field, the field appears as a hyperlink in the grid view. If a field has more than one drilldown associated with it, clicking it opens a popup window where you can select the link you need. If you do not want to drilldown to another report, ignore the drilldown link.
                    • After you launch a drill-down report, you can save it as a separate report.

                    Related References
                    Related Information

                    Create a Drilldown

                    Procedure
                      Step 1   Open the Report Definition for the report in which you want to create a drilldown.
                      Step 2   Click the Fields tab.
                      Step 3   Locate the field from which you want to drill down.
                      Step 4   Click Drilldowns.

                      This displays the All Drilldowns panel. It shows any drilldowns that already exist for that field.

                      Step 5   Click Create.
                      Step 6   Enter a name for the drilldown.
                      Step 7   Click the > arrow next to Report to Drilldown into to display the list of reports.
                      Note    You cannot drill down to or from a report based on an Anonymous Block or a Stored Procedure.
                      Step 8   Select a report by clicking the radio button next to the report name.

                      This opens a panel showing all the fields in that report.

                      Step 9   Highlight a field and click Edit.

                      Doing this presents two options:

                      • Any value—Select this to drilldown into the report that shows any value of the field you selected.
                      • Filter according to the following criteria—Select this to drilldown into the report that shows filtered values of this field. Filter criteria for fields of type string are:
                        • Match field in originating report
                        • Equal to (value)
                        • Not equal to (value)
                        • Matches the pattern
                        Filter criteria for fields of type date are:
                        • Match field in originating report
                        • Match date range in originating report (available only if the originating report is a historical report)
                        • Set date
                        Filter criteria for fields that are associated with a Value List are:
                        • Match field in originating report
                        • Set values
                        Filter criteria for fields that are of type boolean are:
                        • True
                        • False
                      Step 10   Scroll up and click OK.

                      Edit a Drilldown

                      Procedure
                        Step 1   Open the Report Definition for the report in which you want to edit a drilldown.
                        Step 2   Click the Fields tab.
                        Step 3   Locate the field from which you want to drill down.
                        Step 4   Click Drilldowns.

                        This displays the All Drilldowns panel. It shows any drilldowns that already exist for that field.

                        Step 5   Select a drilldown and click Edit.
                        Step 6   Edit the Name for the drilldown.
                        Step 7   Click the > arrow next to the Report to Drilldown into to display the list of reports.
                        Step 8   Change the report by clicking the radio button next to the report name.

                        This opens a panel showing all the fields in that report.

                        Step 9   Highlight a field and click Edit.
                        Step 10   Edit the filter values and click OK.
                        Step 11   Scroll up and click Save.

                        Delete a Drilldown

                        To delete a drilldown:
                        Procedure
                          Step 1   Open the Report Definition for the report in which you want to delete a drilldown.
                          Step 2   Click the Fields tab.
                          Step 3   Locate the field from which you want to delete a drilldown.
                          Step 4   Click Drilldowns. This displays the All Drilldowns panel. It show any drilldowns that already exist for that field.
                          Step 5   Select the name of the drilldown you want to delete.
                          Step 6   Click Delete.

                          Recommendations for Drilldowns

                          Create drilldowns first in cloned (Save As) reports—not in the Cisco stock template reports.

                          You can drill down from grids only. You cannot drill down from—or to—a Chart view or a Gauge view.

                          Be aware that you cannot drill down from grouped fields. To determine if a field is grouped:

                          • Open the Grid Editor for the report.
                          • Click Grouping.
                          • From the Grouping dialog box, determine if the field appears in any of the Grouped By fields.

                          The Drilldown interface does not monitor your selections for Drilldown from and Drilldown into. It is your responsibility to be familiar with the reports and their fields and to understand the business reasons for drilling from one report into another.

                          Related References
                          Related Information

                          Rename a Report Definition


                          Note


                          Not enabled for stock Report Definitions.
                          Procedure
                            Step 1   To rename a Report Definition, right-click its icon and select Rename. This opens a dialog box with a Rename To field.
                            Step 2   Enter a new name (using a maximum of 50 characters) and click Rename to proceed.
                            Step 3   Click Cancel to keep the original name.

                            Delete a Report Definition


                            Note


                            You can delete a stock report definition only if you have the user role of a System Configuration Administrator and a Report Definition Designer, and also have WRITE permissions to that specific Report Definition.


                            Procedure
                              Step 1   To delete a Report Definition, right-click its icon and select Delete.
                              Step 2   Click Yes to confirm the deletion. Click No to cancel. Both Yes and No generate another dialog box confirming your action to delete or to cancel the deletion.
                              Step 3   Click OK to close this dialog box and to complete the action.
                              Note    You cannot delete a Report Definition if it is referenced by one or more Reports or Value Lists.

                              Import a Report Definition


                              Note


                              Not enabled for stock Report Definitions.

                              This page opens when you click Import Definition on the Available Report Definitions page.

                              Use it to import custom Report Definition files saved to your computer. Stock Report Definitions are imported automatically with the stock report templates.

                              You can import a report definition only if you have the user role of Report Definition Designer.

                              Procedure
                                Step 1   Click the Browse button and navigate to the Report Definition file.
                                Step 2   Select the Data Source for this Report Definition from the drop-down menu.
                                Step 3   Click the drop-down menu in the Save To field to select the category in which you want this Report Definition saved.
                                Step 4   Click Import.
                                Step 5   Return to the Report Definitions page and click Refresh. The imported Report Definition displays only after you refresh the page.

                                Export a Report Definition

                                You can export a report definition only if you have the user role of System Configuration Administrator.
                                Procedure
                                  Step 1   Right-click a Report Definition and click Export to open the Export Report Definition dialog box, which gives you the option to rename the Report Definition file. Do not change the file extension.
                                  Step 2   Click OK to open the Windows file download dialog box.
                                  Step 3   Click Save to save the report definition.