Customize Time Dimension Member Names

SQL Server 2000 Analysis Services' and SQL Server 7.0 OLAP Services' Dimension Wizard automatically creates member names, but the names don't always include the member's relevant time period. However, you can customize these generated Time dimension member names. First, from the Dimension Editor, select each level in turn in the tree pane, and change the Member Name Column property to the unique name you choose. Here are some examples of unique member names; these examples use a Time dimension table called Dates that has a date field called Date:

Year Level

DatePart(year,"Dates"."Date")

Quarter Level

'Q'+ CONVERT(varChar,DatePart(q,"Dates"."Date")) + '/' +
CONVERT(varchar,DatePart(year,"Dates"."Date"))

Month Level

CONVERT(varChar,DatePart(m,"Dates"."Date")) + '/' +
CONVERT(varchar,DatePart(year,"Dates"."Date"))

Day Level (European Notation)

CONVERT(varChar,DatePart(d,"Dates"."Date")) + '/' +
CONVERT(varChar,DatePart(m,"Dates"."Date")) + '/' +
CONVERT(varchar,DatePart(year,"Dates"."Date"))

Day Level (US Notation)

CONVERT(varChar,DatePart(m,"Dates"."Date")) + '/' +
CONVERT(varChar,DatePart(d,"Dates"."Date")) + '/' +
CONVERT(varchar,DatePart(year,"Dates"."Date"))