Passing SQL Parameters for Pivot Reports

J

J. Joshi

I need to create a Pivot table based report in MS Excel
2000. Currently, the report is generated using a SQL Store
proc (Data/Pivot Table & Pivot Chart/External Data
Source . . ). Does anybody know how can I make an
interface that asks the user for paramaters that can be
passed into this SQL store proc to generate specific data-
sets. Currently, I show everything by running the store
proc without any parameters. I want to avoid doing that.
The ideal should be:

Whenever the user opens the Excel file . . .

The Pivot report refreshes based on the Username (which I
can create a user table and pass the parameter) whereby on
opening the workbook, the user is promted for his/her name
(or a Department Name) which is passed as a parameter into
the SQL store proc.

Also, is there anyway to lock this SQL generated Pivot
Report whereby the user cannot access the SQL proc using
MS Query? If I use SHEET/PROTECT, the report does not
refresh, which is obviosuly a problem.

I know that there would be some level of Windows API based
VBA code involved. If someone has a solution to this or
can point me to a code/interface that may or maynot be a
free-ware, it would make life more livable for me.

Thank you so much!

Joshi
 
Top