I have a measure that is defined with an aggregationFunction of ByAccount.

The Field (Target Threshold) can either be summed, average, or LastChild.
All is fine and working.

However, how can I conditionally format this to show ('n/v') if no value,
"##.##%" if an average, or "##,##" if summed.

I have written the logic below to do this using a calculated measure. This
works. However, it is slower. So, is there a way to trap the value AFTER the
BYACCOUNT
has been applied but BEFORE it is displayed?

My calculated measure is below:


IIF ( ISEMPTY([Measures].[Target Threshold]),
'N/V',
FORMAT(
Case when [KPI Type].CURRENTMEMBER.Name = 'R' then max([Measures].[Target
Threshold])
when [KPI Type].CURRENTMEMBER.Name = 'P' then AVG([Measures].[Target
Threshold])/100
when [KPI Type].CURRENTMEMBER.Name = 'N' then sum([Measures].[Target
Threshold]) end,
[KPI Format].CURRENTMEMBER.Name)
)

"SteveM" <SteveM (AT) discussions (DOT) microsoft.com> wrote

Perhaps you could use something like that in MDX Script:

format_string(Measures.ByAccount) = "##.##"
format_string([KPI Type].[KPI Type].[R], Measures.ByAccount) = "##.##%"

==============================Â*================== ==
Mosha Pasumansky - http://www.mosha.com/msolap
Analysis Services blog at http://www.sqljunkies.com/WebLÂ*og/mosha
All you need is love (John Lennon)
Disclaimer : This posting is provided "AS IS" with no warranties, and
confers no rights.
==============================Â*================== ==

And to handle the "n/v" requirement you could use the 4 part format
string syntax as noted in BOL at:

http://msdn2.microsoft.com/en-us/library/ms146084.aspx

eg.

"##.##;-##;##;0;\n\/\v"

--
Regards
Darren Gosbell - SQL Server MVP
Blog: http://www.geekswithblogs.net/darrengosbell

In article <44c116e5$1 (AT) news (DOT) microsoft.com>, moshap (AT) online (DOT) microsoft.com
says...