Automatically pivot MS Query data

[Ofina]

I'm not sure if this is the correct forum for this question, so my apologies if it is not.

I have a lot of data coming into Excel via MS Query on an SQL database. Rather than have my users take that data and have to pivot it this way and that to see it how they want to, is there some way I can automatically pivot it?

 

[SkipVought]

hi,

This can be done with native Excel features.

Start with (2007) Insert > Tables > PivotTable -- Use and EXTERNAL DATA SOURCE.

You must have an ODBC Driver configured for your SQL Database.

Chances are, you will need no VBA at all.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ofina]

They'll still have to refresh the pivot when they open it, though right?

 

[SkipVought]

That can be automatic with a setting in Data Range Properties.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ofina]

Okay, this is going well. Thanks so much.

Now, how can I keep the file size from being so bloody large?

 

[SkipVought]

Are you performing any filtering in the Where clause or aggregating any of your data in the query, prior to pivot, in order to limit the amount of data?

I frankly never pivot on external data. I bring data into my workbook and then pivot. I find that easier to accomplish and I can then join data from many different sources/tables, that I could not do in MS Query alone (like data from Oracle, DB2, Access, Excel)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Ofina]

I don't think I was doing much filtering. Admittedly the file size is much smaller than it was when I was bringing in the whole data and THEN pivoting.

But, I'm not sure what stage you're asking about where I would do the filtering. I'm bringing the data from my SQL view directly into a pivot. Where would I have the opportunity to filter more?

 

[SkipVought]

Admittedly the file size is much smaller than it was when I was bringing in the whole data

then you ARE filtering in your query.

I can't really provide help on pivoting directly. As I previously stated, I import my resultset via MS Query and then, in a separate process, employ a PivotTable. It is a ONE TIME EVENT to Add a query and ADD a PivotTable. Subsequent refreshes are simple refresh of both objects. I'd recommend doing it all in the Workbook_Open event...

Private Sub Workbook_Open()
   YourQuerySheetObject.ListObjects(1).QueryTable.Refresh false
   YourPivotTableSheetObject.PivotTables(1).PivotCache.Refresh
End Sub
assuming that you have ONLY ONE query/pivot table on each sheet

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...and I'll tell you another reason I very much prefer doing it this way. I can do spreadsheet function aggregations DIRECTLY on the Source Data (SUM, COUNT, CONDITIONALS) which, for me is much simpler and more versatile than using PT Formulas.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}