SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 Cube Date issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/03/2012 :  09:41:36  Show Profile  Reply with Quote
I have a cube with a Date dimension table; dDate. This table has a column called Day and is datetime. My issue is that the Day column shows as 'Character' in SAP Business Object.

I used Excel and the Day is shown as text too; i.e. if I want to sort the column in Excel, it offers sort A to Z rather than say earliest to oldest.

Why I get this behavior? Any suggestion to resolve the issue is greatly appreciated.


Canada DBA

RickD
Slow But Sure Yak Herding Master

United Kingdom
3603 Posts

Posted - 04/03/2012 :  10:13:51  Show Profile  Reply with Quote
On your dimensions in SSAS, what are the properties of this field? Is the datatype there a datetime?
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/03/2012 :  10:22:19  Show Profile  Reply with Quote
From the Day column properties:

IsAggregateable: True
OrderBy: Key

Name: Day
Type: Date
Usage: Key
OrderByAttribute:

KeyColumns: DimDates.Day (Date)
NameColumn: DimDates.Day (WChar)

Let me know if you need anything else. Thanks!


Canada DBA
Go to Top of Page

CanadaDBA
Aged Yak Warrior

Canada
583 Posts

Posted - 04/03/2012 :  14:57:51  Show Profile  Reply with Quote
I read somewhere that says "SSAS uses the name column as a label to display to the user instead of displaying the key column". Is this related to my issue?

I tried to change the type of NameColumn from WChar to Date but got an error saying "The 'Date' data type is not allowed for the 'NameColumn' property; 'WChar' should be used.". :(

Canada DBA
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3603 Posts

Posted - 04/04/2012 :  03:54:12  Show Profile  Reply with Quote
Hmm, I think it is related, not 100% sure ont he way around it though.

When you view it in BIDS or EM, does it sort properly?

Edited by - RickD on 04/04/2012 03:55:38
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
362 Posts

Posted - 03/14/2013 :  19:26:43  Show Profile  Reply with Quote
I"m having the same problem, wish I knew what the OP did...
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 03/15/2013 :  13:11:59  Show Profile  Reply with Quote
we did month sorting similarly by adding a sortkey field and using it for sorting.
see below for details

http://technet.microsoft.com/en-us/library/ms166763.aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000