1. 

   jcstevens, Member

   I have a user parameter named inTimeZone, for which I created a parameter query with the following:
   

   select '-4', 'EDT' as TZ
   union select '-5', 'EST' as TZ
   union select '-5', 'CDT' as TZ
   union select -6, 'CST' as TZ

   So when the user runs the report, they choose from the second column and I use the UTC offset in the first column to offset the UTC time so that our db's UTC timestamps can be converted to the user's TimeZone. MY DBA WILL NOT LOAD THE MYSQL TIMEZONE tables to our schema for various reasons, so I can't refer to text timezones like 'EDT', 'CST' for conversion.

   1. I'd like to find a better way to capture the client's TimeZone offset for on-the-fly conversion, but everything I find assumes that I know javascript, and I've been unable to make this work.

   2. I'd like to use the TZ column in my parameter query in a column's title so the user ends up seeing the column header of 'Time Zone: EST', 'Time Zone: CDT', etc. I've been able to get 'Time Zone: -5', for example, and I can CASE that result back to a text but this means that I can't differentiate between EST and CDT to display to the user reliably. Is it possible to utilize the second column of my parameter query as text in a column title?

   Thanks,
   Johnny
2. 

   myDBR Team, Key Master

   Easiest solution is to create a parameter table for the timezones and user's timezones. Record the user's timezone selection when user runs the report which contains a timezone selection. You can then use the timezone as a default for the user.

   --
   myDBR Team
3. 

   jcstevens, Member

   My colleagues continue to tell me that javascript is an easier way to find the client's timezone offset, but I can't seem to get that working in my reports. I've tried the following:
   select 'dbr.javascript', " function myFunction(inTimeZone) { var d = new Date(); var n = d.getTimezoneOffset(); }";

   select inTimeZone as Offset

   and

   select 'dbr.javascript', " { var d = new Date(); var n = d.getTimezoneOffset(); }";

   select n as Offset

   Anything that I try results in a value not found in the field list. I believe that the javascript snippet I'm using should result in n providing the number of minutes the client is offset from UTC, but how do I get the value of n to show in my report?
4. 

   myDBR Team, Key Master

   The JavaScript code in the report runs in client's browser after the report has been executed in the server and after the results haven been sent to user. This is why you cannot mix these two in same report.

   --
   myDBR Team
5. 

   jcstevens, Member

   The good news is that I don't need the inTimeZone offset until the linked report. What convention should I use to pull the resulting value into the linked report?
6. 

   myDBR Team, Key Master

   If you are getting the user's browser's timezone info, the data is considered as user enterable data from myDBR point of view. You can create a report which has a timezone integer parameter and initialize it in report parameter page with following JavaScript:

   <script type="text/javascript"> $(document).ready(function() { $('#u1').val( new Date().getTimezoneOffset() ); }); </script>

   Where u1 is a reference to your parameter (u1=first parameter).

   --
   myDBR Team
7. 

   jcstevens, Member

   I need some help in understanding what you are saying. My initial report:
   DROP PROCEDURE IF EXISTS sp_DBR_Cases_by_Group1 $$ CREATE PROCEDURE sp_DBR_Cases_by_Group1( inCoreGroup varchar(30), inCompleteFrom date, inCompleteThru date, inTimeZone varchar (3) ) BEGIN

   select 'dbr.report', 'sp_DBR_Case_Detail_by_oid', 1, 'inCaseID=CaseID', 'inCaseOID=cid', 'UTCOffset=Offset'; select 'dbr.parameters.show'; select 'dbr.hidecolumn', '2','3'; select 'dbr.colstyle', 'CaseID', mydbr_style('hyperlink');

   set profiling=1; select distinct(vcdi.CaseID), vcdi.oid as 'CaseOID[cid]', inTimeZone as 'Offset', vcdi.Description, vcdi.Status from coredata_read.VIEW_Case_Detail_Info vcdi inner join coredata_read.caseRoot_milestone crmi on vcdi.oid=crmi.oid where vcdi.oid = any (select cra.oid from coredata_read.caseRoot_action cra where cra.actionPartyClean = inCoreGroup) and vcdi.oid = any (select crmi.oid from coredata_read.caseRoot_milestone where crmi.milestoneType='complete') and crmi.milestoneDateTime between date_sub(inCompleteFrom, interval 1 day) and date_add(inCompleteThru, interval 1 day) order by vcdi.CaseID; show profiles;

   END $$

   This requests a choice from the user for TimeZone, coded as follows in my parameter queries:
   select '-4', 'EDT' as TZ union select '-5', 'EST' as TZ union select '-5', 'CDT' as TZ union select -6, 'CST' as TZ

   If I'm replacing the user-requested inTimeZone with an internally calculated inTimeZone from the javascript you sent so that I can pass inTimeZone (javascript-calculated) into linked report sp_DBR_Case_Detail_by_oid?

   Thanks in advance,
   Johnny
