Removing Legacy Data from a PivotChart report

[bmwbase-newsgroup]

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.

 

[Andy Pope]

Hi,

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

Cheers
Andy

 

[bmwbase-newsgroup]

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

Hi,

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

Cheers
Andy

e drop down selection box for the columns.

 

[Andy Pope]

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

 

[bmwbase-newsgroup]

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..?

 

[Debra Dalgleish]

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