How to retrieve data from SQL 2005 using Excel Pivot while using SPs
I work for a firm which require some reports in excel. The database we use here is SQL2005. I know i can use SSRS but due to some technical issue (some users don't have access to IE. They have only access to MS Office) I can't use SSRS. I do reporting in both SSRS and Excel (Pivot report). My problem is this . I have created some Stored Procedures and I want this to be used for Pivot tables. This is fine till I work with a query. But if its a SP I can'nt do much on this Can somebody tell me how can I call a sp using excel pivot. Please help or at least let me know a forum where i can post the same
You wont be able to reference a stored procedure from Excel, it needs to reference a table or a view. My suggestion on this problem would be to use the stored procedure in a scheduled job to put the data into a table for the Excel reporting. So long as the users know when/how often the data is refreshed then they can open the spreadsheet at the appropriate time and work with data that they know is current.
You can reference a stored procedure from Excel and select data by executing stored procedure. However you cannot select a stored procedure when creating the connection in Excel. To workaround this, create a connection to some table. After the connection is created, choose properties of the connection, switch to the Definition Tab, and there select Command Type = SQL and put your query like "EXEC usp_YourStoredProc". Confirm the dialog and you have an Excel connection to a stored procedure :-) ![alt text] : /upfiles/ExcelConnection.png