[SOLVED] Is it possible to modify display values in a level?

[rvitatoe]

This sounds dumb, but we work with a Broadcast Calendar, which I have not been able to match to any type of time dimension in Transformer, so I had to create years, quarters and months in my datasource and bring them in as integer fields.Ã,  This works perfect for what I am doing...Ã,  But, When I drill through say "2005"Ã,  I get columns "1" "2" "3" and "4" --Ã,  Is there anyway to modify the column based on it's value?Ã,  Such as adding a "Q" to it so it at least shows up as "Q1" ?

And if this is possible... can I also change my months to read a text month, instead of the numeric month?Ã,  Example:Ã,  Instead of listing as "1", it would alias as "January"?

Thanks in advance for any insight anyone might have!

[mhamilto]

I would create 2 new input queries for Quarters and Months. They don't need to be fancy, in fact they can just be csv files. In each of these queries I would have 2 columns, the first one for the numeric value and the second one for the label. Then you just set the properties of the level in the dimension so that the numeric value is used for the category code and for sorting and the text value is used for the label.

[Darek]

Or you could just use DB time/date-to-string formatting functions and bring what you need as labels.

Or you could create calculations inside the query in Transformer.

But bootom line, yes, just have separate columns for source and labels. While you are at it, you also should have a separate column for category codes.

[rvitatoe]

Thanks for the input.  I think I'm going to go with adding another query to translate the month number to text...  But I am almost positive there is a way to concatenate a 'Q' onto the quarter value...  I swear I have seen it done before :)  which really means it will drive me crazy for awhile longer :)

I do have access to the datasource as well, so I could concatenate the 'Q' and the numeric quarter value there...  But I'm now still curious if it really is possible in transformer.

Thanks again for the help!

[CoginAustin]

Create a new level(Q1,Q2,Q3,Q4) in the heirarch view and drag your 1,2,3,4 into these new Q level. Then supress your 1,2,3,4 level

Also, you can just modify your IQD and add something like 'Q' + Quarter as QTR for this field.
you probably will need to cast the Quarter value to a CHAR type field for it to work.

[Blue]

You could also use the date dimension wizard which will generate standard labels for you.  I assume that your fact data source has a date/time key in it.  This will be what connects the date dimension to the measures for scope.