CODEWITHSUNDEEP
sqlexcelstored-procedures
frank
frank

Reputation: 67

Creating an excel working with SQL stored procedure

i am very new to SQl and i have a complex report for my job which i am trying to create in excel using a SQL stored procedure.the report itself will be in an excel workbook and consists of 3 monthly tabs for the entire year, the first tab should show detailed totals (total number of absences, total number of injuries, etc), the second will show a detail of each absent employee without a valid reason and the third will show a detail of each employee absent with a valid reason and the very last tab of the workbook will have all the raw data. Could someone please explain to me how that is possible without using VBA? i know i can extract data from SQL into excel without using VBA but the main part i'm having a hard time with is separating the data into each tab and creating the summary totals for each month.

thank you in advance wise once.

Upvotes: 2

Views: 2745

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You need to learn about data sources in Excel and how to set up a connection to the database. Here is an example of information on the subject: http://office.microsoft.com/en-us/excel-help/create-edit-and-manage-connections-to-external-data-HA010167227.aspx.

Once you have data connections, you can then have queries in your spreadsheet automatically pull data from the database, without using VBA. Excel supports refresh functionality, which will reload all the data, at the user's request.

When doing this, I suggest the following:

  1. Have only one query per tab.
  2. Have the query pull data from a view, rather than writing a complicated query in Excel.
  3. Do not mix read-only reporting and database inserts/updates in the same spreadsheet.
  4. Use database permissioning to limit access to data, if appropriate.

Upvotes: 1

Darthtater
Darthtater

Reputation: 111

What I did was use the stored procedure as a source to a view and then used the view as the data source to Excel. I assume that since this a report, you only need the access to be read-only. Problem is that this could lead to bandit database issues.

Upvotes: 1

Related Questions