I have a pivot chart report which pulls its source data from SQL
Server database. I have created a copy of this file to point to a
different SQL Server (exact same tables). When I refresh the report,
the chart and pivot are refreshed correctly from the new server.
However the original data still remains (as well as the new data) in
the drop down selection box for the columns.

i.e.
the chart is showing ExecuteDate in the ROW area, JobName in the
COLUMN area and the number of minutes each job ran each day as the
DATA. The graph plots each job showing the duration over the last 30
days.

In the JobName dropdown, I can see all of the jobs from the original
SQL Server, as well as the second one.
In reality I have copied this file over 5 servers, so I have lots of
redundant data I can't seem to remove!!

How can I clear this out?

Thanks, Ben.

Hi,

Have a read of Debra Dalgleish's page.
http://www.contextures.com/xlPivot04.html

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
wrote in message
ups.com...

I have a pivot chart report which pulls its source data from SQL
Server database. I have created a copy of this file to point to a
different SQL Server (exact same tables). When I refresh the report,
the chart and pivot are refreshed correctly from the new server.
However the original data still remains (as well as the new data) in
the drop down selection box for the columns.

i.e.
the chart is showing ExecuteDate in the ROW area, JobName in the
COLUMN area and the number of minutes each job ran each day as the
DATA. The graph plots each job showing the duration over the last 30
days.

In the JobName dropdown, I can see all of the jobs from the original
SQL Server, as well as the second one.
In reality I have copied this file over 5 servers, so I have lots of
redundant data I can't seem to remove!!

How can I clear this out?

Thanks, Ben.

Excellent.. And to truly show my lack of knowledge in Excel
programming, can a sort function be added to that macro?



On Mar 29, 9:45 pm, "Andy Pope" wrote:
Hi,

Have a read of Debra Dalgleish's page.http://www.contextures.com/xlPivot04.html

Cheers
Andy
--

Andy Pope, Microsoft MVP - wrote in message

ups.com...

e drop down selection box for the columns.

Not sure what it is you want to sort.

You could try using the macro recorder whilst you do the sorting and then
see if it works when added.

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
wrote in message
ups.com...
Excellent.. And to truly show my lack of knowledge in Excel
programming, can a sort function be added to that macro?



On Mar 29, 9:45 pm, "Andy Pope" wrote:
Hi,

Have a read of Debra Dalgleish's
page.http://www.contextures.com/xlPivot04.html

Cheers
Andy
--

Andy Pope, Microsoft MVP -
wrote in
message

ups.com...

e drop down selection box for the columns.

I was hoping to sort the data in the PivotField dropdowns. The macro
in Debra Dalgleish's example works a treat, though I'd like to sort
the list of items as well as remove redundant items..?


On Mar 30, 7:02 pm, "Andy Pope" wrote:
Not sure what it is you want to sort.

You could try using the macro recorder whilst you do the sorting and then
see if it works when added.

Cheers
Andy

--

It depends which code you're using, but if it's the version for Excel
2000, you can add a line to set the AutoSort, e.g.

For Each pf In pt.VisibleFields
pf.AutoSort xlAscending, pf.SourceName


wrote:
I was hoping to sort the data in the PivotField dropdowns. The macro
in Debra Dalgleish's example works a treat, though I'd like to sort
the list of items as well as remove redundant items..?


On Mar 30, 7:02 pm, "Andy Pope" wrote:

Not sure what it is you want to sort.

You could try using the macro recorder whilst you do the sorting and then
see if it works when added.

Cheers
Andy

--




--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html