Good Tip on Reporting From SQL to Excel

  • 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

  • This was removed by the editor as SPAM

  • 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