Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dbradish
Starting Member
24 Posts |
 Posted - 2009-10-19 : 16:23:56
|
| My date dimension has a calendar DateTime field which I have formatted as 'short date' under the KeyColumn, NameColumn and ValueColumn attribute properties. However, the date keeps showing as '1/1/2009 00:00:00' in my cube browser and in Excel 2007. I am up to six test calendar date attributes testing between my data source 'full date' (1/1/2009 00:00:00) and my data source 'int date' (20090101), and regardless of what key I use, or if I put the 'full date' in my NameColumn and/or ValueColumn property, I can't seem to get a simple date format.When in an Excel 2007 pivot table, filtering on any of the six date fields using an 'is between' statement results in zero rows. Week, quarter, month and year filters work just fine. (I've also verified the RI between my fact and dimension tables.)Is there an option to get a date format and a date field to filter properly without converting my DateTime 'full date' to a SQL Server 'Date' field type, and/or making my user filter with my 'int date'?Your comments will be appreciated! |
|
|
dbradish
Starting Member
24 Posts |
Posted - 2009-10-27 : 15:59:11
|
| For anyone who may have the same problem, this issue was resolved with the following:1. In your SSAS data source calendar table, add a field that is a varchar data type. Example: Convert(varchar(10),calendar.dbo.DateField, 101) will give you 01/01/2009 for Jan 1, 2009. 2. Add this character-date value as your NameColumn property for your date-value attribute.Excel will now display a "formatted" date value and will be able to filter it providing the date is a row or column label. I assume that most readers here understand that '1/1/09' and '01/01/2009' are not the same thing in a character data type.If your user drags your date to the Excel ReportFilter option, your user gets to click, click, click every date they want. Years/quarters/months/weeks are just a different way of thinking for users who are conditioned with years of date-driven reporting. After a few hundred clicks, that week filter will start to look mighty fine for an acceptable level of granularity.Best of luck!p.s. Bonus tip: To get your attribute date to sort in DESC order, follow these steps:1. Add a calculated field to your SSAS data source calendar view that converts your date to an integer and then multiplies that integer value by -1.Example: CONVERT(int, CONVERT(datetime, cal_DateFull)) * - 1 AS DateSortDesc2. In your time dimension, add this field to your attribute list setting AttributeHierarchyVisible = False. Add the new field multiple times if you have multiple dates in your dimension.3. Set an attribute relationship between each date and a unique instance of this new 'DateSortDesc' attribute. (Your calendar/fiscal date field would be your 'Source Attribute' and your DateSortDesc1/2 field would be your 'Related Attribute'.)4. For date values that now have a relationship to an instance of 'DateSortDesc', set the OrderBy property to 'AttributeKey' and set the OrderByAttribute property to 'DateSortDesc'. |
 |
|
|
CanadaDBA
583 Posts |
Posted - 2012-03-27 : 10:31:55
|
| Thanks dbradish for leaving the resolution here. After 2.5 years, it is helping me!I have the same issue. My date values show as text in Excel. Although the column’s data type is datetime in the table and is Time in the cube. The business needs the column to be as date in excel, so they can sort the column as date or search ‘between’ dates. I am going to follow up dbradish steps but if anybody have new findings for my issue please post here. Thanks!Canada DBA |
|
|
|
|
|
|
|
|
SSAS 2008 Date Format & Excel07 Filtering