Below is my Calculated members:

iif(Measures.currentmember is [Measures].[Rx Base] OR
Measures.currentmember is [Measures].[Photo Base] OR
Measures.currentmember is [Measures].[Total Triple Base] OR
Measures.currentmember is [Measures].[Wait Time Base],
SUM([Time].[Year].[2005].Children,
Round(Measures.CurrentMember)),Avg([Time].[Year].[2005].Children,
Round(Measures.CurrentMember*100,0)))

I have "Solve Order" of 10.

For AVG the value for the cell is "85.4" but I wanted it to be "85.4%"
But for SUM it should be just the normal. So If I change the
"Format_String" then it change my SUM value too.


Any help will be really appreciate!!!
Sam

Next if-format works in AS 2005, but I do not test it in AS 2000.

Format_String=IIF(..., '#,#', '#,#.0 "%"')

Ohjoo

"Sam" <msam77 (AT) gmail (DOT) com> wrote

Thanks But it does not work in AS 2000.

Do you have any another ideas?

Sameer


Ohjoo Kwon wrote:

With AS 2000 Enterpise Edition, you can use Calculated Cells to apply
different format for a measure, as i n this Foodmart example:

for '({[Measures].[Sales Average]})'
as 'CalculationPassValue([Measures].CurrentMember, -1, RELATIVE)',
FORMAT_STRING = '#.0"%"'
Cell Calculation [FormatSum]
for '({[Measures].[Unit Sales]})'
as 'CalculationPassValue([Measures].CurrentMember, -1, RELATIVE)',
FORMAT_STRING = '#,#'
select {[Measures].[Unit Sales], [Measures].[Sales Average]} on 0,
Non Empty [Product].[Product Category].Members on 1
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

It looks like that there is no direct way with one single calculated member
in AS2000.

I tested two cases below and they work well with Foodmart 2000.

Case1: Using Calculated Members
with
member measures.a as '[Unit Sales]',format_string='#,##0%'
member measures.b as '[Unit Sales]',format_string='##0'
member measures.c as 'iif([Unit Sales] > 7000, a, b)'
select
{a, b, c, [Measures].[Unit Sales]} on columns,
order(except([Promotion Media].[Media Type].members,{[Promotion
Media].[Media Type].[No Media]}),[Measures].[Unit Sales],DESC) on rows
from Sales

Case2: Using Calculated Cells
with
cell calculation [Unit Sales<7000] FOR '({[Unit Sales]})' AS
'CalculationPassValue([Unit Sales],-1,Relative)',
CONDITION='CalculationPassValue([Unit Sales],-1,Relative)<7000',
FORMAT_STRING='#,##0"%"'
cell calculation [Unit Sales>=7000] FOR '({[Unit Sales]})' AS
'CalculationPassValue([Unit Sales],-1,Relative)',
CONDITION='CalculationPassValue([Unit Sales],-1,Relative)>=7000',
FORMAT_STRING='Currency'
select
{[Measures].[Unit Sales]} on columns,
order(except([Promotion Media].[Media Type].members,{[Promotion
Media].[Media Type].[No Media]}),[Measures].[Unit Sales],DESC) on rows
from Sales

Ohjoo



"Sam" <msam77 (AT) gmail (DOT) com> wrote

Thanks Ohjoo and Deepak.

I am planning to go with Case 1. Good logic.

Thanks
Sameer

Ohjoo Kwon wrote: