Can Crystal grab the time zone from a user's PC?

[njfank]

Hi,

I'm wondering if Crystal can pull the date, time, and time zone from a user's PC and use it in a formula. I'd like to compare date/time values in our Oracle database with the date/time on the user's machine, and need to take time zone into account. Has anyone done this before?

I'm running CR 8.5 on Windows 2000.

Thanks,
Nancy

 

[synapsevampire]

Are the reports run locally or on the server?

The Currentdate function returns the datetime if a report is ran locally, and you can use a SQL Expression (I think that it's select sysdate() from dual) to return the datetime from the Oracle side.

You may need a custom UFL, but first post some technical information about your environment.

Next you'll probably need a means to convert between timezones, check out:

http://www.milletsoftware.com/CUT_Light.htm

And in Oracle 9 you can store the timezone with a timestamp to resolve such things.

Good luck with it.

-k

 

[njfank]

Good questions.

We're actually running Crystal reports through our call tracking tool, Magic. As I understand it, Magic stores dates in the Oracle DB using the time zone of the DB server. Then when a Magic user views the dates on the screen, Magic adjusts them based upon the difference between their workstation's date/time and the date/time on the DB server. This works okay on the screen, but when someone runs a Crystal report, it just pulls the date/time that's been saved in the DB and doesn't do the adjustment. I was wondering if having Crystal do the adjustment would be an option.

Since the reports are run through Magic, getting the date/time from the user's PC is probably not an option. And since we are running Oracle 8, not 9, the time zone is not stored with the date/time.

Hmmm. Maybe I can find a way to make Magic pass the user's time zone.... I'll check into that.

Thanks.

 

[rosemaryl]

Dear NJFank,

I tested this in Magic for you and the assumption is correct that CurrentDateTime on a report will return the application server's currentdatetime if the report is run through Magic. However, if the run the report independently of Magic, in other words from their machine, then the currentdatetime is that on their machine.

A way to use the timezone would be to prompt the user for their time zone.

You can link from the Incident View (or any other view) on the Incident.Seq.Group field to the GMT.Seq.Group field.

Create a Parameter called GMT, populate default values with the GMT.Description field for this example.

Create a formula like the one below which is assuming the db is at GMT, you would have to adjust the hours depending on where your database is located.

select ({?GMT})

case "(GMT +01:00) Amsterdam, Copenhagen, Madrid, Paris, Vilnius" : dateadd("h", 1,currentdatetime )
case '(GMT +01:00) Berlin, Stockholm, Rome, Bern, Brussels, Vienna' : dateadd("h", 1,currentdatetime )
case '(GMT +02:00) Athens, Helsinki, Istanbul' : dateadd("h", -2,currentdatetime )
default : dateadd("h", -5,currentdatetime )
)

That is just an example, I tested it and it does correctly add/subtract the number of hours from currentdatetime. I just did this quickly and if doing in an actual report, I would probably use the GMT offset (displays a number such as 1, -5, 6 and so on) as the value and display the description to the users so they could choose their timezone with out worrying about what the number is supposed to be.

I hope that helps.

Ro

Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[njfank]

Ro,

It sounds like this solution would require: (1) that every report I create and put into Magic would need to prompt the user for their timezone, and (2) that every date displayed on the report would have to be adjusted according to the timezone they enter. Am I understanding it correctly?

When you say that the DB would need to be at GMT, does that mean GMT 0 (as opposed to GMT -6 as it is now)?

If Magic uses the timezone from the client's machine, what is the GMT setting in the staff view for?

Do you know of anyone using Magic in a global capacity that has already dealt with this issue? I have a number of questions around it. It seems to be more of a Magic issue than a Crystal Reports issue, but it may take a Crystal solution to work it out.

Thanks,
NJFANK

 

[IdoMillet]

