Passing Parameters to a Pivot Table
in [Programming]
|
Prev: colorscales, icon sets, and databars referring to remote cells
Next: excel 2010 question regarding determining the last row
From: Keith on 11 Mar 2010 18:37 Hi, We use parameters and Excel formulas (Start Date=Now()+1 year, End Date=Now(), etc.) to pull sales detail from an IBM system and it works fine. I want to use a piivot table to summarize 14,000+ lines of details into about 800 parts. Works great but I need to change the date range daily to capture 1 year of history. This is one of 12 different queries in the same file we want to do this with. Is there a way to use parameters in a Pivot Table like we are now doing with an MS Query?
From: Jim P on 12 Mar 2010 09:45
On Mar 11, 6:37 pm, Keith <Ke...(a)discussions.microsoft.com> wrote: > Hi, > We use parameters and Excel formulas (Start Date=Now()+1 year, End > Date=Now(), etc.) to pull sales detail from an IBM system and it works fine. > > I want to use a piivot table to summarize 14,000+ lines of details into > about 800 parts. Works great but I need to change the date range daily to > capture 1 year of history. > > This is one of 12 different queries in the same file we want to do this > with. Is there a way to use parameters in a Pivot Table like we are now > doing with an MS Query? Hi Keith The short answer is you can't use parameters when MS query returns the record set to a pivot table. I had some assistance from Debra Dagleish on this code. See www.contextures.com The trick is you start with an existing pivot table and then replace it with a new cache. I have a range named QueryRows which contains the SQL used, for example Select item, quantity, retail from table where date between 20100101 and 2010228 You can enter the variable parameters into a cell and then use contcatenation to vary the lines in your SQL. For example =" where date between "&fromdate&" and "&todate One other thing is you may have to use the "zoned" funciton on packed fields. For example, Select zoned(itemtable.number) as item, zoned(inventory.quantity) as On_hand There's a command button on the worksheet containing the pivot table wich has the following code. Code: Private Sub Import_Click() On Error GoTo Err_cmdImport_Click Dim AS400Conn As New ADODB.Connection Dim objCmd As New ADODB.Command Dim rs As New ADODB.Recordset Dim ObjPivotCache As PivotCache Dim ptOld As PivotTable Dim QueryString As String Dim c Dim pt As PivotTable Dim strCmd As String Dim ws As Worksheet Dim wsTemp As Worksheet Set ws = ActiveSheet Set ptOld = ws.Cells(5, 1).PivotTable Application.Calculation = xlCalculationManual Set AS400Conn = New ADODB.Connection AS400Conn.Open "Provider=IBMDA400;Data Source=000.000.000.000 (your tcpip address for the 400", "", "" For Each c In Worksheets("SQL").Range("QueryRows").Cells QueryString = QueryString + c.Value Next 'Open the recordset Set rs = AS400Conn.Execute(QueryString, , adCmdText) 'Populate the pivot table cache from the record set Set ObjPivotCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal) Set ObjPivotCache.Recordset = rs 'create a temporary sheet and pivot table to use the new cache Set wsTemp = Worksheets.Add Set pt = ObjPivotCache.CreatePivotTable _ (TableDestination:=wsTemp.Range("A3"), TableName:="Temp") 'change old pivot table to use the new cache ptOld.CacheIndex = pt.CacheIndex 'delete the temporary sheet and pivot table Application.DisplayAlerts = False On Error Resume Next wsTemp.Delete Application.DisplayAlerts = True Exit_cmdImport_Click: Set ObjPivotCache = Nothing Set rs = Nothing Set objCmd = Nothing Set AS400Conn = Nothing 'Toggle off the display of the pivot table field list ActiveWorkbook.ShowPivotTableFieldList = False 'Switch calculation back to automatic Application.Calculation = xlCalculationAutomatic Range("A1").Select Exit Sub Err_cmdImport_Click: MsgBox Err.Description Resume Exit_cmdImport_Click End Sub I know it looks pretty complicated. It works welll when you need to retrieve more than the 65,000 rows that Excel 2003 can accommodate in a sheet. Jim P |