Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Reporting with Microsoft Excel


By:   |   Last Updated: 2008-02-21   |   Comments (2)   |   Related Tips: More > Microsoft Excel Integration

Problem

Our business users are very savvy with Microsoft Excel.  The issue we struggle with is getting the SQL Server data to them in a streamlined manner.  We are familiar with some of your earlier tips related to integrating Microsoft Excel and SQL Server.  We have gone down the paths and they have been helpful for a number of standardized reports.  Now that the users can see the data, we are getting a number of requests to modify reports on a daily basis.  Unfortunately, we do not have the staff to support all of the reporting needs.  Do you have any suggestions to conduct SQL Server reporting directly with Microsoft Excel from a user perspective?

Solution

That is a great question.  A number of tips have been written on MSSQLTips.com (Export data from SQL Server to Excel, Different Options for Importing Data into SQL Server, Insert data from Excel to SQL Server 2005 by using copy and paste commands, etc.) related to SQL Server and Microsoft Excel integration.  More options are available in the marketplace natively with Microsoft Office, SQL Server, Reporting Services and third party tools.  In this tip let's work through setting up a PivotTable in Microsoft Excel 2003 from data in the SQL Server 2005 AdventureWorks database.  In a nutshell, we will do the following:

  • Setup connection between Microsoft Excel and SQL Server
  • Define the data, report definition and layout
  • Save the report for re-execution or future modification

How to setup Microsoft Excel reporting with SQL Server

Below outlines the detailed steps to create a Microsoft Excel PivotTable accessing data in SQL Server:

Microsoft Excel Reporting from a SQL Server Database

Step 1 - To start the PivotTable and PivotChart Wizard, open Excel and navigate to the 'Data' menu and select the 'PivotTable and PivotChart Report...' option.

To interact with SQL Server, select the 'External data source' radio button.

Press the 'Next >' button to continue the process.

excel pivot table wizard step 1

Step 2 - Press the 'Get Data...' option to retrieve the data.

excel pivot table wizard step 2

Step 3 - If Microsoft Query has not been installed, press the 'Yes' button to begin the installation.

excel install feature

Step 4 - Validate Microsoft Query installs properly.

excel install feature

Step 5 - Select the '<New Data Source>' option to create a new data source.

excel choose data source

Step 6 - Define the terms of the data source.

excel create data source

Step 7 - Select the SQL Server and authentication mode (Windows or SQL Server).  For an expanded set of options (Database, Language, etc.), click the 'Options >>' button.

sql server login

Step 8 - Below outlines the completed Create New Data Source interface.

create data source

Step 9 - Select the columns from the applicable table(s) or view(s) to outline what data is included in the report.

excel query wizard

Step 10 - If you press the 'Options...' button from the interface above will provide the types of objects to include in the report.

table options

Step 11 - If necessary, data can be filtered on a per column basis.

query wizard filter

Step 12 - If necessary, the data can be sorted by a number of columns.

query wizard sort

Step 13 - Select the data interface.

query wizard finish

Step 14 - From the interface above, if you select the 'Save Query...' button the interface below will appear to complete the process.

save query as

Step 15 - As the interfaces above are being completed, Microsoft Query will be executed and can be reviewed to see the query, WHERE clause criteria and sample data set.

query view

Step 16 - Once the data has been retrieved, press the 'Next >' button.

pivot table wizard

Step 17 - Choose the radio button for the appropriate worksheet and once the 'Layout...' and 'Options...' buttons have been configured, select the 'Finish' button to generate the PivotTable.

pivot table wizard

Step 18 - If you select the 'Layout...' button from the interface above, you will be able to drag and drop the column to the page, row, column or data portions of the interface.

pivot table wizard layout

Step 19 - If you select the 'Options...' button from the interface (Step 18) above you will have the following options.

pivot table options

Step 20 - Final PivotTable with the PivotTable Field List to modify the report.

query output
Next Steps
  • The next time you are faced with a recurring report that is not completely standardized, consider building a PivotTable with the steps outlined in this tip.  By following these steps, you enable your users to build their own reports, modify reports as needed and execute the reports on demand without any development effort.
  • One idea may be to setup custom views or expose needed columns from your existing tables in order for your users to complete their reporting needs.
  • To take this a step further, be sure to have an non-OLTP environment available to the users for reporting purposes.
  • If you do have some power users in your environment, setting up an infrastructure between SQL Server and Excel to support custom reporting, may yield numerous business and IT benefits.
  • Check out these related tips:


Last Updated: 2008-02-21


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an MSSQLTips.com co-founder and Edgewood Solutions SQL Server Consultant.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, October 14, 2014 - 6:43:52 AM - Bulbul Back To Top

Hi,

 

I want to generate the report from SQL server in the below format..Can you please help me out ?

 

I hope you get what i want to retreive the value

 

Date Job_A Job_B Job_C Job_D
  Start End  Diffrence Start End  Diffrence Start End  Diffrence Start End 
2/1/2014                      
2/2/2014                      

Thursday, September 04, 2008 - 3:27:00 AM - JimR Back To Top

One drawback with an Excel Pivot Table from an external source, is that (unlike a query) the definition of the underlying query does not seem to be editable.  So when the external view definition changes (e.g. adding the extra columns that you only realize you need after trying the first pivot table) it seems necessary to discard and redefine the pivot table.  Any workarounds?


Learn more about SQL Server tools