To avoid the need to prompt the user, you can write your own UFL to return the time zone of the local PC or use a 3rd-party UFL (see list at:

http://www.kenhamady.com/bookmarks.html).

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[rosemaryl]

Dear NJFrank,

Sorry for the delay, I am working through the weekend at a client site and this is the first chance I have had. The answers to your questions required some length to explain, so I apologize for that in advance.

First off, I don't think that Ido's idea, while a very good one, will work in this instance as the report is not being run locally when you run it from withing Magic, rather it is being run on the MagicTSD application server and will return the timezone of the application server which is mostly likely the same as the DB's.

Also, please note that all my examples below assume that you are using MS SQL Server 2000 as MagicTSD's db backend rather than Oracle. My recommendations will also work with Oracle, although syntax may be slightly different.

Your other points:

<<(1) that every report I create and put into Magic would need to prompt the user for their timezone.>>

Hmmm, a good point and on second thought this would tax the user base so we need to automate. I think a UDF as explained further down would do the trick.

<<(2) that every date displayed on the report would have to be adjusted according to the timezone they enter.>>

Well, whatever solution is used is going to have to transform the date fields to the user's local time. So, the answer is yes.

<<When you say that the DB would need to be at GMT, does that mean GMT 0 (as opposed to GMT -6 as it is now)?>>

I was simply stating that my example formula assumed GMT 0, to make it easy on myself for my example formula. Any formula would have to account for the db's actual GMT.

Global Magic Clients question:

I have many clients running MagicTSD globally, however none have requested a solution to this problem before. Interestingly, about the same time you posted your question here, an NAI/Magic Solutions employee(vendor of the app) contacted me with the same question on behalf of one of their clients (maybe it was for you <smile>).

Do you have a SQL DBA or good SQL skills? If so, my suggestion is that the best way to do this and make it easiest for handling on reports is to create a UDF (User Defined Function) that will perform the transofrmation on any date field passed to the UDF.

You can call a UDF in a SQL expression in Crystal. As a matter of fact, a UDF is a very flexible Magic solution because it can be used in Crystal, in a caluclated field in Magic's DBADMIN tool, and in Magic's Business Rules tool!

In a Crystal SQL Expression, the syntax to call the udf would look something like this (assuming you named the udf XFORMDATE and it was owned by [_dbo_]:

(select [_dbo_].XFORMDATE(Incident.&quot;Open Date & Time&quot;,{fn User()}))

Passed to the UDF above is the date field you want to transform, and the user who logged into the report via the odbc function {fn User()}.

The UDF could then select the staff person's GMT offset for their timezone.

I can easily do that in a SQL Expression and can also get the timezone of the database, and show an example of both below. However, since there are so many date fields in reports, by creating a udf it would simplify the whole process.

To create a SQL Expression, in Crystal, select Insert/Field Object/SQL Expression, provide a name and then copy the statement.
Here is the SQL expression to get the DB's timezone: (we have to cast it because it is stored in an Ntext field), I named my dbzone:

(Select Cast(VALUE as varchar(4)) from [dbo].SMSYSFLAGS WHERE NAME = 'namDBTimeZone')

In your database's case it would return -6.

Here is an example to select the logged in report runner's GMT Offset (notice that I use base tables rather then views for increased performance ... the Personnel table is the base table of the View Support Staff):

(select [GMT_OFFSET]
from [_SMDBA_].[_GMT_]
where [_SMDBA_].[_GMT_].[SEQUENCE] =
(select[SEQ_GMT]
from [_SMDBA_].[_PERSONNEL_]
where CODE = {fn USER()} ))

This would return -5 for a staff person located in NY. NY is GMT -5.

So, you could then either create formulas for each datefield in your report using the returned values to do a dateadd or better create a udf as I indicated.

And, finally cause I just noticed I missed this part of the question:

<<If Magic uses the timezone from the client's machine, what is the GMT setting in the staff view for? >>

The GMT setting in the staff view is used by the application's code, which uses the GMT offset to transform the &quot;DISPLAY&quot; on the forms of all datefields stored in the database to the timezone as set in the regional settings on the user's local machine.

When you run a report from Magic, the data is pulled locally directly from the database where all datefields are stored in the DB's timezone. If it wasn't done this way, then when you created an Incident in NY and a few minutes later a user in California pulled it up to close the call, the save would fail with the error that the Close datetime was earlier then the Open DateTime. Storing the data at one timezone means that these types of problems won't happen.

I hope that this information has helped you.

Regards,

ro

FYI: I will be traveling back home my client in Baltimore tomorrow, so I won't see messages until tomorrow evening.


Rosemary Lieberman
rosemary@microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

 

[njfank]

I'm going to see if I can implement your solution. Thanks, Ro!

NJFANK