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?

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