Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLAP Pivot table - How to show items with no data ?
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/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 ! Timmo |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
OLAP Pivot table - How to show items with no data ?
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 "Timmo" wrote: 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/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 ! Timmo |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel Macro to Copy & Paste | Excel Worksheet Functions | |||
pivot table - New Data | Excel Discussion (Misc queries) | |||
Pivot Table Data Filter Problem | Excel Discussion (Misc queries) | |||
Pivot Table Data Field Query | Excel Worksheet Functions | |||
Pivot Tables, Help? | Excel Discussion (Misc queries) |