Creating an excel working with SQL stored procedure

Question

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.

Gordon Linoff · Accepted Answer · 2012-07-22 16:05:02Z

1

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:

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

answered Jul 22, 2012 at 16:05

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Darthtater · Accepted Answer · 2012-07-22 15:17:11Z

1

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.

answered Jul 22, 2012 at 15:17

Darthtater

1112 bronze badges

Collectives™ on Stack Overflow

Creating an excel working with SQL stored procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related