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.
- When your queries return a lot of data and your network is slow. See page 61 of the SQLCAT Guide to BI and Analytics 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:
SELECT
{[Measures].[D1],[Measures].[D2]}
DIMENSION PROPERTIES PARENT_UNIQUE_NAME,HIERARCHY_UNIQUE_NAME
ON COLUMNS
FROM
[Adventure Works DW2008]
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS
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:
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 Rick, do you mean that when you tried to load the data as a single csv file that you ran out of memory – so you split the file and loaded it into separate Excel workbooks?
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 hope I explained myself
thank you
Rick
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?
Reblogged this on Sutoprise Avenue, A SutoCom Source.
Hi,
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?
Regards,
Nidhin
I have no idea, sorry – my VBA knowledge is fairly limited…
Now it is working. Replaced the line of code,
Sheets(“Sheet1″).PivotTables(“PivotTable1″).PivotFields(“[region].[region]”).CurrentPageName = “[region].[region].&[” & v_region & “]”
with the below line of code
Sheets(“Sheet1”).PivotTables(“PivotTable1”).PivotFields(“[Region].[Region].[Region]”).CurrentPageName =
“[Region].[REgion].&[” & V_Region & “]”
I love your blog.
Thank you very much for allllll the interesting contents.
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.
Thanks Chris. Will proceed further with the above mentioned way.
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.
Thanks
Yogitha
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
[Expense Submitted Currency] – currency dimension
[Submitted Currency ID] -Attribute (KeyColumn -Currency_sk) (NameColumn-Currency_ID)
[Measures].[Minimum Expense Currency ID] – measure , source column :Expense_currency_sk aggregate function :min
CREATE MEMBER CURRENTCUBE.[Measures].[Expense Submitted Amount]
AS
sum(existing([Measures].[Expense Amount]))
,FORMAT_STRING = “Currency”, LANGUAGE = strtomember(“[Expense Submitted Currency].[Submitted Currency ID].&[“+CStr([Measures].[Minimum Expense Currency ID])+”.]”).Properties(“Locale ID”)
,NON_EMPTY_BEHAVIOR = { [Expense Amount] } ,
ASSOCIATED_MEASURE_GROUP = ‘Employee Expenses’;
Interesting. First of all, I don’t recommend this approach to setting currency formats because it will also impact things like decimal and thousands formatting as well:
https://blog.crossjoin.co.uk/2008/04/24/currency-formats-should-they-be-tied-to-language/
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.
Hi Chris
Under “OLAP Server Formatting”, if I uncheck all the boxes, should I, in theory, be able to now format a text field into a date field? My table has a column that looks like dates, but I think it must be text. I don’t have the option to change the field format. I feel like this is because the data (and formatting) is coming from the OLAP server. But, when I uncheck the boxes, I still don’t seem have the ability to change the formatting. Any ideas? I have searched high and low and your blog is the closest I have found to an answer.
Thanks!
Matt
No, I would not expect you to be able to format the field (although I may be wrong) – this setting just means Excel doesn’t retrieve the formatting from the server.