<

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

Published on
24,068 Points
4,868 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
Comment
Author:Jim Horn
  • 4
  • 3
  • 2
  • +3
12 Comments
LVL 66

Author Comment

by:Jim Horn
>the button to save an article without submitting it is gone.
Nice speech, but perhaps you did not read the above line?

Thanks for understanding.
0
LVL 66

Author Comment

by:Jim Horn
>—and qualifying you to receive points towards a free Premium Services membership, possible awards
I have double-EE-for-life around here, check my member profile, but thanks..

>Our content detection process suggests that the article you submitted may have similar content to articles on another website.
This is completely original work.

>If you use Google+, you can enable "Google Authorship” on Expert’s Exchange
I'll investigate this if I ever do publish my articles on other sites.

Thanks.
Jim
0
LVL 1

Expert Comment

by:curtwade
I have been using a similar technique for years and users love it!  We have recently converted to another main database system here at our hospital and will now have hundreds of more users for files like this.  That said, our DBAs do not want to maintain all these new users on the server so I cannot use 'Windows Authentication' to connect to the database.

Instead, they want me to use a "local" user on the server.  The issue now is how to hide/encrypt/obfuscate that user's password in the 'Connection String'.  Any good advice on how to do that?

TIA,
Curt
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

LVL 66

Author Comment

by:Jim Horn
Hi Curt - Thanks for the feedback.  Sadly, no, I never had to go down that road, as this client was an airline and very little is considered confidential.   Since I wouldn't want to go down the road of attempting to encrypt a password into a connection, the only thing that really comes to mind is to 'roll your own' by passing the Windows ID as a parameter to the Stored Procedure, and then the SP can take that Windows ID and validate if it should return data or not.

Good luck.
Jim
0
LVL 1

Expert Comment

by:curtwade
Well, "passing the Windows ID as a parameter to the Stored Procedure" (or SQL view) is not an option for me these days because using 'Windows Authentication' to connect to the database is no longer an option because the DBAs do not want to maintain a bunch of users' rights on the SQL server.  So, a "local" user (e.g., clarityoptime) on the server is my only option.  Here's what I've come up with so far to make our InfoSecurity and DataIntegrity folks happy, because here at a hospital, securing patient info (aka, PHI based on Federal HIPAA constraints) is kinda, well, VERY important.  Please see the attached file for an example.  Things to know:

1. Obviously, one does not include the [Obfuscate] sheet from the example files in the user-distributed file, and this sheet is used in a separate file to get the password in its obfuscated format.  Notice how it uses the TRUE and FALSE as the second parameter, and the formulas in Cells B2 and B3.

2. The [Procs_OR_Alias_2] sheet is the one with the OBDC-based query behind it which pulls data from our SQL database and presents it to the user in Excel.  Typically, though, such files include multiple SQL tables.

3. The VBA code in the file has the Private Sub = Set_Connection (which has .SavePassword = False) and the Function = Obfusc (which I borrowed from I-forgot-who on the internet) is password protected in the real world - no one can "see" the VBA code unless they guess what the password is (and if anyone ever guesses that password, then I'm probably out of a job!).  The VBA code is NOT password protected in the attached sample.  And when users get clever (perhaps) and dig into the 'Connection String', all they see is this - which is useless without the password, even if they know the username (UID):

   DSN=ClarityOpTime;Description=ClarityOpTime;UID=clarityoptime;;APP=2007 Microsoft Office  
   system;WSID=TKNOW0968;DATABASE=Clarity;LANGUAGE=us_english

If you have time, please play with this and let me know what you think.  I have changed a few source files from filename.XLSM to filename.ZIP and none of the underlying component files have the "real" password (that I could find) - they only have the obfuscated password.

Thanks!
Curt
Obfuscate.xlsm
0
LVL 13

Expert Comment

by:Koen Van Wielink
@Jim:

Is there any specific reason you use VBA to populate the parameters of the stored procedure instead of just selecting the cells which the user enters as the parameter values directly? We have been using a similar solution and just replace the parameter values with question marks in the procedure call, so we can indicate which cell to use. Is there an issue with this method as far as you know?

@Curt:

Why are your DBA's not managing the rights through AD user groups (assuming you have an Active Directory). We also use Windows authentication but everything is managed on group level so the DBA doesn't really have to look at it once it's set up. Just add the user to the appropriate AD group and authentication is automatically granted.
0
LVL 1

Expert Comment

by:curtwade
In a nutshell, it all boils down to "speedy customer service" and saving electrons and work for the DBA's.  First off, users (yes, based on AD accounts) must be put into a "network group" which gives them rights to where the file lives on the network, and a non-DBS group at our company takes care of such "ITS Service Requests" (aka an "ITS SR").  For the user to also be put into an AD group which gives them also rights to the database(s) on SQL server (as well as SQL views) via AD authentication requires yet another ITS SR.  And we're constantly backlogged on both such requests, but the making-them-a-member-of-the-network-group requests are usually fulfilled within a day or two - and the stuff the DBA's would have to do can often take weeks.

Make sense?
0
LVL 13

Expert Comment

by:Koen Van Wielink
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.
0
LVL 1

Expert Comment

by:curtwade
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? ;-)
0
LVL 16

Expert Comment

by:Richard Olutola
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?
0

Expert Comment

by:Harreni
Nice article Jim, it helped me a lot.
0
LVL 8

Expert Comment

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

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

Join & Write a Comment

This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month