Parameterizing a query

G

Garrett Fitzgerald

Using Excel 2007.

I have a Workbook Connection where the command is of type SQL, and the data
brought back is within a particular range. Works fine, looks great in the
crosstab.

Now, I'd like to fix it so that I can specify the dates above the crosstab
and pass them in to the command. However, nothing I try is letting me do
this: I put question marks in the SQL, but it warned me about missing
parameters. Do I need to do the whole thing in VBScript? Or is there
something else I should try first? The fewer macros, the fewer security
warnings. :-(

Thanks!
 
J

Jim Thomlinson

I assume you are creating a pivot table directly from the data source and not
returning the data to a worksheet? Assuming that to be the case you are going
to have a bit of an issue. Pivot Tables created directly from a query can not
have parameters (there is a Microsoft article about it somewhere). To do what
you want to do you need to bring the data back to a worksheet and then create
the pivot table off of the worksheet. Doing that you can have a parametric
query. You can have a very simple macro that updates the query and then
updates the pivot table directly.
 
G

Garrett Fitzgerald

Jim Thomlinson said:
Pivot Tables created directly from a query can not
have parameters (there is a Microsoft article about it somewhere).
Click to expand...

Of course, because nobody would ever want to do monthly Pivot Table reports,
after all...
To do what
you want to do you need to bring the data back to a worksheet and then create
the pivot table off of the worksheet. Doing that you can have a parametric
query. You can have a very simple macro that updates the query and then
updates the pivot table directly.
Click to expand...

ITID. :) More work than I think I should have to go through, but I can see
how to get there.
 
J

Jim Thomlinson

Preaching to the chior on that one. The only thing that you can do otherwise
is if you are on a first name basis with the DBA you might get him to set you
up with a table where you can write your parameters. Write your query to read
the parameter values from the table and you get around the issue. But then
you need an interface to write to the parameters table...
 
Top