In SSAS 2005, under the properties for a measure, I am curious if I can
create a dynamic FORMAT_STRING - allowing me to verify (or look up)
what kind of measure is displayed, and then attach the '$' or '%' or
whatever as appropriate. We have a few KPIs that are a mixed bag of
formats and would like to have this handled in the cube rather than the
front-end display tool. I have the display format in a dimension with
the KPI name, and could look this up using an IIF or something if that
were to work. Any ideas?

Should be possible, by setting the FORMAT_STRING property of a
calculation - see this post at Chris Webb's blog:


http://spaces.msn.com/cwebbbi/blog/cns!7B84B0F2C239489A!137.entry

Anyone who has looked at the AdventureWorks sample cube will probably
have picked up by now that all the intrinsic member properties like
FORMAT_STRING can now be defined with an MDX expression (in AS2K it was
only FORE_COLOR and BACK_COLOR that you could do this with), but
something else caught my eye today when I was looking at the tutorials
in BOL: there are new MDX statements which allow you to set these
properties on any area within a cube from a script. So you can now do
things like:
BACK_COLOR(THIS)=RGB(255,100,100)
...to give everything in the current subcube a red background, or this:
FORMAT_STRING(THIS)="\H\e\l\l\o"
...to change the format of everything in the current subcube.
...

For example, in the Adventure Works cube, if you add this calculation to
the MDX Script, then cents show only for [Internet Sales Amount] <
$100K:

FORMAT_STRING(this) = iif([Measures].[Internet Sales Amount] < 100000,
"Currency", "$#,###,###");
End Scope;

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Deepak - but I guess my question is - can I set this in the
'formatstring' property of a measure while in BI Dev Studio? I don't
see the option to put in an expression where I can simply add the IIF
statement there. Am I stuck with having to make all of these
calculations rather than pull straight from the cube with a normal
measure?

Again - I appreciate the response....

From what I've found, there appears to be no option to directly set the
FORMAT_STRING property of a measure to an MDX expression. If Chris or
Mosha are listening, they might be able to shed some more light on
this...


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.developersdex.com ***