tim laqua dot com Thoughts and Code from Tim Laqua

5Nov/080

How to add a calculated measure (calculated field) to an Excel 2007 PivotTable with a SSAS data source

As it turns out, you can do it programatically as explained by Allan Folting here:
Microsoft Excel: Common Questions Around Excel 2007 OLAP PivotTables:
http://blogs.msdn.com/excel/archive/2008/02/05/common-questions-around-excel-2007-OLAP-PivotTables.aspx

And the parts that I have to keep looking up (I use my blog as a notebook for things I don't want to forget ;-):

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

He also mentions that you can expose these members to Excel Services 2007 by creating the new objects and then removing the VBA code - very useful article.

29Sep/085

Changing Server Names in Excel 2007 Embedded Workbook Connections

Ok, so you want to migrate servers, eh? Well - all your business users pry have a couple thousand Pivot Reports lying around that point to the old server. We need to point all references to the old server at the new server.

There are two places these references exist (afaik) - The My Data Sources folder (Windows XP / Excel 2007) which holds all of the odc objects for the logged in user, and the real pain - Embedded data sources in Excel Workbooks.

For the odc objects - you can just loop through the My Data Sources folder files and do a little find/replace action.

For the Excel files - we need to first find all the files, then loop through them and hunt for embedded Connections. Once we find a connection, we can just blindly replace occurrences of the old server name with the new server name. The first thing we need here is a function we can call recursively to go hunt down all the excel files - then we loop through and play with the connections (when found).