Format DateTime attribute in AS 2005

Hi,

I have an attribute a Time dimension which is of the datetime datatype. In the browser and in other tools, the displayed members are formatted like this:

dd-mm-yyyy hh:mm:ss

I want to change this format. How is this done? The format property of the attribute in the dimension editor seems to have no effect... I tried "mm/dd/yyyy" and without quotes.

Thnx... Jeroen.

Hi Jeroen,

If you customize the date format to what you want in the regional settings in the control panel, and refresh the browser, you will get the format you wanted. I've tried this out and it works.

Yan

|||

Hello Yan,

Thnx for your reply. I will try that. However, we want to implement a solution that's independent of the local user settings. For instance, we don't want the time displayed in this case. I figured this would be peanuts with AS 2005. Ofcourse, we can always do it in an extra column in the table or in the data source view.

Regards,

Jeroen

|||these are the value to use instead of "dd/mm/yyyy"

Constant

Description

DateFormat.GeneralDate

Display a date and/or time. Display a date part as a short date. If there is a time part, display it as a long time. If present, both parts display.

DateFormat.LongDate

Display a date using the long date format specified in your computer's regional settings.

DateFormat.ShortDate

Display a date using the short date format specified in your computer's regional settings.

DateFormat.LongTime

Display a time using the time format specified in your computer's regional settings.

DateFormat.ShortTime

Display a time using the 24-hour format (hh:mm).

|||

I am trying to solve the same problem.

So where do you need to set this property? I tried setting it in the NameColumn.Formatting property and in the Value.Nameformating property, but to no avail!

|||

Please could you telll me where to set this constant value?

I'm have tried in the format property but it does not work.

|||

Hello. I would recommend you to add a named calculation to your time dimension and use the TSQL-function CONVERT to transform your date to an appropriate format.

CONVERT(Char(10), GETDATE(), 112) will change a dateformat to '2007-05-25' (ISO-style)

You will find more information about this function in Books OnLine.

Try

select Getdate(), Convert(Char(10),GetDate(), 112)

in a query in management studio on the database engine.

HTH

Thomas Ivarsson

|||

Thank you. If you say that is the only solution, i will do so. But it will be very hard for me to add a named calculation to all date field in my data source view. I have about 50 date field and i was just wondering why it is not possible to set date format in the "format proterty field". The solution Mentionned by Yan is very interring for me, but it does not work. In fact, how to use VB code in AS projet?

|||

I have used the following code in the Calculations tab of Cube Designer in BIDS to acheive the formating for calculated measure. You can try doing the same for dimensions - I guess it would work. Pl change the format string to appropriate one.

FORMAT_STRING([Measures].[MyDimName]) = "#,#;(#,#)";

|||

Hi Thomas,

And thanks for this helpful answer : Date labels are well formatted with this conversion.

However, an OLAP client such as Excel 2007 will not recognize the attribute datatype and thus will not display the "date filters" for that dimension.

Moreover, an error occurs when setting the DataType of the NameColumn to something else than WChar (Date, for instance...)

SSAS allows to use a ValueColumn for the attribute, which I set to the CONVERTed date, in both Date and WChar formats, but the client still does not recognize it as a date field...

Any idea ?

Thank you

Greg

|||

Hi all,

Updating the thread with great news :

To have the Date attribute of a Time hierarchy recognized as a Date field in Excel pivot tables and display the Date Filtern, you have to set the ValueColumn of the key attribute (not the Date attribute) to the Date attribute column. Typically, the key attribute is an int32, this is why you don't think about making it point to the date column.

HTH

Greg

|||I've been trying to get this to function right for months to no avail. I've tried setting the key's value column to the date attribute column, It didn't work (excel still thinks my dates are text). My key was already a date type, perhaps that's the issue?|||

Hello Greg. You can set the name property to the named calculation that I have suggested but keep the key column to the date time data type. This is how I hve done it in SSAS2005.

HTH

Thomas Ivarsson

|||The problem I've had with doing that is that Excel 2007 still thinks that the value is text, it doesn't realize it's a date value...hence all the date filters won't work (and graphing by date is not done correctly).|||

Hello Jamie! I have tried this on the time dimension in the Adventure Works cube projects and it works correctly. I have used a proper dateTime column as the key and the output of the Convert function as the name column.

Do not forget to set the order by property to key column.

Regards

Thomas Ivarsson