Formatting "scoped" calculated members

Hi,

How do I format calculated members that have been modified in a Scope-statement?

Example:

CREATE MEMBER CURRENTCUBE.Test1
AS NULL,
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [Measure1], [Measure2] },
VISIBLE = 1 ;

SCOPE ([Company].[Company].children,[Measures].[Test1]);
This = [Measures].[Measure1]/[Measures].[Measure2];
END SCOPE;

My calculated member "Test1" looses its formatting in the Scope-statement and is therefore no longer formatted as "#,#.00". How can I change this?

Try the following:

CREATE MEMBER CURRENTCUBE.Test1
AS NULL,
FORMAT_STRING = "#,#.00",
NON_EMPTY_BEHAVIOR = { [Measure1], [Measure2] },
VISIBLE = 1 ;

SCOPE ([Company].[Company].children,[Measures].[Test1]);
This = [Measures].[Measure1]/[Measures].[Measure2];
FORMAT_STRING(THIS) = '#,#.00';
END SCOPE;

FORMAT_STRING For Calculation

I have a calculation that gives the evolution of a certain measure over 1 month in percentage.

MAR2006 : 1000

APR2006: 1100

> calculation = 10%

MAR2006 : 1000

APR2006: 900

--> calculation = -10%

This works perfect ... but the users of my application ask me if I can put a '+' sign for positive evolutions.

In the first case they want to see '+10%' in stead of '10%'

This is the FORMAT_STRING I am using : "#0.00%"

Any idea ?

Hi Christophe,

You need to include two sections in your format string definition, the first for the positive values and the second for the negative values. Here's an example from Adventure Works which does what you want, I think:

with

member measures.test as (7000000 - [Measures].[Internet Sales Amount])/[Measures].[Internet Sales Amount]

, format_string='+#0.00%; -#0.00%'

select measures.test on 0,

[Date].[Calendar Year].members on 1

from [Adventure Works]

HTH,

Chris

|||

THANKS A LOT.

Just tested it and it works.

Format_String Currency lost by division

Hello!

I use AS 2005 SP1.

If I create a calculated measure "C" with a currency measure ("A") and a non-currency measeasure ("B") like
C=A/B
and define the format_string for the calculated measure as currency. The result is still a non-currency value. Why?

Thanks in advance!
Ole JepsenIf you explicitly specified FORMAT_STRING='Currency' for the calculated measure C, then it will be formatted as currency.

Format_String causes crash excel 2007

Hello,

I have the following mdx script command :

SCOPE ({[Rubrieken].[Hierarchy].&[32]});

FORMAT_STRING(THIS) = "#,#.00";

End Scope;

when I now generates an excel 2007 olap report, I get the correct formatting.

But when I change the MDX to

SCOPE ({[Rubrieken].[Hierarchy].&[32]});

FORMAT_STRING(THIS) = "#,#.00 d";

End Scope;

So now I expect a 'd' after the figures.

But when I generate the same report in excel 2007, excel crashes.

Anyone an idea?

thx

This is only a guess but you can try "#,#.00d" .

It can be the space that is causing the problem.

When you write a percent format you can use "##.##%" so if my suggestion do not work SSAS2005 expects a code it understands and d is not supported.

HTH

Thomas Ivarsson

|||

Hi Thomas

I tried it, but it also cause excel to crash.

The weird thing is that in the 'browser' of Analysis Services the formatting is shown correct.

So I think that it is a bug in excel.

|||

I can confirm this problem with Excel 2007. It works fine in ProClarity Professional 6.3

Yes, it looks like a bug in Excel 2007.

Regards

Thomas Ivarsson

FORMAT_STRING "Currency" returns different currencies

hi!

i'm just getting to know SSAS. i created a standard measure and a calculated measure. the standard measure returns my values in euros, whereas the calculated measure displays them in swiss francs. the analysis server language (which one can set in sql server management studio) is German (Germany). my operating system language is German (Switzerland) though. seems as if calculated measures would read their standard value out of the system language, instead of the SSAS language - is that true? and how can it be fixed?

thank you,

Nico

Please check the following blog - http://www.sqljunkies.com/WebLog/mosha/archive/2005/10/13/mdx_format_currency.aspx it should help you to set up the formatting of currencies the way you want it.