User Guide for the Cisco Unified Intelligence Center Reporting Application Release 9.0(1)
Report Definitions
Download this chapter
Report DefinitionsReport Definitions
Download the complete book
User Guide for the Cisco Unified Intelligence Center Reporting Application Release 9.0(1)User Guide for the Cisco Unified Intelligence Center Reporting Application Release 9.0(1) (PDF - 5 MB)
 Feedback

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, 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 Tasks
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 Datatype: 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 Datatype: 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

    Related Tasks
    Related References

    Complete Data Source tab

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

    Database Queries, Stored Procedures, and Anonymous Blocks are ways to retrieve data from a database. Most stock templates are based on Database Queries. Two stock templates are based on Anonymous Blocks. 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.

      The screen repaints based on the Query Type you select.

      Step 2   For all Query Types, select the data source for the 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.

      Figure 2. Report Definition Data Source tab



      Step 3   Complete the tab based on the query type you have selected: Database Query, Anonymous Block, or Stored Procedure.
      Step 4   For Anonymous Blocks and Stored Procedures, open the Parameters tab, where you can edit parameter properties.
      Step 5   For Database Queries, Anonymous Blocks and Stored Procedures, open the Fields tab, where you can edit field properties and create drilldowns.

      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.

      Related Tasks
      Related References

      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 Tasks
        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 3. Anonymous Block example 1 of 6



          • Click Create Parameters:
            Figure 4. Anonymous Block example 2 of 6



          • Set the Data Type:
            Figure 5. Anonymous Block example 3 of 6



          • Click Create Fields.
            Figure 6. Anonymous Block example 4 of 6



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



          • Open the Parameters tab:
            Figure 8. Anonymous Block example 6 of 6



          Related Tasks

          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 9. Stored Procedure example 1 of 6

            • Click Create Parameters:
              Figure 10. Stored Procedure example 2 of 6

            • Set the Data Type:
              Figure 11. Stored Procedure example 3 of 6

            • Click Create Fields.
              Figure 12. Stored Procedure example 4 of 6



            • Open the Fields tab:
              Figure 13. Stored Procedure example 5 of 6

            • Open the Parameters tab:
              Figure 14. Stored Procedure example 6 of 6

            Related Tasks
            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 15. 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.
            Related Tasks
            Related References

            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.
              Related References

              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 to save the field properties.
                Step 4   Scroll up and select Edit Field Properties for the formula field.
                Step 5   Click Update to save the field formatting.
                Related References

                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 16. 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 17. 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.
                Related References

                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.

                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

                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.

                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

                  Related Tasks

                  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 datatype 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 prepended 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
                          Not enabled for stock Report Definitions.
                          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.

                            You can delete a report definition only if you have the user role of System Configuration Administrator.

                            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 System Configuration Administrator.

                            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.
                              Related References

                              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.