Wednesday, March 17, 2010

Excel Pivot Tables and Filtering

So I get a post this morning from my client saying everything looks great and they can't find any issues with the latest release. Happy moment, cigar time.

Well, at this point I am over on the project and am working on my own time. That's fine considering that there might be future business there. Ok, so they mention 2 minor items that are annoyances that would be perfect. No obligation to do them but I have a few minutes so I dive in.

5 hours later I have it working. What was the issue? Pivot Tables in Excel and setting their filter values. For those poor saps who have to deal with it in the future, here is some C# code to hopefully save you some time:

private static void HandlePivotTableChanges(string p)
Console.WriteLine("Starting Excel. Please be patient while it loads.");
string format = "'Product_Container_Release_Metrics_Workbook.xls'!Format_Workbook";
Application ExcelApp = new Microsoft.Office.Interop.Excel.Application();
ExcelApp.DisplayAlerts = false;
Console.WriteLine("Opening workbook.");
Workbook wb = ExcelApp.Workbooks.Open(p, m, m, m, m, m, m, m, m, m, m, m, m, m, m);
Console.WriteLine("Running macro.");
Worksheet ws = (Worksheet)wb.Worksheets["Pivot Tables"];
string pivotTable = "Forecast to Go";
PivotTable pt = (PivotTable)ws.PivotTables(pivotTable);
pt.ManualUpdate = true;
PivotField pf = (PivotField)pt.PivotFields("Release (A)");
PivotItems items = (PivotItems)pf.PivotItems(m);

foreach (PivotItem item in items)
if (item.Value != string.Empty) // <--- CRITICAL LINE. Filters must have at least 1 value selected. Exception otherwise.
((PivotItem) items.Item(item.Value)).Visible = false;

pt.ManualUpdate = false;
ExcelApp.Calculation = XlCalculation.xlCalculationAutomatic;
Console.WriteLine("Saving workbook.");
wb.Close(false, m, m);
Console.WriteLine("Closing Excel.");

private static void FindProcessInstances()
Console.WriteLine("Finding open processes.");

private static void KillProcesses(string processName)
Process[] processes = Process.GetProcessesByName(processName);
if (processes.Length > 0)
foreach (var p in processes)

public static void RunMacro(this Application app, string macro)
m, m, m, m, m, m, m, m, m, m,
m, m, m, m, m, m, m, m, m, m,
m, m, m, m, m, m, m, m, m, m


Alejandro said...

Hi Alan, my name is Alex, im working right now with SAS Metadata as part of one proyect and also SAS tables.

Until now i have implemented something like Linq To SAS using the SAS OMI, but its still in the beggining, maybe you could help me to develop a nice Linq To SAS provider this is my email

Best Regards Alex.

Anonymous said...

filtering pivot tables

SAS throwing RPC error

If you are doing code in C#  and get this error when creating a LanguageService: The RPC server is unavailable. (Exception from HRESULT:...