Passing Parameters to a Pivot Table
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? |
Passing Parameters to a Pivot Table
On Mar 11, 6:37*pm, Keith 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:=xlExter nal) 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 |
All times are GMT +1. The time now is 09:54 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com