2 Replies Latest reply on Jan 16, 2019 11:43 AM by Gerard Oakes

    Smart Reporting Cross Table Builds and Calculated Fields

    Gerard Oakes
      Share This:

      We're trying to build an Incident Summary report that needs to use a Cross Table design based on the number of Incidents by Service Type and Date Differences from the Submit Date to the Last Resolved Date.

       

      First, a Dimension field called "Resolved Time in Days" was created with the following setup:

           Formula Type:             Pre-Defined

           Functions:                    Date Differences - In Days, ARJDBC

           Resulting Field Type:   Dimension

           Values:                        Start Date:     Submit Date

                                                End Date:     Last Resolved Date

       

      Second, an integer Calculation Field was created with the following setup:

           Calculated Field Name:     ">= 1 Day"

           Formula Type:                    Simple

           Formula:                              CASE WHEN "Resolved Time in Days" <= 1 THEN COUNT (Incident ID) END

       

      Third, an attempt to create the second integer Calculation Field with the following setup:

           Calculated Field Name:     "2 Days"

           Formula Type:                    Simple

           Formula:                              CASE WHEN "Resolved Time in Days" >= 2 AND "Resolved Time in Days" < 3 THEN Count (Incident ID) END

      When the Validate button is selected to check the syntax, the error message "This type cannot be displayed." appears, and only the Cancel button is available to escape from the editor.  The syntax does not have the double quotes around it, I just put these I this message to emphasize the Dimension or field name that is being used for the syntax build.

       

      What is the proper syntax to build the secondary Calculated Fields in Smart Reporting when trying to build fields to be displayed and used in the Cross tables and report graphics?

       

      I could not find any information on BMC Docs, Communities or administration guides on how to build these Dimensions and Calculated Fields for Smart Reporting design, especially when trying to build Cross Table functionalities.

       

      If you have a document or link where this information can explain how to do these complex designs, please do share.

       

      Best Regards,

      Jerry Oakes

        • 1. Re: Smart Reporting Cross Table Builds and Calculated Fields
          Shubham Verma

          Hi Gerard,

           

          There is a simpler way to build a cross-tab report for incident counts. Try the following

          1) You got the first part right. - "Resolved Time in Days"

          2) Create a new Calculated Field with name "Resolved Age Bucket" with following Calculations

               -  CASE  WHEN "Resolved Time in Days" <= 1 THEN "1 Days"

                              WHEN "Resolved Time in Days" >= 2 AND "Resolved Time in Days" < 3 THEN "2 Days"

                              WHEN "Resolved Time in Days" >= 3 THEN "More than 3 Days"

                   END

          3) Rather than putting this filed in Columns section, drag this age bucket field to Rows section of report builder

          4) Drag and Drop Incident Number Field to Column Section and use drop down to select Aggregation as count distinct.

          5) Add another dimension field  in Columns against which you need to build cross-tab. e.g. "Priority"

          6) Play around with Rows and Columns to get your desired report just make sure you add at least one metric field in either of them to build cross tab.

           

          Regards,

          Shubham

           

          3 of 3 people found this helpful
          • 2. Re: Smart Reporting Cross Table Builds and Calculated Fields
            Gerard Oakes

            Shubham,

             

            The details of your instructions were very helpful and I was able to correct the syntax error condition to move forward on the custom report.

             

            I have the table and Pie Chart looking good and valid with the right aggregated counts in the columns, rows, column totals and row sub totals, while splitting up the report in sections for the Assigned Support Group.

             

            Since this is my first complex report with Cross Tables, Charts and multiple Dimensions with Calculated fields, as well as the aggregations, do we have any detail documentation to show how we can change the colors on the Pie Chart based on the Resolved Age Buckets; remove the field name from the column and row headings; custom the naming of the columns for the values pulled from the data, such as naming column "User Service Restoration" to just say "Restoration"; and updating the report Title and Description?

             

            I tried to update the Title and Description for the report through the Report edit option, and the changes stay on the Edit window, but they don't update in the file folder where the Report is displaying.  Is this a defect with version 9.1.03?

             

            Thank you for all the assistance.