12 Replies Latest reply on Aug 2, 2019 11:44 AM by Sinisa Mikor

    BMC Smart Reporting - HOUR function

    Stefan Srbljanin
      Share This:

      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.

                          2 of 2 people found this helpful
                          • 10. Re: BMC Smart Reporting - HOUR function
                            Sinisa Mikor

                            You could use modulo 24 on that calculated field to keep values between 0 and 23, or decide to avoid hour function and use direct conversion using (hms_value - ms_value) / ms or (`HPD:Help Desk`.`Submit Date`% (60 * 60 * 24)) - (`HPD:Help Desk`.`Submit Date` % (60 * 60))) / (60 * 60).  Using the latter method does not take into account daylight savings (and, as mentioned before, I'm not certain that HOUR function does it either), so results will be offset by an hour if your (AR) system does during periods when clocks are running one hour ahead .

                            • 11. Re: BMC Smart Reporting - HOUR function
                              Conor McGinn

                              The HOUR function in Freehand SQL returns one hour less than actual hour because the Freehand SQL uses the database timezone to calculate the hour, and the database timezone is set to GMT. If you set the GMT timezone in your Smart Reporting profile settings then you will see the correct Hour in the report.

                               

                              If you don't want to set the GMT timezone in your profile then you can update your formula as Matt Stringham suggests, i.e. (HOUR(`CHG:Infrastructure Change`.`Submit Date`))+1

                               

                              BMC advised me that from 19.02 version onwards, Smart Reporting Freehand SQL uses user profile timezone instead of database timezone. Hence you will not face this issue on 19.02 release of Smart Reporting.

                              1 of 1 people found this helpful
                              • 12. Re: BMC Smart Reporting - HOUR function
                                Sinisa Mikor

                                Matt's and your suggestion do correct difference between Create_date and HOUR columns on Stefan's first screenshot, but if you look closer at the second (and Stefan may not have done so because he marked all rows when he shouldn't have), you should notice that difference is either one hour or none at all so adding an hour will not always help.  In that example, values match on dates between November and March, so I suppose that the difference is related to daylight savings time.   Matt's example is set in June when adding an hour yields correct results because server doesn't use DST, but user (Matt) does; if I'm correct, Matt's corrected time should be one hour late during "winter".

                                1 of 1 people found this helpful