November 24, 2008 at 8:12 am
Hello
I have not been working with SSAS all that long, so any help / suggestions would be great.
The situation is that user connect to a cube via Excel 2003 they have several columns that contain dates, The data source view is based off of a view in this view I select these dates as dates but also select the same date again but need to format it so I select the date column like this again CONVERT(varchar, ,7) this produces the result Nov 24, 08 which is what I was hoping to get this all works fine until the user access it in excel they drop the dimension in but Excel sorts it via name so April is sorted first, The reason for this is I am sure because the data is converted to a Varchar,
My question is does anyone know another way to do this, so that when the user drop the month column into the pivot table is pre-formatted and still a date ?
Any help would be great
November 24, 2008 at 9:45 am
You don't actually do that. Do the formatting as you have (not that I like the format), but leave it alone.
In your dimension definition, you can configure a "Name" and a "Key" field for an attribute. Leave the "Name" field the formatted field and make the "Key" field an actual date. Then, you can change the sortby property of the attribute to "AttributeKey" and your formatted values will be sorted in date order.
November 25, 2008 at 4:58 am
Hello,
Thanks for the help, I have been trying to implement what you suggested but have not been able to get any results, or even affect the sort order
I select the attribute that is the key of the dimension and change the sort by to attributekey in the order by drop down but the SSAS then gives error of the OrderbyAttribute has not been defined, I select from the drop down on of the other attributes that is a date - I rebuild the cube but the sort order does not change
I then create the key of the dimention to be a data type of date this then shows in the column Key Column as Date instaed of WChar, I follow the steps as I did beofre - no change.
I then try to alter the actuals atribute that I am trying to sort by the date, the atrribute ahs a Key column value of WChar, so i change the OrderBy to Attributekey but when i try build the cube i get the error message that the OrderByAttribute Value hasnot been defined, I open the properties of the attribute again and try to set the OrderByAttribute but there are no values available in the drop down.
Any pointers on where i am going wrong would be great
November 25, 2008 at 5:10 am
I have attached a screen print of one of my month dimensions. The "KeyColumn" is a numeric value and the "NameColumn" is a string ("January 2008"). The "OrderBy" attribute is "Key" meaning order the members by the key column rather than the name column.
November 25, 2008 at 7:37 am
Hey there,
Thanks for the help, it didn't do exactly what i wanted, but I can see how I should now build my views a little diffrently, and once that is done this should sort if out I think , thanks for the help
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply