January 20, 2006 at 12:49 pm
Hi,
Just want to share a tip:
I use Excel Pivot Tables for reporting from SQL Server. In SQL Server database I create views to do all the processing and then from the Excel spreadsheet access this view using Pivot Tables feature. The only thing I could not find out the where the query definition was stored. I mean, the query that Excel is using to select from my view. Because after several month and having many tables and views in the database you are not likely to remember what did you use to create a pivot. I tested that the query was stored in the workbook itself, not on the computer, so the query travels from computer to computer with the Excel document, so the report could be easily refreshed if you have an appropriate Data Source on that computer
After some research I found a solution: create a macro with the following text and run it. You could modify it to direct the output into something else like the text file. You also can retireve the Data Source and the Database name if you use pc.Connection instead of pc.CommandText. There is a warning: Connection string may contain your password that was used to access the data source.
Sub WhereIsMyQuery()
For Each pc In ActiveWorkbook.PivotCaches
MsgBox (pc.CommandText)
Next
End Sub
Regards,Yelena Varsha
January 23, 2006 at 8:00 am
This was removed by the editor as SPAM
January 23, 2006 at 12:47 pm
I do something similar, to reduce maintenance.
I create a stored proc on the server that does all the calculations, then just "exec StredProc" in the pivot table. That way, many users can have their own pivot tables, but share the same SP. Any changes to the SP need only be done once on the Server.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply