Data Time Zone issue in Crystal Reports

[Former Member]

Hi,

I am using Crystal Reports Version 12.3.0.601 and I have been struggling with a time zone issue. I am writing report to be run with a web application. The server is in Sweden and the client is in London. I wanted to footer to have the date and time of the client at run time. I have a reserved internal parameter called "timezone_bias" that is passed through.

I attempted to use the Special Field "Data Time Zone" and found that it was only correct when the time has not been adjusted for daylight savings. When I noticed that issue, I began working on the solution below:

I have the following formula that works when we are not in daylight savings:

ShiftDateTime(DataDate+DataTime, DataTimeZone, {@ActiveTimeZoneBias})

Where ActiveTimeZoneBias = "XXX," & ToText({?timezone_bias},"#") & ",XXX"

The following formula works when we are in daylight savings:

ShiftDateTime(DataDate+DataTime, DataTimeZone, {@ActiveTimeZoneBias 2})

Where ActiveTimeZoneBias 2= "XXX," & ToText({?timezone_bias},"#") & ",XXX,0"

The issue with using those formulas is the fact that the schedule for daylight savings varies by country. I would then need to write a custom formula per run time location which would become a large task.

Does anyone have any advice for solving this issue?

Elyssa

[Former Member · Accepted solution]

Firstly, I assume you want current server time to show in the footer

There are several options

Option 1: Uses Crystal Report build-in functions to automatic convert

Auto detect server timezone (British time) and users' timezone (Sweden time)

ShiftDateTime ( CurrentDateTime, PrintTimeZone, CurrentCEUserTimeZone)

Option 2: Same as above but hard code the timezone

ShiftDateTime ( CurrentDateTime, "UTC,0,BST", "CET,-60,CEST")

Option 3: Uses database timezone function

Create a SQL Expression in Crystal Report designer, and enter following Oracle syntax (MS SQL Server 2005 and earlier does not support timezone)

( Select From_TZ( Current_Timestamp, 'Europe/London') AT Time Zone 'CET' As TzValue From Dual )

Then in the report footer (or other desire place), insert the SQL Expression as part of the display text

Reference:

http://singcheong.blogspot.ca/2012/05/businessobject-dynamic-timezone.html

[Former Member · Accepted solution]

Hi Sing-Cheong,

Thank you for replying to my questions, but I think I may have simplified it slightly. The issue is two fold - one is for this situation which I do have a fix for (although it is from using custom formulas rather than using Crystal's Data Time Zone). My issue is that I may have to run that report next in Boston and then in California, and I need the report to always display in the footer the client's date and time.

a) Do you know how to make Data Time Zone work as I am hoping?

b) Is there a different generic fix that can work for detecting and displaying the client time zone?

Thank you for your time,

Elyssa

[Former Member · Accepted solution]

Hi Elyssa,

Both Option #2 and #3 hard coded the target timezone as Sweden regardless you from Boston, or California.

In addition, DST is automatically handle by ShiftDateTime as you see I specified 2 abbreviation. Internally, it keeps track when DST start and end. Unlike many others solution where they don't handle DST, and hard coded the hour.

For Option #3, you must run Oracle 10g database and above. Again, it is DST aware.

Your concern which runs from Boston and California may not be applicable. This is because your BusinessObject server is the same, unless you have several BusinessObject servers, all contain the same report.

In such a case, where you have multiple BusinessObject server, then my question to you is are the database each accessing are different as well? If the data are in the same timezone, then these 2 options have hard coded to convert to Sweden timezone, and meet your requirement.

If there are multiple databases, multiple BusinessObject servers, the data belongs to multiple timezone, and all containing the same rpt (the same rpt renders the output), then you need to provide more info about the setup. There is a solution as well which can automatically handle the timezone and DST aware

It is very rare to see databases, reporting servers, data, and rpt all contain different timezone, so I only provide solution meet the requirement you indicated. This is the time you should spell out everything so that I can answer with 1 post

[Former Member · Accepted solution]

Hi Sing-Cheong,

I apologize for the confusion. First off, I do not deploy my reports through BusinessObjects servers. I work for a software company that has embedded Crystal Report Viewer into the application.

Next, it is extremely common for my customers to have several servers in various locations as well as client applications in several other countries. I will need the reports to display time with respect to the client, regardless of where the client is. I already solved the situation for if the report's server is ALWAYS in Sweden and the client is ALWAYS in the UK, so this is not what I need help with. I am looking for a way to solve the problem so that it will work regardless of where the client and server is.

Please let me know if this clears everything up.

Thank you,

Elyssa

[Former Member]

Hi Elyssa Litchfield,

I understand better now. Correct me if I'm wrong. Following is my understanding

1. Your table contain a date column, time column, timezone column, but not target display timezone column

2. Original timezone is already defined in table, with column name DataTimeZone

3. You need to know the formula to automatic convert the original timezone based on DataTimeZone to clients' timezone by using embedded Crystal Report API

Following should be the formula, with 2 options:

Option 1:

ShiftDateTime ( {DataDate}+{DataTime}, {DataTimeZone}, CurrentCEUserTimeZone)

The value for DataTimeZone must be a valid value recognize by Crystal Report, or setup another lookup table which contains the full list. Example "UTC,0,BST"... "EST,300,EDT"...."PST,360,PDT"...

The Crystal Report function CurrentCEUserTimeZone will auto detect the client PC's timezone and calculate the new time.

Option 3: Uses database timezone function

Create a SQL Expression in Crystal Report designer, and enter following Oracle syntax (MS SQL Server 2005 and earlier does not support DST timezone)

( Select From_TZ( DataDate + DataTime , DataTimeZone) AT Time Zone SESSIONTIMEZONE As TzValue From Dual )

It makes use of Oracle build-in timezone function From_TZ and SESSIONTIMEZONE (DST aware) to auto detect client application's timezone. This solution works if the database connection is established from the client application where the client located. That means, if client PC located in EST timezone, and directly connects to the database, when open the embedded Crystal Report screen, database will automatically calculate its timezone.

However, if it goes through another middle-tier server (e.g. web server, SAP server) located in different timezone, then it will convert to the time to the middle-tier server's timezone. The workaround is to set the middle-tier server's OS time to your client's timezone. This is because the database connection is established from the middle-tier server

[Former Member]

Hi all,

is CurrentCEUserTimeZone working fine just with BO Embedded Server 2008?

In my environment, I get NO value out of this Special Field or Variable.

Thank you

[Former Member]

Not to mention, the actual date the time switches from standard to daylight and back varies yearly. Perhps you could add a parameter that asks whether they are in standard or daylight savings time.

[Former Member]

Hi Debi,

I have it successfully working if I know in advance what time zone it will run on. This is because the dates for DST are predetermined (USA - 2nd Sunday in March to 1st Sunday in November, for example). My issue is for making it generic without hard coding all of these daylight saving schedules. Any ideas?

Thank you,

Elyssa

[Former Member]

Sometimes we have to bite the bullet...  

Hmmm. How many clients do you have? (don't answer that ) But maybe you can make a Client Specific formula?  It will need updating as clents change but it would be a smaller formula, and if you are running the report, you can stay on top of it.