Toronto
Join DataRails in Toronto for ToFIS!
June 10, 2018
Addressing The Challenge of Data Agility
June 18, 2018

Running an SQL Query with Parameters, Part III

Three Generations of the Same Task

By Dany Hoter, DataRails Solutions Architect

In parts I and II of this series, I reviewed different methods of importing data into Excel from an SQL command with some input parameters. The goal was to include parameter values coming from cells in Excel, with data automatically refreshed anytime the value of any parameter changes in the grid.

This week, I discuss how to accomplish this goal with the new kid on the block.

Using Power Query with Parameters

Power Query (PQ), aka Get & Transform in Excel 2016, supports parameters that can be used to filter data. I will not use this feature in my PQ example because it is not possible to link parameters to cells in Excel, and in general, changing a value of a parameter is too complicated for a regular business user.

Instead, I’ll describe a different mechanism to parametrize filtering in PQ which pulls the values for parameters from cells in Excel. Once this is the case, I can also automate the refresh anytime the cell value changes, similar to what I did here.

In this example, you can see a query that filters the DimDate table using two parameters to limit the calendar year and one parameter to filter the weekday.

Each parameter comes from a query that pulls a single value from a named range in Excel. To create such a query, you need to select the cell that contains the value, and create a new query from table/range.

Once in the query, you simply create a new step by using right-click/Drill Down from the actual value returned from the cell. This will return a value instead of a table, and thus will enable the result of the query to be used as a filter.

SQL image 13

In the query that reads the actual table from SQL, you can use the name of the query as the value by which to filter. You first create the filter in the regular way:

SQL image 14

In the formula bar, you can change the specific value to the query name that contains the selected day.

SQL image 15

The last step is identical to the one described here. If I want to use three parameters, I could use this VBA code:

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Range(“FromYear”), Target) Is Nothing Or Not Intersect(Range(“ToYear”), Target) Is Nothing Or Not Intersect(Range(“Day”), Target) Is Nothing Then

       ActiveWorkbook.RefreshAll

   End If

End Sub

The final result is here.

Another Variation with PQ

In Excel 2016, Power Query was integrated into Excel and renamed to Get & Transform. Consequently, the queries are accessible thru VBA and I can now change the query programmatically. So now I can manipulate the text of the query in a similar way to our example using an SQL query.

Why would I do it this way and not through parameters? In this manner, the kind of changes I can make to the query are more radical. I might choose to change from a column=parameter to column contains parameter. I can change between column=parameter to column <= parameter. So the values themselves can be dynamic, as well as other parts of the query.

Obviously, these kinds of changes can become more and more complicated, but are still possible. You can create the initial query with the PQ editor, which offers a lot of power.

The example with VBA changing the query is here. Instead of manipulating the connection command text, I change the query formula. The formula property contains the actual M script that is run during refresh.

The example is a simple one and I only change the value for a single parameter. In this case, this is the script:

SQL image 16

Notice that I used the special value ~Day~ as the value for filtering the day. This is to make it easier to substitute the value with a real day name.

The VBA code used this time is :

Private Sub Worksheet_Change(ByVal Target As Range)

   If Not Intersect(Range(“Day”), Target) Is Nothing Then

       ActiveWorkbook.Queries(1).Formula = Application.WorksheetFunction.Substitute(ActiveWorkbook.Queries(1).Formula, “~Day~”, Range(“Day”))

       ActiveWorkbook.RefreshAll

       ActiveWorkbook.Queries(1).Formula = Application.WorksheetFunction.Substitute(ActiveWorkbook.Queries(1).Formula, Range(“Day”), “~Day~”)

   End If

End Sub

I change the formula property of the query and then I refresh the connection. The object in the front is the query while the connection is rarely used in the context of PQ.

This concludes this mini-series about using parameters in SQL queries.

One method I did not include is using the query parameters feature in PQ. The reason is that it is very non-intuitive for an end user to change the value of such a parameter when it is not linked to cells in Excel. If you want to urge the Excel team to create the obvious link between parameters and Excel cells, please vote for this feature here. You’d be surprised, but the number of votes really does influence the team’s priorities.

 

dany

 

About the author:

Dany Hoter is a renowned Excel guru who works as a solutions architect at DataRails. Dany has over two decades’ experience working as an expert for the Microsoft Excel team, and his online Excel classes reach over one million students across the globe.

 

 

Request a demo
[contact-form-7 404 "Not Found"]