We have just started using Enterprise, and need a descending sort by the field "Date YQMD".
The current format of "DATE YQMD" is "YYYY MMM" so the result of "2012 Sept, 2012 Oct" sorts alphabetically (not in date order). Thus, resorting by descending order does not correctly sort in date order.
I tried using the "Month" field in columns the "Year" or the "Date YQMD", but that is summing up only by month (i.e., both 2011 Apr and 2012 Apr incorrectly show $311,154).
If the format of "DATE YQMD" can display as "YYYY MM", or even "YY MM MMM", alphabetic sorting would be fine.
I know how to format dates in Jet Essentials reports and tables, but how do I do it in an Enterprise Cube table?
Maybe I need to make a Jet Report with ah second page built using the Cube, and add a Report column reformatting the date?
Date
Votes
2 comments
-
Jet Reports Historic Posts Hi,
The date formatting is done in the data warehouse and you unfortunately can't change it through Excel. You will need to start Jet Data Manager and open your project.
Expand your data warehouse and look for Date table. The fields that are called DisplayMonth, DisplayQuarter and DisplayDate within the Date table are the ones that control the formatting for those periods.
For instance DisplayMonth has this custom transformation on it:
CAST([YearValue] as VARCHAR) + ' ' + SUBSTRING([MonthName], 1, 3)
It takes the YearValue and converts it to a string then adds a space and the first three characters of the month name.
In your case, you could replace the MonthName with the MonthNumber, which will enable you to sort it either way you want to in Excel:
CAST([YearValue] as VARCHAR) + ' ' + CAST([MonthNumber] as VARCHAR) -
Jet Reports Historic Posts Miha,
Ok, I suspected as much. I can probably manage this change.
However, I don't see Jet Data Manager in my program listing. Should I see this as a separate program or do I access it from within a speadsheet that uses a cube?.
Please sign in to leave a comment.