Need to display EST time in the report

[Former Member]

Hi Gurus,

I am using Activity universe to build the report which shows User login by Time,

when i am running the report it is showing the GMT time.

The time should show the EST timing.

My Database is MS SQL

pls find the screenshot attached.

Thank for the Support...

[Former Member]

Hi Lilkar,

Try using DateTimeOffset function, this function will be available from MS SQL Server 2008 onwards.

Hope it helps!

Thanks,

Megha Jassal

[Former Member]

Hi Megha,

Thank You for the response,,

Have you did this in Report level, can you help me out with this...

Thank You for the Help !!!

[amitrathi239]

Hi,

Check SAP Note 1428277 - How to display the action time in EST time instead of the GMT time if the auditing databas....

1. Open the Designer
2. Import the "Activity" universe
3. Open the class "Actions"
4. Right click and then select "Object" to add a new object
5. Set a name for this object, for example: "Action Time EST"                                                                       
6. Set the type of this object to: "date"                                                                                         
7. In the "Select" text box type the following line:  date_sub(AUDIT_EVENT.Start_Timestamp, interval 5 hour)                                                              
8. Click on OK
9. Save and export the universe
10. The new object is ready to use in reports

Note:  On the Step 7, you can convert the Action Time to any time zone you desire, either use date_add or date_sub function in MYSQL:

For example, if you want to display the time in PST, which is GMT-8, you can use:

date_sub(AUDIT_EVENT.Start_Timestamp, interval 8 hour)

if you want to display the time in Beijing time, which is GMT+8, you can use:                                                             

date_add(AUDIT_EVENT.Start_Timestamp, interval 8 hour)

This is only valid if you use MYSQL as auditing database. If you are using oracle please refer note  1390845

Note: In SAP BusinessObjects BI 4.0 Auditing Universe (Activity universe) is no longer packaged in the Enterprise install. However it is available online as per KBA1592124. In BI 4.0 ADS_EVENT.Start_Time is also stored in GMT (Greenwich Mean Time). Similar resolution can be applied to convert the action time from GMT to another time zone; substitute ADS_EVENT.Start_Time in place of AUDIT_EVENT.Start_Timestamp.

Check attached link.

Amit

[Former Member]

Hi Amit,

Thank You for the reply,

The above one works for MYSQL, What is to be done for MS SQL,??

Thank You for the Support,,,

[amitrathi239]

Hi

not very sure about exact syntax in SQL server.might be you can do with DATEADD function in universe.

dateadd(hour,-datediff(hour,getdate(),getutcdate()),AUDIT_EVENT.Start_Timestamp)

Amit

[Former Member]

Hi Amit,

Can you help me out with something in Report level.

kindly help...

Thank You for Help...