Report Creation: Leveraging timezone offsets for your current browser

Version 1

    Details

    HEAT native storage in the database, holds date/time fields in UTC value.  The interface automatically changes that in a date/time field to the correct date/time for your browser's local timezone.  


    This does not happen automatically for reports, however. You must account for this offset yourself, and hopefully in a dynamic way, so that you do not have problems at daylight savings time.

    This help article discussed how to do that on the surface, but many customers may desire further instruction.

     


    Resolution

     

    1: Your report needs to have a text-type parameter, “BrowserTimezoneOffSet”, with a default value of “$(CurrentBrowserTimezoneOffset())”:



    2: You will also have a parameter called simply “OffSet” with no default value.


    3: Your query needs to use these values appropriately:


    Likely, you are querying some data, based on some datetime field in SQL being before and/or after a start/stop time.  In that case, you probably have parameters where the user of the report selects a StartTime and EndTime also.


    For a simple example, assume you’re returning ever value in the Incident table, based on the incident being created within the StartDate and StopDate that the user selects.  Ideally, your query will look like this:


    Select * from Incident

    where CreatedDateTime>=DATEADD(mi,@OffSet,@StartDate)

    and CreatedDateTime<=DATEADD(mi,@OffSet,@EndDate)

     

    HOWEVER, the above may not work, because SSRS may inherently try to understand your @OffSet variable as a character type, which is not valid. If that happens…



    Then the resolution is to cast them as explicit input types:


    Select * from Incident

    where CreatedDateTime>=DATEADD(mi,cast(@OffSet as numeric),@StartDate)

    and CreatedDateTime<=DATEADD(mi,cast(@OffSet as numeric),@EndDate)

     

    You should now be able to use “Refresh Fields”, and ensure your query is validated. However, follow through step 4, before clicking “Ok” to create your data set!


    4: You will need to go to Parameters, and opt to give the OffSet parameter a new function value:



    “Code.GetBrowserTimezoneOffsetAsInt(Parameters!BrowserTimezoneOffSet.Value)”