How to change PT data source from table/range to external for an existing PT?

[feipezi]

Hi,

Hope I made myself clear in the subject. Every time I try to do that, the option of "Use an external data source" is disabled. If not allowed, why the heck leave it there in the first place. I know if I set up a PT from scratch, I can use Connections (external source). What if I have a whole bunch of PTs and each one has diff layout. It would be a lot of PITA to create each and every one of them.

Thanks in advance.

 

[SkipVought]

Hi,

This seems to be a shortcoming.

When I encounter such a situation, I'd rather query the external data and the do a pivot on the data in my sheet.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[feipezi]

Yes. But what if you have some data with size of over 2 million records.

Thanks.

 

[dhookom]

I can't imagine anyone wanting to see over 2 million cells of data. I would either filter or aggregate the external records to something more manageable.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

My sources usually have millions of rows. I can query to return the data to a sheet that I need to pivot.

 

[dhookom]

I'm currently working on a pivot that pulls from 4.9 million records but my actual results in the data sheet are typically several hundred to several thousand.

Duane
Hook'D on Access
MS Access MVP