Hi,

I am just trting to display 0.00 for all the empty cells in an calculated
member (MDX Expression) and i used the following syntax.

format(IIF([Measures].[Units Sold]<> 0,
[Measures].[Selling Price]/[Measures].[Units Sold],0),
"##.00;(##.00);#.00;#.00")

It always displays "0" not "0.00". Please let me know what is wrong in the
MDX Expression.

Thanks,

Sam.

Here's a sample query for Foodmart:

'IIF([Measures].[Unit Sales]<> 0,
[Measures].[Unit Sales],0)',
FORMAT_STRING = '##.00;(##.00);0.00;#.00'
Select {[Measures].[Unit Sales],
[Measures].[Format0]} on columns,
[Time].[Month].Members on rows
from Sales

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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

Hi Deepak,

Thanks for your reply. But i want do this as part of the calculated member
with in the Analysis Manager.

I used the same format string in my calculated member and it does not work.
But your's works. The problem is i cannot use the the with clause in the
calculated member with in the Anlaysis Manager.

Please help. This is the last thing in my model.

MINE:
-------
format(IIF([Measures].[Units Sold]<> 0,
[Measures].[Selling Price]/[Measures].[Units Sold],0),
"##.00;(##.00);0.00;#.00")

YOURS:
---------

With Member [Measures].[Format0] as
'IIF([Measures].[Unit Sales]<> 0,
[Measures].[Unit Sales],0)',
FORMAT_STRING = '##.00;(##.00);0.00;#.00'

Select {[Measures].[Unit Sales],
[Measures].[Format0]} on columns,
[Time].[Month].Members on rows
from Sales




"Deepak Puri" wrote:

As you can see, my sample uses the "Format String" cell property of the
Calculated Member, whereas you used the VBA Format() function instead.
In Analysis Manager, under the "Advanced" tab of Calculated Member
Properties, you can set the "Format String" accordingly.


- Deepak

Deepak Puri
Microsoft MVP - SQL Server

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