OLAP Pivot table - How to show items with no data ?

[Guest]

given:
Excel 2003 Pivot table with a SQL Server OLAP cube (access via 'Microsoft
OLEDB Provider for OLAP Services' works fine)

problem:
Items (rows or columns) of the excel Pivot table are not shown as long as
the items (rows or columns) contain no data ! Typically this applies if
detailed items are opened within the Pivot table.
Excel Pivot tables allow to check 'Show items with no data' in the 'Field
Settings ...' dialog (window 'Pivot Table Field') to display the fields/items
(rows or columns) even if they contain no data. However Excel Pivot tables
with an underlying OLAP cube do NOT allow this setting (see
http://support.microsoft.com/kb/234700/EN-US/#XSLTH4170121122120121120120).

Question:
Is there a possibility (workaround), to display/show items with no data,
i.e. emty rows or columns in an Excel Pivot table even if the Excel pivot
table relies on an OLAP cube ?
Setting 'for emty cells, show ...' in 'Table Options' didn't help. Single
empty cells in rows or columns were shown correct, but empty rows or columns
that are completely empty are still not shown.

All hints welcome. Thank you for your help !
Timmo

 

[Guest]

Since there was no reply to my question at all within the last 6 weeks :-( I
don't want to keep back the solution which I've got from friends in a little
while.

Create the follwing VBA procedures, they will do the work for you.
DisplayEmptyMembers() displays the items with no data and HideEmptyMembers()
hides the items with no data in the pivot table "PivotTable1". That's all !
Inscrutable why this functionality is not available with the Excel GUI !

Sub HideEmptyMembers()
Dim pvtTable As PivotTable
Set pvtTable = ActiveSheet.PivotTables("PivotTable1")
pvtTable.DisplayEmptyColumn = False
pvtTable.DisplayEmptyRow = False
pvtTable.RefreshTable
End Sub

Sub DisplayEmptyMembers()
Dim pvtTable As PivotTable
Set pvtTable = ActiveSheet.PivotTables("PivotTable1")
pvtTable.DisplayEmptyColumn = True
pvtTable.DisplayEmptyRow = True
pvtTable.RefreshTable
End Sub