How do I force a pivot column if there is no data?

[John Brock]

Here is a simplified example of what I need to do: Let's say the
database I am pivoting includes columns (fields) for Office, Quarter,
Region, and Revenue. There is a row for every Office that had
Revenue in a given Quarter (Q1, Q2, Q3, Q4), but not all Offices
have Revenue for all Quarters. In the Pivot Table Wizard I put
Region in the Row Area, Quarter in the Column Area, and Revenue in
the Data Area, so the pivot table shows Regions on the left and
Quarters across the top, while summing Revenue over Offices.

Couldn't be simpler, right? But here is the complication: It may
happen in the database I am working with that there is no Q3 revenue
for any office at all. I.e., the database contains no rows with
"Q3" in the Quarter column. Nevertheless the pivot table must show
a Q3 column, even though there is nothing in it.

So how can I make this happen? When I pivot off of this database
I only get columns for Q1, Q2, and Q4. How can I force the pivot
table to include a Q3 column as well (and in the proper sequence)?

As I said, this is simplified. In reality the values in the Quarter
column will often change (always Q1-4, although if a Q5 shows up
by mistake I want to see it), I will be doing many different pivots
off of the data, and each pivot must always show all four quarters.
Missing quarters will be the rule rather than the exception. Also,
I will be creating these pivot tables programmatically using VBA,
and I will be counting rows as well as summing. I am hoping that
whatever works for my simple example will also be usable for my
real-life project.

Thanks in advance for any advice!

 

[Jim Thomlinson]

In the Field Settings for your quarter field have you checked off show items
with no data?

 

[Sandy Mann]

John,

I had a similar problem when I wrote a Budget Spreadsheet at work. Not
every Department would have an expenditure within a given month. I solved
it by adding all the departments in the Department Column but in hidden Rows
between the Headers and the real data. This gave me a PT Data field for
every Deparment regardless of whether or not there was an entry for that
Department in the Data.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[John Brock]

Are you talking about hidden rows in the actual database worksheet?
I'm reluctant to do that, because there are end users involved who
might stumble upon those rows and become confused or mess things up.

Also, I am not sure If I could get the result I want by adding just
four rows for the four quarters. Remember I am going to be doing
fairly complicated pivots with all sorts of filters. Wouldn't I
need a different set of hidden rows for each pivot table?

What might work is a way to programmatically add appropriate "hidden
rows" when I create each pivot table. (And even then I have a
problem if I am counting rather than summing rows). Or else just
a way to assert at creation time that the table will contain at
least a specific set of values.

 

[John Brock]

I can see in Google Groups that Jim Thomlinson has suggested that
I try the "Show items with no data" checkbox. For some reason
though his post hasn't shown up in my newsreader yet, so I can't
respond directly.

It doesn't work though. That checkbox is only for when you are
using a page field, and some of your pages have data for a column
while others don't. In my case though the string "Q3" never appears
anywhere in the database, so there is no way Excel can guess what
I want. (How would it know I didn't really want "Q3.5")?

Or to look at it another way, what if I wanted a region called
"Antarctica" to show up in every pivot table, even though no rows
in the database are ever listed as being in that region? There is
obviously no way I could do that using "Show items with no data"
-- somewhere I am going to have to explicitly supply the name!

 

[Sandy Mann]

I'm reluctant to do that, because there are end users involved who

might stumble upon those rows and become confused or mess things up.

You could hide them in code as xlVeryHidden which would stop end users
"stumbling upon" them but of course as with all things in XL it is not
foolproof against determined users.

four rows for the four quarters. Remember I am going to be doing
fairly complicated pivots with all sorts of filters.

Of course I don't know what complicated things you want to do but if you
start off with the whole database as your PT range then would it not work
out anyway?

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk