Microsoft Analysis Services, MDX, DAX, Power Pivot, Power Query and Power BI

MDX Cell Properties Supported By Excel

I was wondering the other day (as you do) which of the MDX cell properties Excel PivotTables actually support. This page has all the details on the cell properties that are available in an MDX query but most client tools don’t bother retrieving all of them and Excel is no different. Of course it retrieves the most important properties and it retrieves one or two others, but I couldn’t resist doing a bit of research to find out the exact situation with Excel 2013.

The first thing to note is that you can control which cell properties Excel retrieves for a given connection in the connection properties dialog, in the OLAP Server Formatting section:

If you can live with not getting all of the cell properties back from SSAS there are some scenarios where unchecking all of the boxes in the OLAP Server Formatting section can improve performance:

When you have a large number of databases and cubes on your server, and/or complex security, because a side effect of the way Excel retrieves properties is that it causes all cubes in all databases to be loaded, their MDX Scripts executed and security evaluated. See here for more details.

You can also quite easily see which cell properties Excel is retrieving by looking at the MDX queries it generates (thank you OLAP PivotTable Extensions). Here’s an example of a simple PivotTable query run on a connection which has all of the boxes checked in the OLAP Server Formatted section:

In the cell properties clause of this query you can see the six properties returned. Here’s a breakdown of each of them.

VALUE and FORMAT_STRING

Excel doesn’t actually retrieve the FORMATTED_VALUE cell property, which gives you the measure value with formatting applied by SSAS. Instead, assuming you have the Number Format box checked in the Connection Properties dialog, it retrieves the VALUE property from SSAS (which contains the raw, unformatted measure value) and the FORMAT_STRING property (which contains the format string you defined on the server). It then tries to convert the format string into an Excel 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 defined in the fourth section (see here for more details) of the FORMAT_STRING property for null values, and there are probably lots of other relatively obscure types of formatting it doesn’t support too.

LANGUAGE

The language property of a cell controls things like the currency symbol displayed when you are using the “Currency” built-in format string and the thousands and decimal separators used. Whether Excel returns the language property or not is also linked to the Number Format checkbox in the Connection Properties dialog. I strongly recommend that you do not use the “Currency” format string and the Language property if you are working with multiple currencies, for reasons I outlined here.

BACK_COLOR and FORE_COLOR

I’ve never particularly liked using the BACK_COLOR and FORE_COLOR properties on a cell to do traffic light-style reporting, to be honest, although I know some people love it. However I do use these properties a lot when debugging scoped assignments as seen here. BACK_COLOR is retrieved if you have the Fill Color box checked on the connection properties dialog; FORE_COLOR is retrieved if you have the Text Color box checked on the connection properties dialog.

FONT_FLAGS

Probably the only surprise of this whole exercise was the fact that Excel retrieved the FONT_FLAGS property if you have the Font Style box checked on the connection properties dialog; alas it doesn’t support FONT_SIZE or FONT_NAME. For example if you put the following calculated members on your cube:

CREATE MEMBER CURRENTCUBE.MEASURES.BOLD as "This is BOLD", FONT_FLAGS=1;

CREATE MEMBER CURRENTCUBE.MEASURES.ITALIC as "This is ITALIC", FONT_FLAGS=2;

CREATE MEMBER CURRENTCUBE.MEASURES.UNDERLINE as "This is UNDERLINE", FONT_FLAGS=4;

CREATE MEMBER CURRENTCUBE.MEASURES.STRIKEOUT as "This is STRIKEOUT", FONT_FLAGS=8;

If you drop them into a PivotTable, you will see the following returned:

16 thoughts on “MDX Cell Properties Supported By Excel”

Hi Chris
like your blog
I have a large CSV file that has too many rows to be handled with powerpivot and my system. My solution was to split the table into smaller chunks. The challenge I have is that after bringing the separate tables together into Powerpivot, how do I now analyze all the data as a whole…thank you for your consideration

Hi Chris
thank you for getting back to me
The computer I have at work is a 64 bit system with limited memory. they have provided me with excel 2010 for a 32 bit system
The data is a string of test data in a CSV format that grows by about 50,000 lines per day. Currently I have around 2,000,000 lines @ 9 columns,
Each row has a date time stamp and a lot and batch identifier. So if I split the data by month (which I did) i can have part of the information in one month and part in the next. each month is a separate sheet.
I would like to run a pivot table so I can compare lot to lot, or batch to batch when the data is in different tables (sheets)
Even if I use access to split the tables, I would still like to compare the data between the sheets and that is where I get stuck

I’m still a bit confused – are you saying that you tried loading the data as a single file into Power Pivot and it failed, but when you split it up and loaded it to separate worksheets you were able to load it into PowerPivot using linked tables? Did you try loading the data from the original csv file direct into Power Pivot, or did you try loading that data into the worksheet first?

I was trying to assign the excel pivot field value using the vb script

Sheets(“Sheet1”).PivotTables(“PivotTable1”).PivotFields(“[region].[region]”).CurrentPageName = “[region].[region].&[” & v_region & “]”
but it is giving an error
“UNABLE TO GET THE PIVOTFIELDS PROPERTY OF THE PIVOT TABLE CLASS”!

I have been trying all the possible options for the past 2 days and no luck.Any suggestions?

Hi Chris,
Currently few of our users are facing the below issue while connecting to the cube through excel.
“The MDX function failed because current coordinate is empty”
Strange thing is not all the users are affected, only few are reporting this issue and cube is processing successfully.
Since a lot of MDX Calculations are written in Cube Solution, it seems to be difficult for debugging as well.

Could you please suggest, whether this issue is because of the MDX calculations, or restarting the SSAS Services will resolve the issue for those few users

It sounds like this is an error in a calculation, but I’m not familiar with the error message here. You should use Profiler to try to find out what queries cause this error and then try to work out which calculation(s) in the query are the problem.

Hi Chris ,
I have a doubt. Multiple currency display is not supported in excel 2016.
I am not able to display the amounts in their native currencies on excel 2016 pivot table when I use SSAS cube as my data source. However this works perfectly fine on excel 2013. Is this some sort of a bug ? Is there any workaround for this ?
Any help would be appreciated.

I’ve just tested and dynamic formatting works properly in Excel 2016 for me. It might be a bug that has been fixed, it might be something to do with the format strings you’re using. Can you give me more details?

In order to display native currencies for my ‘expense submitted amount’ measure , I am using this formula in the language property . This gives me the locale ID of the currency sk using which currency symbol is displayed. This works in excel 2013 but not in excel 2016. For some reason only ‘$’ is displayed in excel 2016. Here is my mdx code snippet

Instead you should be setting a format string directly, and in your case that will involve using SCOPE statements on each possible currency, something like this:
SCOPE([Expense Submitted Currency].[Submitted Currency ID].&[1]);
FORMAT_STRING(THIS)=”\$0,0.00″;
END SCOPE

Note that for the format string to work in Excel you need to use a \ in front of the currency symbol to escape it.

Follow Blog via Email

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 7,729 other followers

Public Power BI and SSAS Training Courses

I'm running several SSAS, MDX and Power BI-related training courses through Technitrain in 2017. Check out the Technitrain course catalogue for full details, and to see other upcoming courses from the likes of Alberto Ferrari, Andy Leonard, Allan Hirt and Alex Yates.

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk