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

Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.
Published:
Updated:
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
8,861 Views
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Microsoft SQL Server database developer, architect, and author specializing in Business Intelligence, ETL, and Data Warehousing.

Comments (12)

Koen Van WielinkBusiness Intelligence Specialist

Commented:
From a business perspective, not really, but i know how these things can work out in practice. Sounds like you guys could do with some business process overhaul and/or extra staff.
Anyway, was just a thought.

Commented:
Right, Koen.  And our current solution is to hire a bunch of consultants from Accenture ($$$) to tell us the "best" way to do it.  When I had my session with one of the Accenture folks and explained my current method (i.e., hiding the ODBC connection behind some password-protected VBA code), she did a double-take and said "We've never thought of that.  What a great idea!".  Gee, should I send them a bill? ;-)
Rich OluConsultant

Commented:
I hit a problem on the line:
Call fn_execute (Range("month_value").Value, Range("category_value").Value

It gives message: Run-time error '1004':
Method 'Range' of object' _Global' failed

Any ideas?
HarreniArchitect

Commented:
Nice article Jim, it helped me a lot.
CERTIFIED EXPERT

Commented:
Nice article, Jim. Didn't know this can be done with Excel and stored proc.

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.