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 "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.

format string for dutch language

Hi everyone,

I have some measure with the following format string : #,#0.00

Now in Reporting Services my value is 1,975,647,808.55 instead of 1.975.647.808,55

I know that I can't change the format string to #.#0,00

This gives bad results in reporting Services

Any idea or solution?

thanks!!

Filip

Hello. I do not have a complete solution. We are struggling with this problem in Sweden as well.

You can use this format string '### ### ### ###.##' in SSAS2005.

It will help with avoiding the problem that all numbers will be text when you export to Excel.

The downside is that you will have to accept a dot (.) as a decimal pointer.

I have found no other way to solve this,

Kind Regards

Thomas Ivarsson

Format measure from milliseconds to HH:MM:SS

Hi,

I have a measure that contains milliseconds, I want to display it in the HH:MM:SS format.

How to specify the format string?

please help.

Regards

Vijay R

Hi Vijay,

This thread from the public SQL Server OLAP Newgroup discusses some approaches:

http://groups.google.com/group/microsoft.public.sqlserver.olap/msg/37b9ae771bc762bc

>>

microsoft.public.sqlserver.olap > How to format & display a measure which is in seconds to HH:MM:SS

This MSDN link discusses FORMAT_STRING options for cube measures:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdma
d/agmdxadvanced_2aur.asp

This earlier post shows how to format hh:mm:ss

http://groups-beta.google.com/group/microsoft.public.sqlserver.olap/msg/
13d3af02580a7257

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/olapdma
d/agmdxadvanced_2aur.asp

This earlier post shows how to format hh:mm:ss

http://groups-beta.google.com/group/microsoft.public.sqlserver.olap/msg/
13d3af02580a7257

From: Deepak Puri (d...@.progressive.com)
Subject: Time format Measure
This is the only article in this thread
View: Original Format
Newsgroups: microsoft.public.sqlserver.olap
Date: 2004-04-24 21:53:31 PST

Try the following format, if time is < 32K seconds:

With Member [Measures].[FormattedASA] as
'TimeSerial(0,0,[Measures].[AvgSpeedAnswer])',
FORMAT_STRING = 'hh:nn:ss'
>>