Common Questions Around Excel 2007 OLAP PivotTables

Today's author: Allan Folting, a program manager who works on the Excel team.

I have gotten lots of questions from customers around specific functionality in Microsoft Excel 2007 PivotTables that is missing when connected to Microsoft SQL Server 2005 Analysis Services Service Pack 2 cubes:

1. Filtering individual calculated members
2. Showing non-visual totals when filtering

I have also gotten lots of requests for the ability to add calculations to OLAP PivotTables using the object model, specifically:

1. Adding calculated members/measures
2. Adding named sets

Today I'll discuss these topics and outline current ways and workarounds to address this.

Behaviors in Excel 2007 specific to Analysis Services 2005 Service Pack 2 (SP2) cubes

Let's start with a bit of background information.

With Analysis Services 2005 SP2+, Excel 2007 uses subselects in the MDX queries generated when filtering is applied to a PivotTable.

In the following example of MDX generated by an Excel 2007 PivotTable, the user is filtering a geography hierarchy to only show United States and Australia which is represented by the subselect part of the query (in bold font).

SELECT NON EMPTY Hierarchize({DrilldownLevel({[Customer].[Customer Geography].[All Customers]})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS
FROM
(

SELECT ({[Customer].[Customer Geography].[Country].&[United States], [Customer].[Customer Geography].[Country].&[Australia]}) ON COLUMNS FROM [Adventure Works]

)
WHERE ([Measures].[Internet Sales Amount])
CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS

Subselect support in the OLAP server is required in order for the new Label, Date and Value filtering features to be available in Excel 2007 PivotTables. The reason is that the subselect feature is designed for making Analysis Services calculate visual totals while filtering which was also an explicit design goal for Excel 2007 PivotTables.

When multiple filters are applied to different hierarchies at the same time, the filters are applied in the order that the user added them, using subselects, which ensures an intuitive filter experience where filtering is always based on the totals currently displayed in the PivotTable.

The reason why subselects are only used with Analysis Services 2005 SP2 or newer versions is that Analysis Services 2005 introduced a provider property in SP2 so that client applications can determine whether the provider supports subselects. The provider property is MDPROP_MDX_SUBQUERIES.

By reading this provider property to determine whether subselects are supported, Excel exposes the new filtering features with any OLAP provider that implements this property (and supports other fundamental OLAP provider capabilities needed, I included a link to more information on provider properties that Excel reads in the additional resources section below).

Now, let's discuss the specific features affected by using subselects for filtering. I'll start with filtering individual calculated members.

Filtering individual calculated members with Excel 2007 PivotTables connected to Analysis Services 2005 SP2+ cubes

Calculated members are not supported in subselects with Analysis Services 2005 so Excel 2007 does not offer filtering on individual calculated members for Analysis Services 2005 SP2+ servers.

However, you can choose to display all calculated members, or no calculated members at all, in the PivotTable Options dialog box using the "Show calculated members from OLAP server" option.

Note that this does not apply to calculated members in the Measures hierarchy (calculated measures); those are always displayed regardless of the option.

 
PivotTable Options dialog with the "Show calculated members from OLAP server" option

The screenshot below illustrates the filter drop down in a PivotTable for a hierarchy with calculated members ("Budget Variance" and "Budget Variance %").

Note that the checkboxes for the calculated members are disabled and therefore cannot be unselected.

Calculated members cannot be individually selected when subselects are used for filtering with Analysis Services 2005 SP2+

Non-visual totals with Excel 2007 PivotTables connected to Analysis Services 2005 SP2+ cubes

Another implicit behavior of subselects with Analysis Services 2005 is to always generate visual totals.

For this reason, PivotTables created using Excel 2007 do not allow the user to turn off visual totals with Analysis Services SP2+ cubes.

The first screenshot below illustrates a PivotTable with visual totals. The total for United States is the sum of the states currently in the filter (California, New York and Washington).

PivotTable displaying visual totals

The second screenshot illustrates a PivotTable with non-visual totals. The total for United States is not affected by the fact that some states are filtered out; it is still showing the total sales amount for all existing states.

PivotTable displaying non-visual totals

Workarounds

Individually filtering calculated members and generating non visual totals are important features for common scenarios. The Excel and Analysis Services teams are working together on a future solution for lifting these limitations and this section outlines current workarounds for enabling these features in Excel 2007 with Analysis Services 2005 SP2+.

The workaround for creating PivotTables that support both non visual totals and filtering individual calculated members with Analysis Services 2005 SP2+ cubes is to create PivotTables in Excel 2007 with a pre-2007 version.

PivotTable versioning was introduced in Excel 2000. PivotTables created with a certain version ensures that the PivotTable is compatible with the corresponding version of Excel in that it is generally created with the same default settings as in that version of Excel and it exhibits the same behaviors. This is also true when using the Excel PivotTable object model through code. A benefit from this is that users can expect macros created in previous versions of Excel to still work exactly the same way in newer versions of Excel.

The table below outlines the different PivotTable versions supported in Excel as well as which version is the default created in different versions of Excel.

There is an additional PivotTable version supported in Excel 2007, namely version 2 (xlPivotTableVersion11). However, there is no functional difference between version 1 and version 2 PivotTables. Since no version of Excel creates version 2 PivotTables by default, it isn't included in the table above. Version 2 was added in Excel 2007 for completeness although Excel 2003 creates version 1 PivotTables.

For PivotTables with a version number less than 3, Excel will generate Excel 2003 style MDX queries (no use of subselects) and will support filtering calculated members individually and toggling visual totals.

As indicated above, the drawback of working with old version PivotTables is that the new filtering features are not available.

There are two ways in Excel 2007 to create PivotTables with a specific version:

1. Work with Excel 2007 in compatibility mode (this mode is on when you open xls files) since then Excel automatically creates version 1 PivotTables
2. You can explicitly specify the version of a PivotTable when creating it if you create it using the object model (example below)

When Excel 2007 is in Compatibility Mode, version 1 PivotTables are created

The VBA code below creates a version 1 (PivotTable.Version = xlPivotTableVersion10) PivotTable, turns on the new Excel 2007 default layout and turns off visual totals. It is using the sample Adventure Works cube that comes with Analysis Services 2005. You'll need to change the OLAPServerName to your actual OLAP server name and make sure there is a worksheet named Sheet1 which is empty in the area where the PivotTable is placed to try out this code.

Sub CreateV1PivotTable()

    With ActiveWorkbook.PivotCaches.Create(SourceType:=xlExternal, Version:=xlPivotTableVersion10)

.Connection = Array("OLEDB;Provider=MSOLAP.3;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=Adventure Works DW;Data Source=OLAPServerName;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error")
.CommandType = xlCmdCube
.CommandText = Array("Adventure Works")
.MaintainConnection = True
.CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10

    End With 

ActiveSheet.PivotTables("PivotTable1").RowAxisLayout xlCompactRow
ActiveSheet.PivotTables("PivotTable1").InGridDropZones = False
ActiveSheet.PivotTables("PivotTable1").TableStyle2 = "PivotStyleLight16"
ActiveSheet.PivotTables("PivotTable1").VisualTotals = False

End Sub

The screenshot below shows the PivotTable Options dialog box for a version 1 (xlPivotTableVersion10) PivotTable in Excel 2007 with the option to toggle visual totals using the "Include filtered items in totals" checkbox. Non-visual totals are displayed when the checkbox is selected.

OLAP PivotTables with a version less than 3 in Excel 2007 allow users to turn off visual totals in OLAP PivotTables using the PivotTable Options dialog box ("Include filtered items in totals")

Note that if you save to an Excel 2007 file format while Excel is in compatibility mode, all PivotTables with a version number less than 3 will be marked for upgrade to version 3 (xlPivotTableVersion12) and the next refresh you perform on each PivotTable will do the actual upgrade. Once a PivotTable is upgraded to version 3, you'll lose the ability to filter on calculated members individually and toggling visual totals.

If you want to avoid the automatic upgrade, before refreshing the PivotTables, go to each one and set PivotTable.PivotCache.UpgradeOnRefresh to False using the object model. For example, in the immediate window (press ALT+F11 and then CTRL+G to display the immediate window) type and run Activecell.PivotTable.PivotCache.UpgradeOnRefresh = False with the active cell inside the PivotTable.

Turning off PivotTable version upgrade for the selected PivotTable in the Immediate window

Additional resources

Excel 2007 and SQL Server Analysis Services 2005 Service Pack 2

http://blogs.msdn.com/excel/archive/2007/05/06/excel-2007-and-sql-server-analysis-services-2005-service-pack-2.aspx

Excel 2007 Document: Designing SQL Server 2005 Analysis Services Cubes for Excel 2007 PivotTables

http://www.microsoft.com/downloads/details.aspx?FamilyId=2D779CD5-EEB2-43E9-BDFA-641ED89EDB6C&displaylang=en

OLE DB for OLAP Properties Used by Excel 2007

http://msdn2.microsoft.com/en-us/library/bb407625.aspx

Creating calculated members/measures and sets in Excel 2007 OLAP PivotTables

Excel doesn't offer a user interface for creating calculated members/measures and sets in PivotTables connected to OLAP cubes. However, in the object model there is support for creating personal calculations and sets in PivotTables with OLAP data sources.

The CalculatedMembers collection holds calculations and sets private to a PivotTable and it has an Add method for creating new calculations and sets.

Below are examples of creating a calculated measure, a calculated member and a named set using the CalculatedMembers collection of the PivotTable object. All the examples are using the sample Adventure Works database that comes with Analysis Services 2005.

Example of adding a calculated measure in an Excel PivotTable connected to an OLAP data source:

Sub AddCalculatedMeasure()

Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String

Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[Measures].[Internet Sales Amount 25 %]"
strFormula = "[Measures].[Internet Sales Amount]*1.25"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember

End Sub

Calculated measure "Internet Sales Amount 25%" added to an OLAP PivotTable using the CalculatedMembers collection in the object model

Example of adding a calculated member to the Adventure Works Product level of the Product Categories hierarchy in an Excel PivotTable connected to an OLAP data source:

Sub AddCalculatedMember()

Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String

Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38 25 %]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].[Mountain-100 Silver, 38]*1.25"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedMember
pvt.ViewCalculatedMembers = True

