Clarity

Hello All,

We have one Jaspersoft report that extracts timesheet information. When the report is run by some US user, the prstart field of prtimeperiod table returns one day less in the report output. The database is set to work for CET timezone.

Kindly, suggest some way to return exact prstart date for all timezone users.

Thanks,

Pragya Singh

Hi Pragya,

It depends on the locale set at user profile, also what functions you are using in your reports. In the out of box reports we use these functions to get the date correct COP_CALC_FINISH_FCT or COP_CALC_FINISH_TIME_FCT or COP_DATE_TRUNC_FCT

Regards

Suman Pramanik 

Hello Suman,

We have used COP_CALC_FINISH_FCT  function for prfinish date and it works fine for it.

Have also tried all three functions for prstart but issue is still there.

Thanks,

Pragya Singh

Hi Pragya,

Have you observed the same problem in the out of box report?

Regards

Suman Pramanik 

Hi Suman,

Same issue is not appearing in out of the box report. I tried "Resource Time Summary and detail " report and it shows correct prstart date for US timezone user.

Do you know whether any function is used in this report for time period start date?

Thanks,

Pragya Singh

Hi Pragya,

I am sorry I don't have it handy, you can open the report in Jaspersoft studio and you will be able to see the same.

Regards

Suman Pramanik 

We have the same issue.  We use ondemand.ca.com where the servers are based in the Eastern States (Australia) and we are based in Western Australia where there can be up to a 3 hour time difference.

In our custom reports we had to put in additional logic in the SQL:

 trunc(sr1.cop_report_date)+1 status_date
 trunc(i.target_resolution_date)+1 target_resolution_date,
 trunc(i.impact_date)+1 impact_date,

First item was for the Status Report Date, and the other two were for Issue Dates.

Not saying this is a perfect solution and still unclear why it is required as JasperSoft should be able to work out where the user is located and correctly show the date, but this is what we had to implement.

Hello Roland,

Thank you for the suggestions.

We have found the fix for this issue. We used below statement for timeperiod start date and report now extracting correct date for all timezones:

TO_DATE(TO_CHAR(prstart,'MM/DD/YYYY') || '12:00', 'MM/DD/YYYY HH24:MI') 

Thanks,

Pragya Singh