Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Excel and ‘Percent’ Formats for Calculated Measures

with 7 comments

Today I was with a customer and created a calculated measure that was formatted as a percentage. The formatting showed up fine in the cube browser but did not in Excel, which was a bit strange given that other percentage calculated measures on the cube seemed to be working fine. Now I knew that Excel doesn’t pick up the formatted_value of a cell in a cellset, but instead takes the format_string associated with each cell and interprets this as an Excel format inside a pivot table, and this explains why sometimes SSAS formats don’t work in Excel. So clearly something was going wrong with Excel interpreting the format string I’d defined.

Finally the guy I was working with, Andrew Baker, worked it out. Look at the two following calculated measures:

CREATE MEMBER CURRENTCUBE.MEASURES.C1 AS 0.99, FORMAT_STRING=’PERCENT';
CREATE MEMBER CURRENTCUBE.MEASURES.C2 AS 0.99, FORMAT_STRING=’Percent';

When you add them to a cube and browse the cube in the cube browser, you see this:

In Excel you see this:

What’s the difference? Yes, you guessed it, when it comes to the built-in SSAS format string types like ‘Percent’, Excel is case-sensitive. So a format string like ‘PERCENT’, while it’s valid from an MDX point of view, gets ignored by Excel whereas ‘Percent’ is correctly interpreted as a percentage format.

 

Written by Chris Webb

November 3, 2010 at 11:35 pm

Posted in Uncategorized

7 Responses

Subscribe to comments with RSS.

  1. Yes, I found this out a few years ago. I couldn’t believe it. I now try and stay away from the standard formats.

    David Shroyer

    November 4, 2010 at 1:46 am

    • I greatly appreciate this post. I would also appreciate it if ReportBuilder would do the same thing (utilize Fields!(“FORMAT_STRING”) automatically as the format of the tablix cell!

      Mark Davis

      November 4, 2010 at 2:23 pm

  2. i also prefer using standard formats as SSRS can neither handle PERCENT nor Percent when you want to use the cube’s formatting within your report

    Gerhard Brueckl

    November 4, 2010 at 7:51 pm

  3. It would be great if you could investigate deeper into formatting. I have different issues making formats work in Excel. It seems to be dependent on user locale or something like that, since on some computers things like “0,00#” work, on others no formatting is applied.

    QQ

    November 6, 2010 at 10:23 pm

  4. […] keresztül, és az OLAP oldalon a számformátumot 'PERCENT'-ként definiáljuk, akkor a Pivot tábla nem fogja százalékos formátumban megjeleníteni az adott mutatót. Ugyanakkor a formázást 'Percent'-re állítjuk, akkor tökéletesen jól formáz a Pivot […]

  5. […] format for the PivotTable. Unfortunately it can’t always do the conversion successfully – I blogged about a problem with the Percent format some time ago and this is still a problem with Excel 2013. Excel also doesn’t support formats […]

  6. […] format for the PivotTable. Unfortunately it can’t always do the conversion successfully – I blogged about a problem with the Percent format some time ago and this is still a problem with Excel 2013. Excel also doesn’t support formats […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 3,302 other followers

%d bloggers like this: