Macro to Show or Export Pivot Table Properties in a Report

[dapoole]

Hi there,

Is it possible to create a macro that when run will show or export pivot table properties in a report? For example, if you right click on a pivot table cell then select 'Show Properties in Report' you can select items indiviudally. This can take a long time if you have a lot of properties to include. Is it possible to run a macro to show/export these pre-defined properties?

TIA

 

[Jerry Sullivan]

For example, if you right click on a pivot table cell then select 'Show Properties in Report' you can select items indiviudally?

Hi dapoole, What version of Excel are you using? I haven't seen that right-button menu item in Excel 2007 or 2010 (for Windows).

A macro could certainly read PivotTable properties and display them. Which properties do would you want to read?

 

[dapoole]

Hi there,

Thanks for your reply. I am using Excel 2010. The right-click option 'Show Properties in Report' only shows when you appear to be at the root of your raw data. For example I've created a basic pivot table which consists of a count of clients that meet particular report filter values. I then include the 'customer number' into the row labels box. Then when I right click on the customer number on the pivot-table the option 'Show Properties in Report' appears, from which I can include any data from the cube. I just want a macro to show these 'properties' in a report automatically.

Hope that helps you understand.

TIA.

 

[Jerry Sullivan]

Ah....I didn't realize that you had an OLAP cube data source. I haven't noticed that option when I've used cubes.

There's a 'ShowAll' option after clicking the 'Show Properties in Report' item.
Are you wanting a macro that provides the same result as if the user clicked the 'Show Properties in Report' then 'ShowAll'?

How would you want to trigger the running of that macro?

 

[dapoole]

Thanks again. There are quite a few properties in the cube so the ShowAll is not really an option for me. I'd like to be able to run a macro to show then export a limited number of properties, such as: title, forename, surname, address1, address2, address3, town, county, postcode, country, telno1, ... if you get my drift. I dont mind how the macro is run just as long as it functions. Do you think this is possible?

 

[Jerry Sullivan]

I'd suggest that you record a macro while you manually display a few of the properties that you want to show. This will give you the correct syntax for referencing the top level rowfield.

When I did this with an example cube, the recorded macro looked like this....

Sub Macro1()
'
' Macro1 Macro
'
    ActiveSheet.PivotTables("PivotTable2").ManualUpdate = True
    ActiveSheet.PivotTables("PivotTable2").CubeFields( _
        "[Fiscal Periods].[Fiscal Period]").AddMemberPropertyField Property:= _
        "[Fiscal Periods].[Fiscal Period].[Fiscal Period].[Fiscal Month]"
    ActiveSheet.PivotTables("PivotTable2").CubeFields( _
        "[Fiscal Periods].[Fiscal Period]").AddMemberPropertyField Property:= _
        "[Fiscal Periods].[Fiscal Period].[Fiscal Period].[Fiscal Period]"
    ActiveSheet.PivotTables("PivotTable2").ManualUpdate = False
    Range("I11").Select
End Sub

Once you have that syntax, you can substitute the names of your PivotTable, top level RowField, and List of Properties into the code shown below.

Sub DisplayMyProps()
    Dim vPropsList As Variant
    Dim i As Long

    
    vPropsList = Array("Fiscal Month", "Fiscal Period", "Fiscal Quarter")

    
    With ActiveSheet.PivotTables("PivotTable2")
        .ManualUpdate = True
        With .CubeFields("[Fiscal Periods].[Fiscal Period]")
            For i = LBound(vPropsList) To UBound(vPropsList)
                .AddMemberPropertyField Property:= _
                    "[Fiscal Periods].[Fiscal Period].[Fiscal Period].[" _
                    & vPropsList(i) & "]"
            Next i
        End With
        .ManualUpdate = False
    End With
End Sub

If you want help adapting the code, please post the recorded macro you create while manually displaying a few properties.

Once displayed, what parts of the PivotTable do you want to export and in what format?
Do you just want to take the entire PivotTable report and paste it as values into another workbook, or something else?