MDX: Formatting the Output of a Calc Member

[G.OKeefe]

Question for Bill Pearson:

First and foremost, I'd like to thank you for providing the number one "go - to" resource for those of us that are learning MDX; your series goes so much further than the documentation, and is highly valuable in that it teaches functions through the use of practical examples, based upon a sample database that is accessible to anyone who has installed Analysis Services. I have a couple of questions about "pure MDX" (results I want to obtain through direct queries, versus obtaining them at the reporting layer of the solution - where performance is often negatively impacted to obtain these same desired effects, as you so clearly and consistently warn us in your articles).

I'm posting each separately to this forum - hope I'm in the right place for Bill to see this!

Question: Is it possible to format the output from a calculated member at the Analysis Services level?

Thanks!

George

[Island1]

George:

When you say "... at the Analysis Services level," do you mean within an MDX query ("WITH" statement), or via the Calculations tab within the Cube Designer (Business Intelligence Development Studio)?

Thanks!

Bill

[G.OKeefe]

How about an example of formatting within an MDX Query? That will be helpful at present ...

Thanks!

George

[Island1]

George:

Give this a spin:

WITH
MEMBER
[Measures].[Freight Cost as % of Sales]
AS
[Measures].[Reseller Freight Cost] /
[Measures].[Reseller Sales Amount],
FORMAT_STRING = '0.000%'

SELECT
{
[Measures].[Reseller Total Product Cost],
[Measures].[Reseller Sales Amount],
[Measures].[Freight Cost as % of Sales]
}
ON AXIS(0),

NON EMPTY
(
[Reseller].[Reseller Type].[Reseller].MEMBERS,
[Product].[Product Categories].[Category].MEMBERS
)
ON AXIS(1)

FROM
[Adventure Works]

WHERE ( [Date].[Calendar].[Calendar Year].[CY 2003])


PS: Keep in mind that this is a sample DB, and that the Freight Cost I reference above was probably populated in the DB by applying a simple percentage to the associated sales value - hence the numbers themselves tend to "look a lot alike!"

HTH!

Bill