asked
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][1] [1]: /upfiles/ExcelConnection.png
Hey Pavel, nice answer. I never knew this was possible - I need to take some time to refresh my Excel knowledge.
Yeh, it's also possible using the Microsoft Query in earlier versions of MS Excel, when connecting, you choose cancel when selecting a table, it asks whether you can to use Microsoft Query for connecting, you choose yes, and the put SQL Commad with Execute stored proc.
Hey Pavel. Thanks for the advice. But it solves only half of my problem. I have no issues till my sp is without parameters. Is there any work around say in case of SP where i have parameter as start date and end date. I don't want the users to go each time to connection property and change as they will not have rights for that. Is there any way by which we can prompt the parameters.
You can write a Macro SUB in excel and update the connection eg. by values stored in some cell etc.. Depends where you will have stored the parameters, or ask user to input the parameters using InputBox, Create a form for providing parameters etc. There is a lot of possibilities in Excel. You can even automatically refresh the data etc. Sub updateConnection() Dim con As WorkbookConnection Dim dateFrom As Date Dim ws As Worksheet Set con = ThisWorkbook.Connections("TestConnection") 'Read date from a cell Set ws = ThisWorkbook.Sheets("Sheet1") dateFrom = ws.Range("A1").Value 'Read Date from InputBox dateFrom = InputBox("Please provide starting date", "Query Parameter", Format(DateTime.Date, "yyyy\/mm\/dd")) 'Update the connection CommandText con.OLEDBConnection.CommandText = "EXEC dbo.usp_TestProc '" & Format(dateFrom, "yyyy\/mm\/dd") & "'" End Sub
