default pivottable page filter when parameter not present
 

Hi

My problem occurs when I run a macro to update pivot tables when new
data has been populated into the underlying records.

When the data records don't contain my reporting parameter during a
particular week, my macro will default the page filter to "All" (or some
other parameter) and not to the reporting parameter required since it is not
present in this week's data. The problem arises when I run the macro during
the following week and my reporting parameter is once again present in the
data set. Since my page filter was pegged at "All" the previous week, it will
not return to my targed paramater but will stay at "All". How do I
programmatically peg my page filter to my reporting parameter even when the
data parameter isn't present in data set? Instead of showing my parameter
when it isn't present in the data set, perhaps it can read "no data" or
something like this...

Thanks in advance,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098

default pivottable page filter when parameter not present
 

In my experience, you can "corrupt" your table by making such erroneous
assignments. I would loop through the items in the Pivotfield/pagefield and
insure the item exists before I try to make the assignment. I generally
then make the assignment using the value of the pivotitem rather than a cell
or variable containing that value.

--
Regards,
Tom Ogilvy


"klysell" wrote:

Hi

My problem occurs when I run a macro to update pivot tables when new
data has been populated into the underlying records.

When the data records don't contain my reporting parameter during a
particular week, my macro will default the page filter to "All" (or some
other parameter) and not to the reporting parameter required since it is not
present in this week's data. The problem arises when I run the macro during
the following week and my reporting parameter is once again present in the
data set. Since my page filter was pegged at "All" the previous week, it will
not return to my targed paramater but will stay at "All". How do I
programmatically peg my page filter to my reporting parameter even when the
data parameter isn't present in data set? Instead of showing my parameter
when it isn't present in the data set, perhaps it can read "no data" or
something like this...

Thanks in advance,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098

default pivottable page filter when parameter not present
 

Hi Tom,

Thanks for your reply. In regards to looping through pivotitems for the page
filter: How would I accomplish this when I have one of six possible values
for this parameter? These values are "SP230", "SP231", "SP232", "SP233",
"SP234", and "SP235", and there is an attendant pivot table for each. What if
the pivot table for SP230 doesn't contain any values for this parameter in
the current week but will in the next week? I'm still a newbie when it comes
to coding, but manage to patch together code that seems to work. I'll look in
the postings for a looping example, but is still doesn't allow me to choose a
"dummy" parameter when my required parameter isn't present.... I would like
to minimize human intervention in having to re-select the page filter from
"All" or "Blank" in the current week to "SP230" in the next week when my
parameter returns to the data set...

Regards,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098


"Tom Ogilvy" wrote:

In my experience, you can "corrupt" your table by making such erroneous
assignments. I would loop through the items in the Pivotfield/pagefield and
insure the item exists before I try to make the assignment. I generally
then make the assignment using the value of the pivotitem rather than a cell
or variable containing that value.

--
Regards,
Tom Ogilvy


"klysell" wrote:

Hi

My problem occurs when I run a macro to update pivot tables when new
data has been populated into the underlying records.

When the data records don't contain my reporting parameter during a
particular week, my macro will default the page filter to "All" (or some
other parameter) and not to the reporting parameter required since it is not
present in this week's data. The problem arises when I run the macro during
the following week and my reporting parameter is once again present in the
data set. Since my page filter was pegged at "All" the previous week, it will
not return to my targed paramater but will stay at "All". How do I
programmatically peg my page filter to my reporting parameter even when the
data parameter isn't present in data set? Instead of showing my parameter
when it isn't present in the data set, perhaps it can read "no data" or
something like this...

Thanks in advance,

--
Kent Lysell
Financial Consultant
Ottawa, Ontario
W: 613.943.9098