Time dimension elements show up as string elements in report studio

[Ogram]

Hey gurus,

Topic sums up the problem quite nicely. I have a time dimension in a transformer cube formatted as YYYY/MM/DD and set up to be a of date datatype. For some reason the elements from the time dimension show up as  being of the type varchar in the report studio and due such cannot be used with time-specific functions which I sorely need.

I have a hunch there could be a localization-related issue behind this as I'm using EU-based localization whereas I assume the YYYY/MM/DD-style formatting is used in the US? Before I go around messing with this and trying, for example, to format the date as YYYY-MM-DD instead of using /s I'd like to hear your opinion as to whether this could be the cause or if you have any ideas to the real problem? I'd rather fix this problem at the cube level to make maintaining the reports easier. This cube is solely used for future mobile reports and I do not need to maintain any kind of compatibility to existing reports as there are none.

I'm somewhat new to this side of things so for future reference - is the data type carried by the formatting of said items or is it separate? The point I'm trying to get across is does the cube convey the data type, value and formatting as separate pieces of information (Data type = Date, Value=2014-04-13, Formatting = YYYY/MM/DD) or does it simply convey the value and the end user/application will interpret the data based on this value, guessing its data type based on how the value was formatted?

Thanks,
Jussi

[Lynn]

Elements in a cube are identified by Member Unique Names so you don't really manipulate things the same way you do in the relational world.

The best idea is to build the various relative time dimensions based on the cube's current period so you can easily report MTD, YTD, rolling months  and all manner of various timeframes your users might be interesting in. There is a chapter in the Transformer user guide that covers relative time.

In addition there are dimensional functions that a report writer can use to traverse the time dimension hierarchy. The attached PDF is a decent reference.

[Ogram]

Thank you for your reply,

I got the time dimension working beautifully with all your standard relative categories and a few custom ones. The problem is that the report studio (BI environment is the latest available) sees these elements as containing string values so I cannot apply date-specific functions or data formatting which is causing me some trouble on the reporting end of things.

[bdbits]

Maybe it is just me, but I am really not following the data type issue here. Is the date a string in the source? Is it modeled in transformer as a string? Or are you saying that it is modeled everywhere as a date, and when you use the data element directly (no expression) in RS it is somehow magically being converted to a string? If so how do you know your time dimension is "working beautifully"?

If it is a string in the source, add a calculated column in your model and cast it to a date, and use that as the attribute instead of the original string.