8. 

   myDBR Team, Key Master

   In order to prepare yourself that your timezone popup does not contain all the value that can be found in user's browser, you can do:

   <script type="text/javascript">
   
   $(document).ready(function() {
   
     var orig = $('select[name="u1"]').val();
   
     $('select[name="u4"] option').removeAttr('selected').each( function() {
   
       if ($(this).val() ==  new Date().getTimezoneOffset()) {
   
         $(this).parent().val($(this).val());
   
         $(this).attr('selected','selected');
   
         return false;
   
       }
   
       $('select[name="u1"]').val(orig);
   
     });
   
   });
   
   </script>
   
   

   --
   myDBR Team
9. 

   jcstevens, Member

   Okay.. I now have a Parameter Query named TZJavaScript set as a Popup type. When I add TZJavaScript in the Edit Report area for field inTimeZone, my report errors out on the initial screen. If I change TZJavaScript to a Default type and use it that way, I get a different error.

   Please tell me what to do with the understanding that I don't code in javascript for a living. Long-story-short... I am creating Report A linked to Report B. In order to convert our UTC timestamps to the user's local TimeZone in Report B, I need Report A to gather the user's browser TimeZone as an integer offset to UTC time. How can I do this, in layman's terms?
10. 

    myDBR Team, Key Master

    How is your parameter query TZJavaScript defined? It should return possible values of the timezones (offset and visible value for the user.

    The JavaScript code above goes into Edit Report-> Help/JavaScript field where it selects the correct value from the TZJavaScript selectlist. The code above asumes that your example above where the timezone is the fourth parameter ('u4' in code).

    --
    myDBR Team
11. 

    jcstevens, Member

    My current TZJavaScript parameter query is defined as: select '-4', 'EDT' as TZ union select '-5', 'EST' as TZ union select '-5', 'CDT' as TZ union select -6, 'CST' as TZ. This has its own issues. If I pick EST, the remembered value sets itself to CDT since they both return -5, but CDT is first alphabetically. Further, this requires the user to select something. I don't want the user to have to select anything.

    I would like something global that will automatically pull the user's browser timezone, convert that to an offset from UTC, then I can use that offset in fields with UTC timestamps.
    ex: (select convert_tz(dbTimeStamp, '+00:00', concat(UTCOffset, ':00')) as 'Local TimeStamp')

    I would think this should be an automatic parameter like inLogin, inIPAddress, etc. If that can't be done, how can I set something up to calculate automatically?
12. 

    myDBR Team, Key Master

    If you want to keep several names for same time offset, you can show them as one selection:

    select '-5', 'EST/CDT' as TZ

    If you want to separate them, you should store the text (EST for example) instead of the value.

    The reason for timezone is not in an automatic variable is that it is a client side function and is not included in browser header info (like IP-address or user agent). This would mean that it could not be used when user access reports directly.

    If you have reports whose time you wish to show in user's own timezone, the easiest way is to have a preference table for user where user can set the preferred timezone (you can use the browser timezone info to help user to choose the correct one). If the user would not yet have entry in the preference table, you could notify user to set the timezone. Figuring out the browser's timezone each time you call a report makes life more complicated as it should be.

    --
    myDBR Team

Reply

You must log in to post.