Pivot table 'views'

[Chartist]

I have a very complex data source (80 columns, thousands of rows) and need different types of Pivot tables for analysis. I have created around 20 Pivot tables in different sheets based on the same data. What I wanted to know was, is there something like a saved 'view' which I can select in the Pivot table to generate different pivots in the same sheet (with different settings of rows, columns, etc but based on the same data)? Currently, to view the other Pivot, I have to go over to the other sheet - or customise the current pivot which doesnt make sense.

Thanks

 

[jeffreyweir]

Re post 29...I take it I'm doing all this from Excel? I'm using excel 2007, which seems to have different file path. I'll have a look on the net to see what the comparable path is, assuming I'm not barking up the wrong tree.

 

[jeffreyweir]

Hi again Fazza. I just ran your code again, and checked it against my data table result, and noticed that the amounts returned are completely different. I'm not sure what the numbers that your code returns relates to. It doesn't appear to be a sum of relevent data by time and date (your formula returns figures that are about four to eight times less than the totals I expect).

I searched through the database for some of the numbers returned, and they weren't found - so it's not returning specific numbers from the data set. I also manually checked the figures that my datatable retruns against the database, and my datatable is correct (but very slow!)

To try to see what was happenning, I replaced all the kW data with ones. Your code returned the number four for all columns and rows down to the intersection of 9/7/07 and time 0830, then returned the number three after that. Very strange, given I have 11 different sites...the code should have returned 11 in each cell.

Any idea what has gone awry?

Regards

jeff

 

[Fazza]

Geez, Jeff, and I've already had a beer last night to celebrate...

No obvious things come to mind. It is smart to check with an entry of 1 in every cell, BTW.

Some basics, all the data must be in a contiguous block. So no blank rows. (Or if it is, we'll change from using the worksheet reference to using a defined name to explicitly defined the data range. Currently the worksheet reference has the Jet database engine identify the data range.)

'Mixed data types' can also be a real problem, and is the most likely explanation here. So, be sure that all site names are text entries, or all are numeric entries - NOT a mixture of names like site 1, site 3, whatever, 5, anotherie. Be sure that all date fields have numeric entries. Be sure that all 'reading time' entries are numeric or all are text. You've loaded all numeric values of 1 as test data.

Not relevant today, but the SQL I posted yesterday included using an alias of A that was unncessary, just leftover from my development work. So instead of,
FROM [data$] A
it need only be FROM [data$]

regards, F

 

[jeffreyweir]

Hmmm...something very strange going on. Have made sure there are no blank rows. Have changed all data to general. still no joy. Have then changed it all to numbers. still no joy. I took out all the unneeded columns, cut and pasted special into another data sheet, saved, then ran macro, but still no joy.

I even changed all the dates to "1" just to see what would happen. I got the value "1285" under each time heading until 830, at which point I got "1284" from then on. I have no idea why i got "1285" let alone why it changes. It doesnt seem to correspond with any of the multiples I'm working with (i.e. 11 sites, 365 days, 48 time periods)

When I changed all 192720 rows to "1" for all variables (i.e. time, kw, date) the code returned the value 61648. No Idea why. Maybe that is the limit at which this code maxed out? I'll make up a really long set, and see if I get the same value.

 

[Fazza]

Jeff,

A thought. It could be the use of 'time' as a field name. The code might want to load in the actual time.

You could try renaming the field timenew in the source data and changing the code to suit.

OR, revise the VBA. Use an alias for the table name and explicitly use it in the field reference. So it would become

TRANSFORM SUM(kW)
SELECT `reading date`
FROM [data$] A
GROUP BY `reading date`
PIVOT A.time

If that doesn't help, please post some samples of the time field entries.

Regards, Fazza

 

[jeffreyweir]

I extended the dataset to the max number of rows (1048576), with a "1" in each of them, and this time the macro returned 65535. It should have returned 1048575, i believe?

Also tried changing the time and date field names to something else. Still no joy.

Will try your suggestions a bit later. Gotta get home and rescue the wife from the kids.

Cheers Fazza.

ps just picked up "Excel 2007 advanced report development" which looks to cover off quite a bit of this type of stuff. Just have to find the time to read the **** thing...

 

[jeffreyweir]

Ahh...I see that prior to excel 2007 that 65535 was the max amount of rows. So this is maxing out because the code thinks I'm using an earlier excel version, me thinks.

 

[Fazza]

OK. I've not used Excel 2007. Maybe the ADO connection I specified in the code,

  strConn = Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _
      ActiveWorkbook.FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

Needs to be updated for Excel 2007. Prior to Excel 2007 the row limit is 65,536. So a header and 65,535 records.

I don't know what it should be for Excel 2007. I'll have a google.

F

 

[Fazza]

You beat me on the keyboard. I had to help a guy at work with a question and was a little slow!

 

[Fazza]

This looks like it will help http://www.connectionstrings.com/