Example of connecting to a relational database - Adventure Works

From XLCubedWiki

XLCubed now includes a updated version of this feature, more information about which can be found in this article.

This example uses the Adventure Works database and view vDemoView.

You can download the Excel workbook that is used in the example here File:ConnectRelationalDB.zip

Connecting to your own server and database

To connect to your server and copy of Adventure Works database perform the following steps:

• Add a quote to start of contents of cell B12 as below

        '=XL3QueryTableSetConnectionString("Results","QueryTable1",$B$17)

This will stop the workbook timing out as it tries to connect.

• Change the server name in cell B3 to be that of your server.

• Change the database name in cell B4 to point to your copy of Adventure Works.

• Remove the quote from the start of contents of cell B12. Once you have connected successfully to your server and database you may wish to comment out again by placing a quote at beginning of string – this stops the workbook trying to reconnect again on refresh.

Using this workbook example

• Parameters 1 – 5: in cells B6 – B10 we have the results of five different filters the user selected.

• Connection string: cell B12 contains the connection string – if you look in the formula part at the top of the spreadsheet you can see:

  =XL3QueryTableSetConnectionString("Results","QueryTable1",$B$17)

XL3QueryTableSetConnectionString will also be taking settings from cell B17 which hold further parameters such as server and database name.

• Set SQL: XL3QueryTableSetSQL – cell B13 holds the following formula:

       =XL3QueryTableSetSQL("Results","QueryTable1",$B$14)

• SQL statement: In cell B14 we have the SQL statement that will bring back our query results:

     ="select * from dbo.vDemoView where region = '"&B7&"' and EnglishProductCategoryName= '"&B6&"'                   
      and IncomeGroup=  '"&B8&"' and monthyear='"&B11&"'"

The four cell locations in the SQL statement correspond to the five different filters. Cell B11 is combination of B9 and B10 (month and year) as this is held as one value in the database.

• In cell B17 we have the rest of the connection string – it shows that server name is in cell B3 and database name in cell B4.

        ="DRIVER=SQL Server;Trusted_Connection=Yes;SERVER="&$B$3&";DATABASE="&$B$4&""

• vDemoView currently holds in excess of 64000 records so we can filter to reduce the number of rows returned. I ran simple queries to return the distinct values in the database for region, product, income group and period and placed these in Sheet3 of the spreadsheet.

• Excel drop-down boxes were created for each of the parameters – 5 in total.

• To create drop-down boxes you need access to the Developer ribbon: File, Options, Customize ribbon, check Developer box and click OK.

• Select Insert button on Developer ribbon to add a combo-box:

• Place this combo-box in a cell. You can right-click the combo-box to format it.

• Under the control tab you will need to enter an input range (the values that you want to appear in the drop-down box). In this example the values are on Sheet3 in cells F12 – F14 and correspond to the three different product groups available.

• Link the combo-box to cell C6 so that whichever value is selected by the user the corresponding index location is displayed in cell C6. The value in that index location is then displayed in cell B6 by use of the following statement in cell B6

           =INDEX(Sheet3!F12:F14,Results!C6)

• Five combo boxes were created in a similar way, one for each parameter.

• When a value is selected from the drop-down box the SQL query is rerun and the results refreshed at the bottom half of the screen.