3 Replies Latest reply on Jan 24, 2020 11:23 AM by Alvaro Paronuzzi

    Calculated field in BMC Smart Reporting

    Alvaro Paronuzzi
      Share This:

      Hello Smart Reporting experts,

       

      I am trying to reproduce the following calculated field in Smart Reporting:

       

      case when ( dbo.fn_adjusted_date(  HPD_HELP_DESK.Closed_Date) ) is null then datediff(dd,( dbo.fn_adjusted_date(  HPD_HELP_DESK.Reported_Date) ),( getdate() )) else datediff(dd,( dbo.fn_adjusted_date(  HPD_HELP_DESK.Reported_Date) ),( dbo.fn_adjusted_date(  HPD_HELP_DESK.Closed_Date) )) end 

       

      In words the Custom_Field value must be the Age of the Incident (days):

       

      if Closed_Date = null

        Custom_Field = Current_Date - Reported_Date

      else

        Custom_Field = Closed_Date - Reported_Date

       

      How can a similar calculated field be implemented Smart Reporting?

      My collegue already made a few attempts but we are not getting the exact result yet:

      Anteprima immagine

       

      Thank you in advance for any help you can provide.

       

      Kind Regards,

      Alvaro Paronuzzi

        • 1. Re: Calculated field in BMC Smart Reporting
          Alvaro Paronuzzi

          I need to do something like this, but of course it is not working because of errors related to types used in this formula:

          Thank you in advance for your help!

          • 2. Re: Calculated field in BMC Smart Reporting
            Sinisa Mikor

            Hello Alvaro,

             

             

            there are several ways to construct calculation you require, here is one which doesn't require advanced options:

             

             

            CurrentDate() is available as a replacement for get_date() both as Pre-Defined Formula Type and as Freehand SQL Formula Type (the latter may be disabled in your Smart Reporting installation), so you can use that to define first calculated field.

            After this, define two more calculated fields:

                 one (Until NOW) to calculate Current_Date - Reported Date, another (Until Closing) to calculate Closed Date - Reported Date

            and use them both in final calculated field as CASE WHEN Closed Date IS NULL THEN Until NOW ELSE Until Closing END.

            1 of 1 people found this helpful
            • 3. Re: Calculated field in BMC Smart Reporting
              Alvaro Paronuzzi

              Hello Sinisa,

               

              Thank you for your response.

              I have defined a few calculated metrics and then applied the when cases to the definition of the resulting calculated field:

               

               

              It seems to work but I still have to check the results in details.

               

              Thanks for the input!