One annotation: I used the formatstring #'# - #,# - #'#00 - #,#00Dear all
this sounds perhaps like a silly question, but I cant format an attribute
(double type) in a dimension. The place where I guessed to place a format
string is in the attributes properties source - keycolumns - Format setting.
But my format string did not affect the appearance of the attribute in the
browser.
I want to format the double type to 3,300 for a number 3333.333333333. If
this does not work I would like to format at least the value like this 3,333
.
Regards and thanks in advance for your help,
Marc|||One annotation: I used the formatstring #'# - #,# - #'#00 - #,#00|||Hello Marc,
The Data is formatted according to the specified named or custom format
string. Any format string that Excel supports can be supplied.
Format Element (ASSL)
http://msdn2.microsoft.com/en-us/ms127786.aspx
DataItem Data Type (ASSL)
http://msdn2.microsoft.com/en-us/ms129482.aspx
Also, you may want to consider a calculated member and use format string to
achieve this goal.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Marc,
The Data is formatted according to the specified named or custom format
string. Any format string that Excel supports can be supplied.
Format Element (ASSL)
http://msdn2.microsoft.com/en-us/ms127786.aspx
DataItem Data Type (ASSL)
http://msdn2.microsoft.com/en-us/ms129482.aspx
Also, you may want to consider a calculated member and use format string to
achieve this goal.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Peter
thank you for your answer. I tried to enter a format string as described but
there was no effect in the BIDS browser of this particular dimension. Any
idea what could be the problem?
Regards, Marc|||Hello Peter
thank you for your answer. I tried to enter a format string as described but
there was no effect in the BIDS browser of this particular dimension. Any
idea what could be the problem?
Regards, Marc|||Hello Marc,
Thank you for your update. I was able to reproudce the issue on my side. It
seems the format of keycolumns is not supported in UI/broswer. I will
peform some more research and get back to you as soon as possible. I
appreciate your patience.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Marc,
Thank you for your update. I was able to reproudce the issue on my side. It
seems the format of keycolumns is not supported in UI/broswer. I will
peform some more research and get back to you as soon as possible. I
appreciate your patience.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hello Roger,
We have filed a bug on this issue and we'd like to know the business impact
of this problem.
If we convert the attribute data type in the Data Source View via Named
Query, we can convert the attribute type from double to integer. Please
test the workaround to see whether the workaround is acceptable for you.
Thanks.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.
Showing posts with label attribute. Show all posts
Showing posts with label attribute. Show all posts
Monday, March 19, 2012
Friday, March 9, 2012
Formating Date Attibute in AS 2005
Hello everybody,
i'm trying to format a date attribute in AS 2005. I try to use the format property in the dimension designer but it does not work. It will be very hard for me to create another field of date in the data source view. My Cube have have about 100 field of type DATE.
Could you help?
Try searcing this forum for "format string". See if any get any useful information from previous discussions. You will find quite a few on the matter.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
Friday, February 24, 2012
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]) = "#,#;(#,#)";
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 :
|||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?|||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
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
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]) = "#,#;(#,#)";
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]) = "#,#;(#,#)";
Subscribe to:
Posts (Atom)