Cube View DatePicker, Time Zone and Custom Calculate

Forum|Forum|3 years ago
December 23, 2022
11 replies
8 views

We're working on a solution, where users can enter and view a date through a Cube View using the Cell Type Date. The challenge is how OneStream handles date's (datetime) with time-zones. When a date is saved to the backend, it's converted to UTC time-zone. The Date component in the Cube View is also Time-Zone aware, so that it uses the local time of the user, to convert back from the backend UTC to local-time. The challenge with this is 2 fold:

1) Assuming we would calculate a rent for a given period. The user, based in CET time-zone, enters 2022-1-15 using the Gridview. This date is converted to 2022-1-14 as UTC (2022-1-14 23:00:00). Using a Custom Calculate method to calculate the rent from start (2022-1-15), now adds one day more, as the server time is UTC. (don't mention Daylight Saving Zone)

2) If another user, based in US Central, is reviewing data in the same Grid View (and same POV), this user will see a wrong date, as the server UTC date, is converted to this user's local time

Is there a way to specify in the Cube View, that when using the Date/DateTime picker, that the component should ignore time-zones? Or are there other usable solutions for this issue?

Cheers

J

JackLacava

I don't see this behaviour in my version (7.2.2). I created two users with different cultures (US and Japanese). When they manipulate a cubeview in Data Explorer, pointing to a NonFinancial account in V#YTD, with a column with Cell Type set to Date or Date Time, whichever value they set will get saved in the database as it is - so both users see exactly the same value, not going through any UTC-related mangling.

As far as I can see, OneStream is simply taking the datetime object and saving the value of its .Ticks method. If you want to manipulate it for some reason (e.g. intercepting it into some event handler), you can reconstruct the object:

Dim myDate as new Date(yourDataCellObject.CellAmount)

F

FrankDKAuthor

I don't think the culture setting in OneStream affects anything related to this. Isn't culture more on language shown + formatting of numbers etc? Try to change the time-zone on your computer, restart the OneStream Client and check if the time is still the same in the Cube-view if you're on US and Japanese time-zone

Though, I have testet on 7.0 and 7.1. Both versions saves dates to UTC and converts to users local time-zone defined by the computer configuration. Will test if 7.2 includes a bugfix.

F

FrankDKAuthor

Just to follow up - now tried on 7.2.2. Still same issue. This is Cube View on Time-Zone UTC-6

Changing the Time-Zone to UTC+1, the same Cube View with same POV now shows:

C

ChristianW

This isn’t a bug, it is expected behavior.

You need to take the time zone of the reporting entity in consideration when you are calculating the rent with a custom calc. VB.net has a set of function to help you handling time zones.

Dim myTimeZone As TimeZone = TimeZone.CurrentTimeZone

' convert DateTime.Now to local and universal time.
Dim local As DateTime = myTimeZone.ToLocalTime(DateTime.Now)
Dim universal As DateTime = myTimeZone.ToUniversalTime(DateTime.Now)

P

PhilB

Christian,

Unfortunately, this approach won't work. The issue is that "Local" time refers to the local time of the computer that the code is running on. In a SaaS environment, that is the "Local" time of the OneStream App Server. OneStream App Servers are always set with their LocalTime = UTC. So, the above conversions won't help the underlying problem.

Below is the result of running your code at noon, on a machine in the US Eastern Time Zone.

At the point of time OneStream is writing or reading the data, it appears to be converting from user's true local time to UTC. However, as an end-user or developer, we don't have any tools to access what the user's true local time is. Any Business Rules we write will always return UTC as the "Local" time zone.

What technique / code / api is OneStream actually using when this data is retrieved? It must be something different that what you've mentioned above.

Thanks,

F

FrankDKAuthor

On the backend, I more or less can make a workaround, converting the UTC stored values to a specific target Time Zone. The biggest issue is the fact, that 2 users, reporting/reviewing/calculating values on the same entity, will eventually look differently at the same date. A user in Germany will input a date, which is converted to a UTC value (because of the time-zone of the server backend). His colleague in US reviewing data, will see the date in a different time-zone, as the Cube View converts the backend UTC value, to his local timezone. And from a user perspective, this could look like 1 day in difference, if only looking at the date value, not including time. So a UTC+1 time of 2022-01-01 (00:00) becomes 2021-12-31, from a UI perspective. So my point is more, that OS, in this case, should ignore the time-zone information. And that's where I'm trying to find a solution.

B

BSK

we are also facing the same issue with the date fields in the data entry forms.

OneStream has confirmed that this a potential bug in the product and they wokring on it with high priority.

F

FrankDKAuthor

Thanks for sharing - can I ask when you did report this? Cheers

B

BSK

we have reported this to oneStream last week.

P

PrithviK1

We are experiencing similar issue. And we also noticed that Cube View shows different time and when you extract that to Excel that's again incorrect time. Reported to OneStream today

A

Anonymous

Did anyone get any additional updates on this from OneStream? Has anyone come up with a solution to handle this?

N

nkehli

Hi all

This is still an issue in 8.4 version when we export a cube view to excel, we have the 1 day difference? Can someone from OS comments and explain how we can resolve this important issue?

Thanks

Nouria

M

MG8

Hi

I'm assuming that you are referring to the date being shown as a header or a footer as you export to excel? There is a similar post already Book: Footer using UTC time zone | OneStream Community

If you search for "UTC" on community there are a few posts and a couple of ideastream posts that you may want to upvote.

If you want specific help with your issue, it might be worth raising a ticket with support to discuss and demonstrate your specific issue. This post is originally 3 years old and since you are seeking someone at OneStream to explain how to resolve an important issue to you, I would suggest you proactively contact support as there is additional detail needed in terms of what you are trying to do and the expected result.

Show more replies

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded