formatting in cube

Is there a way to format data in the cube?

I have a date formatted as such:

YYYY-MM

I want to show the date as 01, 02, 10, 11, ... instead of 2006 1, 2006 2, I want 2006 01, 2006 02, and so on

is this possible to accomplish?

Also,

I have numbers showing

5.12345677999E-02

How can i get this to show as 5.12345 only?

Is this something that should be handled on the db/select statement side or is there a way to do this on the analysis services cube side?

thanks

On the date formatting, I am assuming that your dates are part of a dimension. In this case what you would normally do is to define a column either in a view or in the DSV that formats the date the way you want it and returns this as a string. You then set this string up to be the attributes name and set up the actual value to be the attribute key. This allows you to do things like sort by values, but display nice strings for the names.

On the numbers issue, there is a format string that you can define for every measure. The only problem here is that Analysis Services can return both the formatted and unformatted values and some clients (like Excel prior to Excel 2007) only take the unformatted value, so the formatting may have to be done client side.

|||

i have my derived column formatted as yyyy-dd-mm but how would I have the 0 in front of 1, 2, 3, 4, 5, 6, 7, 8, 9 and not in front of 10,11,12?

what would i have to add in the derived column task?

|||You can use the Len() function to determine if your number is 1 character long, and prefix a zero if it is. Len is supported in the SSIS derived column transformation, if that is what you were referring to in your question. It's also supported in most databases, so you could implement it in your data source view as well.|||

In T-SQL you could do something like the following, just replace the call to GETDATE() with the date column name in your table

SELECT LEFT(CONVERT(varchar,getdate(),121),7)

The 121 format is "yyyy-mm-dd hh:mi:ss.mmm(24h)" and you just grab the left 7 of that.

Formatting dates on a chart when sourced from SSAS2k5

I am creating a few charts in SSRS that are sourced from a cube in AS. On the x-axis is time, which is what I assume a date-like field in the cube. On the y-axis is the measure.

My problem is that I cannot seem to get the dates to format on the chart - regardless of the format code I use, the dates appear in long format, like "Thursday, November 23 2006".

Are the dates from SSAS just string values?

I feel that you may have mentioned some expression or modification done on that field.I faced this problem before where i came to know that Format expression which will be like (#000# Something...) when some operation is being performed on that date. Take out that any operation on that field and give only format option or use format function in the expression.

Hope it should work

Regards,

Raj Deep.A

|||Hi Raj,

I'm not sure I follow - I haven't modified the field at all, yet. I merely created an MDX Query using the designer, and Time is one of the dimensions I used to splice my measure by. I wanted to expose this data in a chart, but for some reason I can't apply any standard formatting codes to the Time label on the x-axis.|||

Based on your query in the MDX query generator, the date time value is provided as a string to Reporting Services. That's why format codes won't work.

You could look into either changing your MDX query to get the value as DateTime object (which means you have to hand-write the MDX) or you could try to convert the string back into a DateTime object in the chart category grouping expression by using an expression similar to this: =CDate(Fields!DateValue.Value)

But depending on the actual strings, the CDate() function may fail to convert and you may need to look into using a combination of several VB runtime conversion functions / DateTime functions to achieve the conversion.

-- Robert

|||That's what I feared...thanks for the confirmation. Hopefully something to look forward to in Katmai? [better integration between SSAS and SSRS, that is]|||

Another aproach is to assing a value to your attribute members and use the MemberValue MDX function.

1. In your cube, assign the Value property of the Date attribute hierarchy to a Date column in the underlying table.

2. Unfortunately, the RS SSAS data provider doesn't currently surface the MemberValue function so you need to create a calculated member, e.g. NativeDate with the following expression [Date].[Date].MemberValue.

3. Now you can format the calculated member as you wish, e.g. Format(Fields!NativeDate.Value, "MMM/dd/

|||How does this work with server time dimensions?|||Sorry, it won't b/c you won't be able to assign a value column to a server time dimension.

Formatting dates on a chart when sourced from SSAS2k5

I am creating a few charts in SSRS that are sourced from a cube in AS. On the x-axis is time, which is what I assume a date-like field in the cube. On the y-axis is the measure.

My problem is that I cannot seem to get the dates to format on the chart - regardless of the format code I use, the dates appear in long format, like "Thursday, November 23 2006".

Are the dates from SSAS just string values?

I feel that you may have mentioned some expression or modification done on that field.I faced this problem before where i came to know that Format expression which will be like (#000# Something...) when some operation is being performed on that date. Take out that any operation on that field and give only format option or use format function in the expression.

Hope it should work

Regards,

Raj Deep.A

|||Hi Raj,

I'm not sure I follow - I haven't modified the field at all, yet. I merely created an MDX Query using the designer, and Time is one of the dimensions I used to splice my measure by. I wanted to expose this data in a chart, but for some reason I can't apply any standard formatting codes to the Time label on the x-axis.|||

Based on your query in the MDX query generator, the date time value is provided as a string to Reporting Services. That's why format codes won't work.

You could look into either changing your MDX query to get the value as DateTime object (which means you have to hand-write the MDX) or you could try to convert the string back into a DateTime object in the chart category grouping expression by using an expression similar to this: =CDate(Fields!DateValue.Value)

But depending on the actual strings, the CDate() function may fail to convert and you may need to look into using a combination of several VB runtime conversion functions / DateTime functions to achieve the conversion.

-- Robert

|||That's what I feared...thanks for the confirmation. Hopefully something to look forward to in Katmai? [better integration between SSAS and SSRS, that is]|||

Another aproach is to assing a value to your attribute members and use the MemberValue MDX function.

1. In your cube, assign the Value property of the Date attribute hierarchy to a Date column in the underlying table.

2. Unfortunately, the RS SSAS data provider doesn't currently surface the MemberValue function so you need to create a calculated member, e.g. NativeDate with the following expression [Date].[Date].MemberValue.

3. Now you can format the calculated member as you wish, e.g. Format(Fields!NativeDate.Value, "MMM/dd/

|||How does this work with server time dimensions?|||Sorry, it won't b/c you won't be able to assign a value column to a server time dimension.