MS SQL Server OLAP - Can the date format be specified for dimension levels?

Author

Can the date format be specified for dimension levels?

David Norman

2006-01-19, 8:24 pm

If I have date data 2005-01-01, 2005-02-01, 2005-03-01 in Excel and use this
as the X axis for a chart, I can format it as "mmm-yy" and the axis will
display Jan-05, Feb-05, Mar-05.

How do I achieve the same result using the OWC chart component and cube data
source?

If I drag the date dimension level from the field list to the category axis
containing the same data, I don't have the equivalent date formatting options.

If I create a named calculation Left(Month,3)+'-'+Year then the members list
in alphabetical order instead of date order i.e. Feb-05 appears before Jan-05
on the axis.

Any suggestions appreciated,
David

David Norman

2006-01-26, 4:58 pm

Figured out how to get the members to appear in date order - it's not as
simple as setting attribute orderby properties. The steps I took are as
follows:

open the time dim
dimension:add business intelligence
specify attribute ordering
for my mmm-yy attribute, select ordering attribute = new attribute
select a suitable ordering attribute e.g. one with date data type
new attribute appears: "ordering mmm-yy on
whateverorderingattr
ibuteyouselected"
next and finish

Hope that helps somebody
David

"David Norman" wrote:

> If I have date data 2005-01-01, 2005-02-01, 2005-03-01 in Excel and use this
> as the X axis for a chart, I can format it as "mmm-yy" and the axis will
> display Jan-05, Feb-05, Mar-05.
>
> How do I achieve the same result using the OWC chart component and cube data
> source?
>
> If I drag the date dimension level from the field list to the category axis
> containing the same data, I don't have the equivalent date formatting options.
>
> If I create a named calculation Left(Month,3)+'-'+Year then the members list
> in alphabetical order instead of date order i.e. Feb-05 appears before Jan-05
> on the axis.
>
> Any suggestions appreciated,
> David

Sponsored Links