Excel 2007 : Pivot Tables - Combination of "Show Items with no data" and "Grouping - Group By Date

Hi,

first of all I would like to thank everybody involved in this forum answering questions and solving problems.
I have learned a lot from the posts and replies.

I have a problem in excel 2007 with pivot tables.

DATA
The underlying data is a list of items which have been created in the database and the actual date the item was created on.
So basically two fields, date (DD/MM/YYYY) and summary.
At the bottom of this post I listed some demo data

FUNCTIONALITY
I want to present this data in a bar chart, grouped by month, showing for the months where no items where created an empty bar, and to have the posibility to select a date range ...


WHAT HAVE I TRIED
I create the pivot table and set the values to Count of Summary and set the row label to Date.
This creates the pivot table with for every unique date a count of items on this date. Perfect

I want to see the data grouped by month so I right click one of the date fields in the pivot and select "Group ..."
There I select Months & Years and leave the grouping to auto.
This groups the items by month, however months with no items in the underlying data are not displayed.

To display those, I set on the field settings of the Date Row, the option under layout and print : "Show items with no data".
This generates for every month with no underlying items an empty row.

At this moment everything looks ok, but now I want to filter the pivot within a certain date range.
Therefore I select again "Group ..." and change
the "starting at" to eg 01/07/2010 (DD/MM/YYYY format)
& "ending at" field to eg 01/07/2011 (DD/MM/YYYY format)

The pivot table shows a grouping field < 01/07/2010, another field > 01/07/2011, and for 2010 all the months (jan to dec) and for 2011 all the months (jan to dec).

I however would expect that
- jan / feb / mar / apr / may / jun is not shown in the 2010 group
- aug / sep / oct / nov / dec is not shown in the 2011 group.

I have the ability to deselect via a filter the < 01/07201, and also eg 'jan'.
However when I deselect 'jan' via the filter it dissapears from the 2010 group (which is good) but also from the 2011 group (which is not good)

Is this something that is possible with pivot tables?

I control the underlying data as well, so if needed I can change the layout of the datasource.
However a workaround to create for every grouping a dummy empty record in the underlying data is not an option as this would generate over 1 million records.


Demo data

Date Summary
1/01/2010 Item 1
1/03/2010 Item 2
1/05/2010 Item 3
2/01/2010 Item 4
5/05/2011 Item 5
9/07/2011 Item 6
10/12/2009 Item 7
10/12/2010 Item 8
10/12/2011 Item 9
21/01/2010 Item 10
3/01/2010 Item 11
4/01/2010 Item 12
8/01/2010 Item 13
14/01/2010 Item 14
22/01/2010 Item 15


Hopefully everything is clear ...
If extra info in needed, do not hesitate to ask ...


Thx in advance,

Quirck, welcome to the board, however, please note:

Your post does not comply with Rule 8 of our Forum RULES.

Cross-posting is when you post the same question in other forums on the web.
You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere.

We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Read this to understand why we ask you to do this