Pivot Table: Problem with data connection parameter

[Legacy 122759]

Hello all,

I'm having an issue with a data connection in Excel 2007. I've not been able to find a solution anywhere online so I'd be tres grateful if anyone could shed some light on this. SQL Server is involved but I believe the problem is with Excel so apologies if people think I'm posting this in the wrong place.

I'm attempting to set up a very simple reporting front end for a SQL Server 2008 database. I'll try my best to explain the setup.

I have a view within a SQL Server database that is filtered by a stored procedure. The stored procedure requires two parameters to be fed into it. I've created a spreadsheet that contains two cells, both with validation lists so that the user can select preset values to use as parameters for the stored procedure. SQL Server then spits out data as required into both a table and a pivot table. There is a little bit of VBA attached to the worksheet containing the Parameter drop downs that refreshes the entire workbook when it detects a change. This is to refresh the pivot table with the 'new' data from SQL server.

The table is working fine. The pivot table however, isn't. The pivot table works fine until I close the workbook and reopen it. When I do this, Excel appears to have lost the cell reference for the cells containing the required parameters and requests me to supply them manually. As I mentioned earlier, the table does not lose the references, it works fine. This is why I believe the problem is in Excel, possibly something to do with how pivot tables deal with data connections maybe?

I do not believe the VBA is contributing any problems. This is because I have macro security set to medium. Excel requests me to supply the location of parameter values even if I choose not to allow code to run.

The command text I'm using in the connection properties (for both the table and the pivot table) is as follows:

exec usp_Plan_Report_Filter ?,?

Then in the parameters section of Connection properties I have Parameter 1 and Parameter 2 set as "Get the value from the following cell" and "Refresh automatically when cell value changes" is checked.

Just to reiterate, everything works fine until I close and then reopen the workbook.

Any help would be much appreciated.

 

[zonian]

Hi, all,

I have this problem too...

someone found a solution?

Thks

There is still no solution...We are still waiting.

Here is an MS link. You can add your two cents and perhaps, we will get some movement.

 

[zonian]

There is still no solution...We are still waiting.

Here is an MS link. You can add your two cents and perhaps, we will get some movement.

Still no movement on this feature. Any one made progress?