Is there a way to create pivot tables based on business rules?

Hi guys,
Well he question is pretty self explanatory, I want to create multiple pivot tables with different filters based on business rules such as if a row contains the word application include it in the pivot table, etc. but the activity just allow to create simple full pivot tables.

You could do it with VB Code. The attached is an example of invoking code on a simple table in an excel sheet to create a new worksheet with a simple Pivot Table based on a single column selection and single filter. You could adapt this on your data by changing the parameters where required.

'Create the excel objects to work on
Dim excel As Microsoft.Office.Interop.Excel.Application
Dim wb As Microsoft.Office.Interop.Excel.Workbook
Dim ws As Microsoft.Office.Interop.Excel.Worksheet
Dim p_ws As Microsoft.Office.Interop.Excel.Worksheet
Dim rng As Microsoft.Office.Interop.Excel.Range
Dim p_rng As Microsoft.Office.Interop.Excel.Range
Dim pt As Microsoft.Office.Interop.Excel.PivotTable

excel = New Microsoft.Office.Interop.Excel.ApplicationClass
wb = excel.Workbooks.Open(Filename)
excel.Visible=False

ws=CType(wb.Sheets(TargetSheet),Microsoft.Office.Interop.Excel.Worksheet)
p_ws=CType(wb.Sheets.Add,Microsoft.Office.Interop.Excel.Worksheet)
rng=CType(ws.Range(SourceRange),Microsoft.Office.Interop.Excel.Range)
p_rng= CType(p_ws.Cells(1,1),Microsoft.Office.Interop.Excel.Range) 
p_ws.Name="PivotSheet"

'Create the Pivot Table cache and Pivot Table objects
Dim oPivotCache As Microsoft.Office.Interop.Excel.PivotCache=Nothing
Dim oPivotTables As Microsoft.Office.Interop.Excel.PivotTables=Nothing
Dim oPivotTable As Microsoft.Office.Interop.Excel.PivotTable=Nothing

oPivotCache=CType(wb.PivotCaches().Add(Microsoft.Office.Interop.Excel.XlPivotTableSourceType.xlDatabase,rng),Microsoft.Office.Interop.Excel.PivotCache)
oPivotTables=DirectCast(p_ws.PivotTables(Type.Missing),Microsoft.Office.Interop.Excel.PivotTables)
oPivotTable=oPivotTables.Add(oPivotCache,p_rng,"Summary",Type.Missing,Type.Missing) 'Provide the PivotTable name

'Creation of pivot Fields' 
Dim oPivotField As Microsoft.Office.Interop.Excel.PivotField=Nothing
Dim oPivotValues As Microsoft.Office.Interop.Excel.PivotValueCell=Nothing

oPivotField=CType(oPivotTable.PivotFields("Col1"),Microsoft.Office.Interop.Excel.PivotField)'Specfiy the column name to move to the columns'
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlColumnField

oPivotField=CType(oPivotTable.PivotFields("Col1"),Microsoft.Office.Interop.Excel.PivotField)'Specfiy the column name to move to the values'
oPivotField.Orientation=Microsoft.Office.Interop.Excel.XlPivotFieldOrientation.xlDataField
oPivotField.Function=Microsoft.Office.Interop.Excel.XlConsolidationFunction.xlCount
oPivotField.Name="Count of IT Assets"

'Save and Close Workbook
wb.Save
wb.Close

PivotTables.zip (7.9 KB)

3 Likes

The above example doesn’t include code to add a selection to your filter. You simply add code such as below against the PageField object to add a specific value:

oPivotField.CurrentPage = "Infrastructure"

PivotTablesWithFilter.zip (7.9 KB)

2 Likes

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.