<

Microsoft Excel & SQL Server:  Self service BI to give users the data they want

Published on
24,829 Points
5,629 Views
12 Endorsements
Last Modified:
Approved
Jim Horn
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
How to build an Excel spreadsheet that allows users to enter select values via populated combo boxes and hit a magic ‘Go’ button that retrieves only that data from SQL Server based on those selections, using nothing more than standard Microsoft Excel functionality and an ODBC Connection.  
This article targets this common scenario for anyone that can execute SQL Server T-SQL: 
Hey Bob, run me my favorite report for April.   Hey Bob, run me my favorite report for June.   Hey Bob, run me my favorite report for July.   Hey Bob, you know that thing you ran for me last week?  Run it again for last month.   Hey Bob, can I get a report that shows the number of aardvarks in every zoo?  Hey Bob, can I get a report that shows the number of flights out of St. Louis, and everything about them?

The finished product, which also is an Excel download at the end of this article, is here:
The Magic 'Go' button!This is also a walk-through of Excel named ranges, data validation, data connections, Excel VBA, and SQL Server Stored Procedures. 

The link to AdventureWorksDW2012 database is here, and the completed Excel doc used in this article is here. 


What is needed for you to create this solution
  • Data in SQL Server, although this solution can work with any database with a saved Stored Procedure.  
  • This solution uses SQL Server 2012 and the AdventureWorksDW2012 database, so if you don't currently have it then click here to download and install.  
  • Microsoft Excel.  This solution has been tested in Excel 2010 and 2013.
  • The ability to create a SQL Server ODBC connection, which comes standard in Microsoft Office.
  • SQL Server Management Studio (SSMS) to create the Stored Procedure that serves as the report's data source.  Users will not need SSMS to use these reports.
  • Basic to Intermediate skills in manipulating T-SQL to create Stored Procedures.

What is needed for users to use this solution
  • Microsoft Excel, and the free Power Query download and install. This solution has been tested in Excel 2010 and 2013.
  • SQL Server execute priveleges on the Stored Procedure we create.

What is NOT needed to pull this off
  • A report server or any reporting application other than Excel.​
  • Any of the extra fee Microsoft Power BI tools
  • Installing SQL Server on client desktops. 

Out of scope / Maybe I'll go here in a future article
  • How to create a SQL Server Stored Procedure
  • Optimizing SQL for fast performance
  • Connections other than SQL Server, such as Oracle, Informix, DB2.​
  • Secured connection issues
  • Reporting Environment Strategies​
  • Logging:  How to stalk your users for fun and self-promotion

Here's what we're going to do...
  1. Create a SQL Server Stored Procedure
  2. Setting up Excel 
  3. Excel connects and executes the SQL Server Stored Procedure
  4. Excel VBA to make the Stored Procedure execute with user-entered values

So let's go...


1  Create a SQL Server Stored Procedure

Below is a pretty basic query I coded to return Internet Sales in the AdventureWorks 2012 Data Warehouse.   It started with just the SELECT statement, and then I added the two parameters whose values will be passed from Excel.  

  1. @dt  - datetime, which is the first day of any given month, e.g. 2013-07-01 will be interpreted as July 2013
  2. @CategoryName, which is the table DimProductCategory, column EnglishProductCategoryName.

Notice the use of aliases with spaces that force the use of [Square Brackets], which is not normally a best practice, but this way the column names that will appear in Excel.

Copy-paste the below code into an open SSMS window connected to the server that contains your copy of AdventureWorks2012DW, and execute.
 
USE AdventureWorksDW2012
GO

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'P' AND name = 'xrpt_sales_by_category_by_month')
   DROP PROCEDURE xrpt_sales_by_category_by_month
GO

CREATE PROC xrpt_sales_by_category_by_month (@dt as date, @CategoryName as nvarchar(50)) AS

/*
Source of data for the Excel report 'Sales by Category by Month'.

@dt - The month will always be passed as yyyy-mm-01
@CategoryName - Hard-coded Category name

2014-05-17    Jim Horn    Original.

Personal Rant:  Hey looky here!  Meaningful code comments!!!  
What an easy way to distinguish yourself from your peers!
*/

-- TESTING ONLY
-- Declare @dt datetime = '2005-08-01', @CategoryName nvarchar(50) = 'Bikes'

