Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1
Default Show only relevant no-data items in pivot table

Currently using Excel 2003

I have two pivot tables based upon same data source. Both are summarised in
rows by two data items, but second table may not necessarily contain same
number of rows because of page selection criteria.
In older version of Excel I could "show items with no data" and this would
ensure that both pivot tables would always have the same number of rows.
When I try to build this sort of table and "show items with no data"
a) First data item shows all the data items for irrelevant categories (i.e.
these are categories that can not (should not) appear because they are
definitively excluded by page selection item.
b) Second data item is attempting to show empty data lines for data items
that only belong to particular sub-categories of the first data item. (e.g.
if first data item had category "Nurse" and "Administrator", and second data
item had category "Staff Nurse" which will only ever be a valid category if
the first data item is "Nurse", the pivot table is now showing this group
under "Administrator" as well.)

I can deal with problem "a" by manually unflagging the unwanted categories.
However this approach doesn't work for problem "b".
  #2   Report Post  
Junior Member
 
Posts: 1
Cool

Quote:
Originally Posted by Workforce_info_Kings_NHS View Post
Currently using Excel 2003

I have two pivot tables based upon same data source. Both are summarised in
rows by two data items, but second table may not necessarily contain same
number of rows because of page selection criteria.
In older version of Excel I could "show items with no data" and this would
ensure that both pivot tables would always have the same number of rows.
When I try to build this sort of table and "show items with no data"
a) First data item shows all the data items for irrelevant categories (i.e.
these are categories that can not (should not) appear because they are
definitively excluded by page selection item.
b) Second data item is attempting to show empty data lines for data items
that only belong to particular sub-categories of the first data item. (e.g.
if first data item had category "Nurse" and "Administrator", and second data
item had category "Staff Nurse" which will only ever be a valid category if
the first data item is "Nurse", the pivot table is now showing this group
under "Administrator" as well.)

I can deal with problem "a" by manually unflagging the unwanted categories.
However this approach doesn't work for problem "b".
Have some one found the solution?! I'm having the same problem, the sub-categories are showing for categories that they do not belong, the problem "b" related above.

Thx
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I show full detail of merged items in a pivot table? AMellor11 Excel Discussion (Misc queries) 0 November 21st 07 05:36 PM
OLAP Pivot table - How to show items with no data ? Timmo Excel Worksheet Functions 1 March 30th 06 06:03 PM
Pivot table-show all grouped items sergv Excel Discussion (Misc queries) 1 September 5th 05 07:18 PM
Pivot Table - show items with no data HJM Excel Discussion (Misc queries) 3 June 22nd 05 12:51 PM
Pivot Table - Group by Month - Show Items with no data Dan Reynolds Excel Discussion (Misc queries) 0 November 28th 04 01:01 AM


All times are GMT +1. The time now is 08:33 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"