6 Replies Latest reply on May 20, 2019 5:48 AM by Sinisa Mikor

    How to build a Handling Duration calculated field?

    Thomas Miskiewicz
      Share:|

      Hi there,

       

      I need to add a Handling Duration field to one of my reports which is defined as TIMESTAMP - Submit Date.

       

      I created:

       

      a.) a calculated field Current Date

       

      CurrentDate.jpg

       

      b.) a calculated field Handling Time

       

      Handling Time.jpg

       

      And even though the resulting SQL    (CurrentDate()-`HPD:Help Desk`.`Submit Date`) makes sense to me no data shows in the actual report and the formatting options offer no Hours and Minutes selection

       

      HandlingDurationFormatting.jpg

       

      What am I missing?

       

       

      Thomas

        • 1. Re: How to build a Handling Duration calculated field?
          Sinisa Mikor

          Hi Thomas,

           

          you cannot subtract dates directly; instead of defining calculation field for Current Date and another for Handling Time, define calculated field Handling Time with:

           

          Formula Type               Pre-Defined

          Functions                      Date Difference - In Seconds, ARJDBC

          Resulting Field Type     Metric

          Values

               Start Date                Submit Date

               End Date                 Current Date + 0 Days

           

          Result will be in seconds, but you can use column formatting to make it humanly readable. For example:

               Format                Hours and Minutes     (these are units used in output)

               Unit Selection     Seconds                     (these are units used in calculated field)

          1 of 1 people found this helpful
          • 2. Re: How to build a Handling Duration calculated field?
            Thomas Miskiewicz

            Unfortunately that doesn't work either. When I do it the way you described a get a negative values as expected. If I swap Start and End Date I get a positive number but still wrong data.

             

            Predefined-field.jpg

             

            Predefined-field-sql.jpg

             

            Formating.jpg

             

            Calculation.jpg

            • 3. Re: How to build a Handling Duration calculated field?
              Sinisa Mikor

              It seems I was a bit hasty and assumed Current Date substitutes $TIMESTAMP$, which it doesn't; when used in filter it is possible to define value as Current Date - 0 Seconds, but in Date Difference, accuracy of Current Date is limited to days at best.  In your example (Current Date + 0 Days) translates into '2019-05-14 00:00' thus date difference is negative and equal to time part of Submit Date, or invalid if Start Date and End Date aren't swapped; if you were to use Current Date + 1 instead, difference would always be positive, but it would still be offset by the number of seconds left between now and the end of current day.  This could be handled by datediff if it were possible to select calculated flelds as either Start Date or End Date.

               

              Could you replace DATEDIFF('ss', 'HPD:Help Desk'.'Submit Date', '2019-05-14') with DATEDIFF('ss', 'HPD:Help Desk'.'Submit Date', CurrentDate()) manually?

               

              P. S.  You could get desired results even with this report in special circumstances -- reports run at exactly midnight will give correct results since there will be no offset.

              • 4. Re: How to build a Handling Duration calculated field?
                Thomas Miskiewicz

                Thank you Sinisa, not quite sure who things can be replaced manually...

                • 5. Re: How to build a Handling Duration calculated field?
                  Sinisa Mikor

                  If one of options offered in Formula Type of calculated field's definition is "Freehand SQL", select it and simply paste in replacement expression; if that option is not available, and it seems that it often isn't, Mohammad Rehman's answer to https://communities.bmc.com/message/754648#754648 might help.

                   

                  Alternatively, it might be possible to alter .jar which defines available units of accuracy of Current Date in Date Difference and add missing ones (hours, minutes, seconds).

                  • 6. Re: How to build a Handling Duration calculated field?
                    Sinisa Mikor

                    To summarize, in order to achieve your goal, you should create calculated field using:

                     

                    Formula Type                         Freehand SQL

                    The Freehand SQL builder    DATEDIFF('ss', `HPD:Help Desk`.`Submit Date`, CurrentDate())

                     

                    There are two possible issues preventing you from doing that:

                    1. Freehand SQL is not shown in Formula Type menu -- siadmin has to enable Allow Freehand SQL Calculated Fields in Administration => Admin Console => Roles => Admin => Report Builder
                    2. Calculated field can not be validated/saved, "Ambiguous column name 'DATEDIFF'" warning is shown instead -- click on pencil icon in lower left corner (Edit View), click on table where Start Date is coming from (in your case, that would be HPD:Help Desk), click on its gear icon, mark it as Mandatory (there is a check box just above Columns) and Publish View to save the change.

                     

                    Once these two issues are resolved, you'll be able to enter DATEDIFF expression and save Calculated Field.