9 Replies Latest reply on Jun 26, 2019 1:47 AM by Stefan Srbljanin

    BMC Smart Reporting - HOUR function

    Stefan Srbljanin
      Share:|

      Hi all,

       

      The hour function does not work as intended (or it does, but the working use goes by me than).

      When I cast timestamp with DATE and TIME to HOUR I get an TIME factor - an hour, which does not compute in correct data when listed to client.

       

      Just a little clarification on why I need this.

      I need to show the number of incidents and such information on hourly basis.

        • 1. Re: BMC Smart Reporting - HOUR function
          Ganesh Gore

          I need to show the number of incidents and such information on hourly basis.

          In that case, you can use predefined filters.

          Just add date field in the filter -> Advanced Settings -> Defined Value -> Predefined Period as shown in the attachment.

          SR_hrs.jpg

          1 of 1 people found this helpful
          • 2. Re: BMC Smart Reporting - HOUR function
            Stefan Srbljanin

            With this I'll get the number of incidents just in the last hour.

            I need to see an hourly track through a day, so for every hour on hour I need number of incidents.

            • 3. Re: BMC Smart Reporting - HOUR function
              Sinisa Mikor

              Are you looking for a way to report something similar to how many incidents were submitted during each hour?  You could define calculated field Submitted hour as HOUR(`HPD:Help Desk`.`Submit Date`) and make a simple test report from HPD:Help Desk with only two columns -- Count of Incident Number (simply add Incident Number and set aggregation to Count) and Submitted hour.  This report will show number of incidents during each hour of any day; you could use another calculated field, Submitted day, as DATE(`HPD:Help Desk`.`Submit Date`) and put it in sections -- that will show you separate subset of numbers of incidents submitted per hour for each date.

               

              Here's how first suggestion's SQL expression would look like:

               

              SELECT DISTINCT

                COUNT(`HPD:Help Desk`.`Incident Number`),

                HOUR(`HPD:Help Desk`.`Submit Date`)

              FROM `AR System Schema`.`HPD:Help Desk`

              GROUP BY

                HOUR(`HPD:Help Desk`.`Submit Date`)

              • 4. Re: BMC Smart Reporting - HOUR function
                Stefan Srbljanin

                That is all well and good, but as I mentioned that does not work as intended.

                So instead of getting (for the first row) 12, I'm getting 11.00.

                And for the hours that are after midnight I'm getting 23.00 (11PM)

                • 5. Re: BMC Smart Reporting - HOUR function
                  Sinisa Mikor

                  Do dates in first column include daylight savings?  Hour columns might be showing time in UTC -- that might explain "drifting"; I assume hours are rounded down.

                  • 6. Re: BMC Smart Reporting - HOUR function
                    Ganesh Gore

                    Abhay Bagalkoti  Can you please check this and provide your inputs here?

                    • 7. Re: BMC Smart Reporting - HOUR function
                      Ganesh Gore

                      Moved to Smart Reporting place so that Smart Reporting experts can provide the solution.

                      • 8. Re: BMC Smart Reporting - HOUR function
                        Matt Stringham

                        I don't know if this is the best work around but I started to work on a report similar to this, "Track the number of Incidents created per hour over the past 24 hours".  The numbers looked correct but when I looked at the data in Remedy the hour was off by 1 hour.  Here is what my table looks like (rough draft - it will look better later):

                        Before I made the fix (See below) the numbers were off by 1 hour.  What was showing was 5 total tickets on 6/25/19 at 4am but it should have been 5 incidents at 5am.  Anyway here is the solution:

                        At the view level create a calculated field using Freehand SQL as HOUR(`HPD:Help Desk`.`Reported Date`) + 1 - saved it as "Reported Hour"

                        In my environment it accepts the +1

                        In your case you can just use "Submit Date" rather than reported date.

                        I added the "Reported Hour" to my report and the numbers are matching up correctly for the Hour now.  This must be an issue with yellowfin.

                        1 of 1 people found this helpful
                        • 9. Re: BMC Smart Reporting - HOUR function
                          Stefan Srbljanin

                          This is all good but since the Call Center works 24h this is not an ideal solution.

                          I'm having incidents that where created between 23:00 and 24:00 (11PM and midnight) as 23, with adding 1 hour on it I'm getting 24, thus creating peaks at 24th hour which, in this case is incorrect.

                          1 of 1 people found this helpful