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.

 

[Legacy 122759]

Bump!

Still no luck with this.

 

[andrepws]

I´m with the same problem and still with no luck to solve that..........

 

[Legacy 122759]

The only workaround I've found is to base a pivot table on a table that is populated by the data connection. Not ideal as it requires additional code to refresh the pivot table once the table has refreshed and doesn't appear to be as stable as I would like (nor as elegant).

I've also discovered an interesting quirk. Although I'm using Office 2007, the report I was working on was saved to .xls format. If I recreate the spreadsheet but save to .xlsx format and reopen it, the spreadsheet corrupts when it tries to refresh itself.

I've already resigned myself to having to use some VB to do this. It's driving me up the wall.

http://www.microsoft.com/communitie...c756e6-2c2b-473a-a4ab-fd2c4ba3e13f&sloc=en-us

 

[andrepws]

This little bug of xls to xlsx I got the same. I´m using Office 2010 too and it has the same problem. I´m thinking to change the procedure...
I posted this problem on Microsoft´s Excel 2010 blog too... any news, I´ll tell you...

 

[Palendrone]

In the same boat here myself except that I have probably spent 30 hours trying to find out why my .xlsx file kept crashing and corrupting on saving the edited file - this was happening as it was trying to save the file with missing links in the parameters cell link box, a swine to figure out when you have been experiencing the file crashing becuase of a complex graph!!! Arrrrghhhh is it my imaginiation or was 2003 much more stable?

Hope they fix this soon as it's doing my head in too!!!!

 

[Palendrone]

Getting a little bored after only two weeks of opening a file and having to manually re-enter the parameter cell, is there any VBA that I can use for this on workbook open?

I did try "insucessfully" recording a macro which makes me think its not possible but any help well received :0)

Michael

 

[Legacy 122759]

I'm glad it's not just me.

Is there any way of raising issues like this with Microsoft? It feels like it's a bug rather than it being me trying to make something work that shouldn't work. It's just a bug that hardly anyone ever encounters.

I've said this before but if it worked it would be such an elegant and simple solution to so many problems. Stored procedures are so powerful, being able to feed them parameters on the fly felt like it was too good to be true, and it was!

 

[Palendrone]

So Office 2010 a port of the previous version with all the same bugs... Another worthwhile upgrade!

I'm gonna pester tech support soon over this as I am banging my head against a wall daily updating a report I have to manually enter parameters.

 

[Miq1963]

Hi, all,

I have this problem too...

someone found a solution?

Thks