software

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
support.microsoft.com/kb/2347...22120121120120).

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 !
TimmoSince 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 quot;PivotTable1quot;. That's all !
Inscrutable why this functionality is not available with the Excel GUI !

Sub HideEmptyMembers()
Dim pvtTable As PivotTable
Set pvtTable = ActiveSheet.PivotTables(quot;PivotTable1quot;)
pvtTable.DisplayEmptyColumn = False
pvtTable.DisplayEmptyRow = False
pvtTable.RefreshTable
End Sub

Sub DisplayEmptyMembers()
Dim pvtTable As PivotTable
Set pvtTable = ActiveSheet.PivotTables(quot;PivotTable1quot;)
pvtTable.DisplayEmptyColumn = True
pvtTable.DisplayEmptyRow = True
pvtTable.RefreshTable
End Subquot;Timmoquot; wrote:

gt; given:
gt; Excel 2003 Pivot table with a SQL Server OLAP cube (access via 'Microsoft
gt; OLEDB Provider for OLAP Services' works fine)
gt;
gt; problem:
gt; Items (rows or columns) of the excel Pivot table are not shown as long as
gt; the items (rows or columns) contain no data ! Typically this applies if
gt; detailed items are opened within the Pivot table.
gt; Excel Pivot tables allow to check 'Show items with no data' in the 'Field
gt; Settings ...' dialog (window 'Pivot Table Field') to display the fields/items
gt; (rows or columns) even if they contain no data. However Excel Pivot tables
gt; with an underlying OLAP cube do NOT allow this setting (see
gt; support.microsoft.com/kb/2347...22120121120120).
gt;
gt; Question:
gt; Is there a possibility (workaround), to display/show items with no data,
gt; i.e. emty rows or columns in an Excel Pivot table even if the Excel pivot
gt; table relies on an OLAP cube ?
gt; Setting 'for emty cells, show ...' in 'Table Options' didn't help. Single
gt; empty cells in rows or columns were shown correct, but empty rows or columns
gt; that are completely empty are still not shown.
gt;
gt; All hints welcome. Thank you for your help !
gt; Timmo
gt;