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."Open Date & Time",{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 "DISPLAY" 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,
Microflo provides expert consulting on MagicTSD and Crystal Reports.
