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

 

[Fazza]

PS.

I've posted in the past on generating cross tab query results. A search of old forum posts by me containing TRANSFORM and SQL should find some. F

 

[jeffreyweir]

Thanks Fazza. Will search, and will post some headings and perhaps a screenshot of what I did with the data table summary...just as soon as I learn how to. Seen some instructions on how to do so on some posts, so will read and post soon as I can.

 

[jeffreyweir]

I should emphasise that my database is all within excel. A quick search of 'query table' seems to hint that you may think my data is external? I'm using the term 'database' based on DSUM functions and the like in excel.

 

[Fazza]

I'm thinking entirely within Excel too.

 

[Fazza]

A screen shot may not be necessary.

What is the name of the data worksheet and the salient (actual) headings - time period, date, consumption, etc?

 

[Fazza]

I'm thinking of something like this. With "Sheet1" source data having field headers "date", "time period" and "consumption". Filtering by sites or times or whatever is just a single extra line in the SQL and neglible difference in execution time.

OK?

Sub ADO_to_newWbk()

  Const strSOURCE_DATA_WKS_NAME As String = "Sheet1"

  Dim i As Long
  Dim strConn As String
  Dim strSQL As String
  Dim objRS As Object
  Dim wbkNew As Workbook

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

  strSQL = Join$(Array( _
      "TRANSFORM SUM(A.consumption)", _
      "SELECT A.date", _
      "FROM [" & strSOURCE_DATA_WKS_NAME & "$] A", _
      "GROUP BY A.date", _
      "PIVOT A.`time period`"), vbCr)

  Set wbkNew = Workbooks.Add(template:=xlWBATWorksheet)

  Set objRS = CreateObject("ADODB.Recordset")
  With objRS
    .Open strSQL, strConn
    wbkNew.Worksheets(1).Cells(2, 1).CopyFromRecordset objRS

    For i = 0 To .fields.Count - 1
      wbkNew.Worksheets(1).Cells(1, i + 1).Value = .fields(i).Name
    Next i

    .Close
  End With

  Set objRS = Nothing
  Set wbkNew = Nothing

End Sub

 

[jeffreyweir]

Database Columns: site, reading date, time, kW. Also have one called 'include', but I only use this to help filter out any sites that I don't want in my final results. This has a vlookup that checks a similar 'include' column on my master list of sites. This probably won't be needed with your solution.

Database worksheet name: data

From memory (as the file is not with me at the moment) I used the Data Table command, which changed the 'reading date' and 'time' cells of my filter criteria. I'm sure you know how data tables work better than me, but will cover off my understanding of what I did anyway. My data table had all 365 days of the year going down, and all 48 trading periods (i.e. half hourly blocks in a day) going across the top. The data table was populated by a DSUM function that used the query described earlier to filter the database and sum whatever was left. Obviously the 'data table' function populated this formula into the other cells of the data table as an array formula.

THen across the top of this data table I had 2 subtotal functions: one for max, one for average. These are the data series for the graph (although I used some other formulas to take the results from the data table and break down further into business day/non business day using a formula I wrote).

So you can see why it is so slow! Works beautifully though.

Can't wait to see what you come up with. I bet it's much simpler!

 

[Fazza]

Jeffrey,

The above is just a demonstration - with a new workbook for the summary though it need not be. I tested with 10,000 rows of data and it takes almost no time. Cheers, Fazza

 

[jeffreyweir]

But Fazza...I'm charging my client by the hour, and I'm as honest as I am inefficient! So you just cut my revenue in half!

I'll try this out. I'm fairly new to VBA, so may take some fiddling and cursing.

Cheers...I can't wait to see what this does.

 

[Fazza]

Jeffrey,

I was getting ahead of you.

I don't use data tables so your comments are helpful.

I generate a cross tab result that is what you're after. For the first step anyway. I haven't considered the MAX & AVERAGE just yet.

This seems to work very quickly. I changed it to match your sheet name & headings.

Can you test this, or want some instructions? Regards, F

Sub ADO_to_newWbk()

  Dim i As Long
  Dim strConn As String
  Dim strSQL As String
  Dim objRS As Object
  Dim wbkNew As Workbook

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

  strSQL = Join$(Array( _
      "TRANSFORM SUM(kW)", _
      "SELECT `reading date`", _
      "FROM [data$] A", _
      "GROUP BY `reading date`", _
      "PIVOT time"), vbCr)

  Set wbkNew = Workbooks.Add(template:=xlWBATWorksheet)

  Set objRS = CreateObject("ADODB.Recordset")
  With objRS
    .Open strSQL, strConn
    wbkNew.Worksheets(1).Cells(2, 1).CopyFromRecordset objRS

    For i = 0 To .fields.Count - 1
      wbkNew.Worksheets(1).Cells(1, i + 1).Value = .fields(i).Name
    Next i

    .Close
  End With

  Set objRS = Nothing
  Set wbkNew = Nothing

End Sub