End Sub

The screenshot below shows the PivotTable with the calculated member ("Mountain-100 Silver, 38 25%") added to the Product level of the Product Categories hierarchy.

Calculated member "Mountain-100 Silver, 38 25%" added to an OLAP PivotTable using the CalculatedMembers collection in the object model

Example of adding a named set in an Excel PivotTable connected to an OLAP data source:

Sub AddNamedSet()

Dim pvt As PivotTable
Dim strName As String
Dim strFormula As String
Dim cbf As CubeField

Set pvt = Sheet1.PivotTables("PivotTable1")
strName = "[My Mountain Bikes]"
strFormula = "[Product].[Product Categories].[Bikes].[Mountain Bikes].children"
pvt.CalculatedMembers.Add Name:=strName, Formula:=strFormula, Type:=xlCalculatedSet
Set cbf = pvt.CubeFields.AddSet(Name:="[My Mountain Bikes]", Caption:="Mountain Bikes")

End Sub

The screenshot below shows the PivotTable with the named set ("Mountain Bikes") added.

Named set "Mountain Bikes" added to an OLAP PivotTable using the CalculatedMembers collection in the object model

Note that Excel 2007 only support named sets consisting of members from a single hierarchy.

Also note that even though Excel Services 2007 doesn't support Excel workbooks with macros/code, you can still use code as illustrated here to create calculated members/measures and named sets, remove the code after creating these and then publish the workbook to Excel Services. This way the calculations created in Excel can be exposed through Excel Services.