Declare @dtStart as datetime, @dtEnd as datetime
SET @dtStart = @dt
SET @dtEnd = EOMONTH(@dt)  -- 2008R2 and below use DATEADD(d, -1, DATEADD(M, 1, @dt)) 

SELECT 
   s.OrderDate as [Order Date], SUM(s.SalesAmount) as sales, 
   pc.EnglishProductCategoryName as [Category Name], 
   c.LastName as [Customer Last Name], c.FirstName as [First Name], c.AddressLine1 as [Address],
   cg.[City], cg.StateProvinceName as [State], cg.EnglishCountryRegionName as [Country]
FROM FactInternetSales s
   -- Products
   JOIN dimProduct p ON s.ProductKey = p.ProductKey
   JOIN DimProductSubcategory ps ON p.ProductSubcategoryKey = ps.ProductSubcategoryKey 
   JOIN DimProductCategory pc ON ps.ProductCategoryKey = pc.ProductCategoryKey 
   -- Customers
   JOIN dimCustomer c ON s.CustomerKey = c.CustomerKey 
   JOIN dimGeography cg ON c.GeographyKey = cg.GeographyKey
WHERE s.OrderDate BETWEEN @dtStart and @dtEnd AND 
   (pc.EnglishProductCategoryName = @CategoryName OR @CategoryName IS NULL)
GROUP BY 
   s.OrderDate, 
   pc.EnglishProductCategoryName, 
   c.LastName, c.FirstName, c.AddressLine1,
   cg.City, cg.StateProvinceName, cg.EnglishCountryRegionName 
GO

Open in new window


When executed (the exec line below), it returns this set:


ssms-exec-xrpt-sales-by-category-by-mont



If you don't understand stored procedures very well, we can just skip this part and eventually connect to a table or view. 
 

2  Setting up Excel  Create a new Excel workbook, and then save it with the name 'Sales By Category By Month', and save as type 'Excel Macro-Enabled Workbook'.

Details Tab - Cosmetic Formatting


  • Give the tab a name, say Detail.
  • Give the tab a title in cell A1, say ‘Sales by Category by Month’.


Tables Tab - Create a list of months and product categories

This can also be done as a SQL Server Stored Procedure if you'd prefer, but in this article I'm doing it with Excel and a range just because it's easy. 

  • Create a new tab named Tables.  
  • Create two column headers, ‘Previous 12 months’ in cell B3, and ‘Categories’ in cell D3.
  • In cell B4 below ‘Previous 12 months’, type the below formula, which will return the first day of the current month. This list was provided by expert Rory Archibald in this question.  
=TODAY() - DAY(TODAY()) +1

Open in new window


In cell B5, type the below formula, which will return the previous month
 
=DATE(YEAR(B4), MONTH(B4)-1,1)

Open in new window


Copy-paste that cell all the way down until you have a full year.

Then highlight the ‘Previous 12 months’ range, right-click: Format Cells, Number tab, Custom category, and type mmmm yyyy in the Type combo box.  This will display the months as 'January 2012, February 2012, March 2012, etc.

Highlight again the ‘Previous 12 months range, and in the name combo box type months_list.

Now manually type under the Categories header the values Bikes, Components, Clothing, and Accessories.

Then highlight the 'Categories' values, and in the Names combo box above type 'categories_list'.   The result will be the below image, side-by-side for each list. 

side-by-side-months-list-and-categories-
Details Tab - Data Validating based on the Lists tab
Now we're going to create combo boxes in the Month and Category cells so that users can only choose from the lists we just created.

Type 'Month' in cell A3, and Category in cell A4.

Cell A3 will also need to be formatted as mmmm yyyy.

Create a Thick Box Border around cells B3 and B4.

Click on the Month value cell in B3, then click on the Data ribbon tab, Data Validation button, Data Validation... menu item, which will display the Data Validation dialog.  In the Settings tab, Allow: List, Source: =months_list.  Then in the Name combo box add the name month_value.

Repeat the above steps for the Category value cell in B4, using the Source: =categories_list, and adding the name categories_value.  

The result will be the same as the below image
side-by-side-month-value-and-category-va


3  Excel connects to and executes the SQL Server Stored Procedure


  • Click on the Data tab, From Other Sources button, From SQL Server...
data-from-other-sources-from-sql-server.


  • Enter your server and method of connecting.  In my case the server name is (local), and I'm using Windows Authentication.  Yours may be different. Hit the Next button. connect-to-database-server.jpg


  • In the Select the Database That contains the data your want' combo box, select 'AdventureWorksDW2012'.  Uncheck the 'Connect to a specific table' checkbox.  Hit Next.


  • In the next screen I'm just adding 'xrpt_sales_by_category_by_month' to all text boxes.  Hit Finish. save-data-connection-and-finish.jpg
  • In the 'Select Table' screen it forces you to click on a table or view.  Kind of annoying, but for now select any one and hit Ok.
  • Click on the cell in the upper left of where you want the data to appear, Excel will display that object.   import-data.jpg
  • Now click on the Data ribbon tab, Connections button.  Choose the connection we just created and hit the Properties button.
  • Copy what's in the Connection String, and save it in Notepad.
  • In the Command type: combo box select SQL.
  • In the Command Text combo box type this line of code, which will call the SQL Server Stored Procedure:



exec xrpt_sales_by_category_by_month '2006-01-01', NULL

Open in new window


connection-properties.jpg


  • Then hit Close.

Then populate the Month and Category with values, hit the Go!  button, and the data will appear. 
 

4  Excel VBA to make the Stored Procedure execute with user-entered values

To perform this action you'll have to show the Developer tab:  
File tab > Options > Customize Ribbon > under Main tabs select the Developer check box. 

Click on the Developer tab, Insert..., and the Command button, which is the upper left button in Form Controls
command-button.jpgClick and hold where you want the upper left of the button to be, drag to where you want the lower right to be, and let go of the mouse button.  When prompted, name the button btn_go.
 
Click inside the button until a cursor appears, and rename the default to something like Go!

Click on the Developer tab, Visual Basic button on the far right. 
Sub btn_go_Click()


'Refresh the table
Call fn_execute(Range("month_value").Value, Range("category_value").Value)

ex:
    On Error Resume Next
    Exit Sub

eh:
    MsgBox "An error occured: " & Err.Number & ", " & Err.Description
    Resume ex


End Sub

Public Function fn_execute(dtMonth As Date, sCategoryName As String)

'Execute the SQL Server Stored Proc xrpt_fuel_burn with parameters entered by the user
'@dtMonth - YYYY-MM-01 of the month
'@sCategoryName

'05-22-14  jim.horn  Original

Application.Cursor = xlWait
Application.DisplayAlerts = False

Dim cn As WorkbookConnection
Dim ocn As OLEDBConnection

Set cn = ThisWorkbook.Connections("xrpt_sales_by_category_by_month")
Set ocn = cn.OLEDBConnection

Dim sCommandText As String
sCommandText = "xrpt_sales_by_category_by_month "
sCommandText = sCommandText & "@dt='" & Format(dtMonth, Text) & "', "
sCommandText = sCommandText & "@CategoryName='" & sCategoryName & "'"

With ocn
    .CommandText = sCommandText
    .BackgroundQuery = False
    .Refresh
End With

Application.Cursor = xlDefault

'ActiveWorkbook.RefreshAll

'Refresh all pivot tables
Dim sh As Worksheet, pt As PivotTable
For Each sh In ThisWorkbook.Worksheets
    For Each pt In sh.PivotTables
        pt.RefreshTable
        pt.Update
    Next
Next

ex:
    On Error Resume Next
    Application.DisplayAlerts = True
    Application.Cursor = xlDefault
    Set ocn = Nothing
    Set cn = Nothing
    Exit Function

eh:
    MsgBox "An error occured: " & Err.Number & ", " & Err.Description
    Resume ex

End Function

Open in new window

Save, and close. 
Then, select a month and category, hit the Go button, and the data set will be populated with your selection.

The completed Excel doc is in the below link:
Sales-by-Category-by-Month-DEMO.xlsm
 


Thank you for reading my article, please leave valuable feedback. If you liked this article would like to see more, please click the 'Good Article' button.   

I look forward to hearing from you. -  Jim ( LinkedIn ) ( Twitter )
12
Author:Jim Horn
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.