Dynamically Passing Parameters to a SQL Saved Process in Excel 365 Utilizing Energy Question - Geo Tv News

Dynamically Passing Parameters to a SQL Saved Process in Excel 365 Utilizing Energy Question


In September 2014, I wrote a weblog put up on dynamically passing parameters from PowerPivot to a SQL Server saved process utilizing VBA. Again then, VBA was an actual lifesaver. It maybe nonetheless is for many people. However frankly, I even forgot the right way to write VBA. Perhaps it’s time to have a look at it once more. I additionally wrote a fast tip in August 2020 about doing a comparable factor in Energy BI utilizing Question Parameters. Test it out if you happen to’re eager to know the way it works in Energy BI.

Eight years later, one among my weblog readers requested the right way to do the identical factor in later variations of Excel; he’s particularly asking for Excel 2019. I assumed it might be good to cowl this matter after 8 years and see the way it works now. So, right here it’s, a brand new weblog put up.

The Downside

Infrequently, Excel customers require to get the info from a SQL Server saved process. The saved procedures normally settle for some enter parameters and return the outcomes. However how can we dynamically cross values to the saved procedures from cells in Excel to SQL Server?

Conditions

For this weblog put up, I take advantage of SQL Server 2019 and Microsoft’s well-known pattern database, AdventureWorks2019. You will discover Microsoft’s different pattern databases right here. I additionally use Excel 365, it ought to work the identical means in Excel 2019, although.

The Answer

I talk about two approaches to beat the problem. Each approaches use Energy Question barely in another way. In each approaches, we parameterise the SQL Assertion of the SQL Server connector, passing the values to the parameters from an Excel desk. One strategy requires ignoring the Privateness Ranges in Energy Question, whereas the opposite doesn’t. Each approaches work, however, relying in your preferences, chances are you’ll desire one over the opposite.

As talked about, I take advantage of the AdventureWorks2019 pattern database that comprises a few saved procedures. I take advantage of the dbo.uspGetBillOfMaterials saved process accepting two parameters, @StartProductID and @CheckDate.

Method 1: Parameterising the SQL connector’s SQL Statements, Ignoring Privateness Ranges

Comply with these steps to cross the parameters’ values from an Excel sheet to the saved process and get the ends in Excel:

  1. In Excel, navigate to the Knowledge tab
  2. Click on the Get Knowledge dropdown
  3. Hover over the From Database possibility and click on the From SQL Server Database
  4. Enter the Server
  5. Enter the Database
  6. Develop the Superior choices
  7. Sort EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = 727, @CheckDate = N'2013-01-01' within the SQL assertion textbox
  8. Click on OK
Using SQL Statement in Power Query for Excel
Utilizing SQL Assertion in Energy Question for Excel
  1. Click on the dropdown on the Load button
  2. Click on Load to
Load to Choices to Load the Outcomes of Energy Question question into an Excel Sheet or PowerPivot Mannequin

From right here, we have now some choices to load the outcomes both into an Excel sheet or the PowerPivot information mannequin. We need to load the info into the PowerPivot information mannequin on this instance.

  1. Choose Solely Create Connection
  2. Verify the Add this information to the Knowledge Mannequin possibility
  3. Click on OK
Loading the Power Query Data into PowerPivot in Excel
Loading the Energy Question Knowledge into PowerPivot in Excel

  1. Rename the question to Invoice of Supplies
  2. On the Rename Question warning, click on the Rename button
Renaming a Power Query query in Excel
Renaming a Energy Question question for Excel
  1. The concept is to cross the parameters’ values from an Excel sheet, so I kind the next into the Sheet1:
Entering Values in Excel that will be passed to SQL Server Stored Procedure
Getting into Values in Excel that can be handed to SQL Server Saved Process
  1. Choose the vary we entered within the earlier step
  2. Click on the From Desk/Vary from the Knowledge tab
  3. On the Create Desk window, make sure that the My desk has headers is ticked
  4. Click on OK. This navigated us to the Energy Question Editor window
Getting Data from a Table or Range in Power Query for Excel
Getting Knowledge from a Desk or Vary in Energy Question for Excel
  1. Rename the brand new question to Parameters
Renaming Queries in Power Query Editor in Excel
Renaming Queries in Energy Question Editor for Excel

We now need to change the SQL Assertion of the Invoice of Supplies question to get the values from the related columns within the Parameters desk. Subsequently, every time the consumer adjustments the values on Excel, the outcomes of the SQL Assertion within the Invoice of Supplies desk change accordingly. In Energy Question, we are able to reference the primary worth of a column in a desk as under:

TABLE_NAME[COLUMN_NAME]{0}

So, we use the next expression to get the worth of the StartProductID column from the Parameters desk:

Parameters[StartProductID]{0}
Getting the First Value of a Column in Power Query
Getting the First Worth of a Column in Energy Question for Excel
  1. Choose the Invoice of Supplies question from the Queries pane
  2. Click on the Superior Editor
  3. Exchange the unique expression with the next
let
    Supply = Sql.Database(".sql2019", "adventureworks2019", [Query="EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = " & Textual content.From(Parameters[StartProductID]{0}) & ", @CheckDate = '" & Textual content.From(Parameters[CheckDate]{0}) & "'"])
in
    Supply
  1. Click on Achieved
Referencing Parameters Table from the SQL Statements
Referencing Parameters Desk from the SQL Statements

Relying in your Energy Question privateness settings in Excel, chances are you’ll get the next warning:

Formula.Firewall: Query 'Bill of Materials' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.
Method.Firewall: Question ‘Invoice of Supplies’ (step ‘Supply’) references different queries or steps, so it might in a roundabout way entry a knowledge supply. Please rebuild this information mixture.

We get this warning as a result of we’re referencing the Parameters question from the Invoice of Supplies question, whereas these queries are coming from completely different information sources, which suggests we’re probably sending information from one information supply to a different. This generally is a potential privateness breach, therefore Energy Question’s inside firewall blocks the referencing question. In our state of affairs, we are able to ignore the Privateness Ranges. I recommend studying this doc to grasp the Privateness Degree settings in Energy Question and this one about Privateness Degree Firewall earlier than deciding to disregard the Privateness Ranges.

To disregard the Privateness Ranges, comply with these steps:

  1. Click on the File menu
  2. Click on Choices and settings
  3. Click on Question Choices
  4. Click on the Privateness tab below the CURRENT WORKBOOK part
  5. Click on the Ignore the Privateness Ranges and probably enhance efficiency
  6. Click on OK
Ignoring the Privacy Levels in Power Query for Excel
Ignoring the Privateness Ranges in Energy Question for Excel

Now, the Invoice of Supplies question ought to present the outcomes as the next picture reveals:

The Query Results After Parameterising the SQL Statement
The Question Outcomes After Parameterising the SQL Assertion

Allow us to take a look on the second strategy.

Method 2: Parameterising the SQL connector’s SQL Statements, With out Ignoring Privateness Ranges

On this strategy, we don’t load the parameters desk from Excel into Energy Question. As an alternative, we use the Excel.CurrentWorkbook() perform, which provides us all Excel tables and named ranges within the present workbook. Allow us to have a fast look and see how the Excel.CurrentWorkbook() works.

Utilizing the Excel.CurrentWorkbook() in Energy Question for Excel

As the next picture reveals, I added some dummy information, value1 and value2, in two cells in Excel. I can create a Named Vary by choosing a number of cells and typing a reputation within the Title Field:

Creating Named Ranges in Excel
Creating Named Ranges in Excel

Now we open the Energy Question Editor and undergo the next steps:

  1. Click on the Get Knowledge drop down
  2. Click on the Launch Energy Question Editor
  3. Within the Energy Question Editor window, create a Clean Question by right-clicking the Queries pane and hovering over the New Question > Different Sources > Clean Question
Opening Power Query Editor in Excel and Creating a new Blank Query
Opening Energy Question Editor in Excel and Creating a brand new Clean Question
  1. Within the components bar, kind = Excel.CurrentWorkbook() and commit the change
Using the Excel.CurrentWorkbook() Function in Power Query for Excel
Utilizing the Excel.CurrentWorkbook() Perform in Energy Question for Excel

Within the above picture, the Params desk is the desk we created earlier after we had been creating the primary strategy, which comprises the values we need to cross to the SQL Assertion of the SQL connector. To see the contents of every desk, click on on the cell (not on the Desk) as proven within the following picture:

Viewing a cell's values in Power Query
Viewing a cell’s values in Energy Question

We’re after the worth of the StartProductID column to cross to the @StartProducyID parameter and the values of the CheckDate column to cross to the @CheckDate parameter of the dbo.uspGetBillOfMaterials saved process in our SQL Assertion. With the next expressions, we are able to the values:

  • StartProductID: Excel.CurrentWorkbook()[Content]{0}[StartProductID]{0}
  • CheckDate: Excel.CurrentWorkbook()[Content]{0}[CheckDate]{0}

Now that we all know how the Excel.CurrentWorkbook() perform works, and the right way to get the values for the StartProductID and CheckDate, we go forward modifying the SQL Assertion. To have each approaches in the identical file, I duplicate the Invoice of Supplies question. I additionally change the Privateness Degree setting again to default. Comply with these steps to implement the second strategy:

  1. Choose the duplicated question, Invoice of Supplies (2)
  2. Click on the Superior Editor button
  3. Exchange the earlier expressions with the under one:
let
    Supply = Sql.Database(".sql2019", "adventureworks2019", [Query="EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = " & Textual content.From(Excel.CurrentWorkbook()[Content]{0}[StartProductID]{0}) & ", @CheckDate = '" & Textual content.From(Excel.CurrentWorkbook()[Content]{0}[CheckDate]{0}) & "'"])
in
    Supply
  1. Click on Achieved
Parameterizing SQL Statements of a SQL Connector in Power Query for Excel
Parameterising SQL Statements of a SQL Connector in Energy Question for Excel

Listed here are the outcomes:

The Results of Parameterizing SQL Statements of a SQL Connector in Power Query for Excel
The Outcomes of Parameterising SQL Statements of a SQL Connector in Energy Question for Excel

It’s all executed. We will now click on the Shut & Load button from the ribbon.

I just like the second strategy over the primary one, so I unload the Invoice of Supplies question from the PowerPivot mannequin and hold the Invoice of Supplies (2) question. Now that we efficiently applied the answer, allow us to take a look at it to make sure it really works as anticipated.

Testing the Answer

Testing the answer is straightforward. We solely want to alter the values of the parameters on the Excel sheet and refresh the PowerPivot mannequin. Now we have two choices to take action.

Choice 1, Utilizing the Queries and Connections pane in Excel

  1. Click on the Queries and Connections from the Knowledge tab on the ribbon. At present, the Invoice of Supplies (2) question has 24 rows
Enabling the Queries and Connections Pane in Excel
Enabling the Queries and Connections Pane in Excel
  1. Change the StartProductID‘s worth from 727 to 802
  2. Click on the Refresh button on the Invoice of Supplies (2)
Refreshing Queries from the Queries and Connections Pane
Refreshing Queries from the Queries and Connections Pane

Because the previous picture reveals, the question now comprises 8 rows. To view the info, hover over the question:

Viewing a Sample of the Data of Power Query queries in Excel
Viewing a Pattern of the Knowledge of Energy Question queries in Excel

The info within the above picture is loaded into the PowerPivot mannequin, so we are able to at all times have a look at PowerPivot to see the precise information, which takes us to the second strategy.

Choice 2, Utilizing the PowerPivot in Excel

On this strategy, we modify the values within the Excel sheet and refresh the info instantly from the PowerPivot.

  1. Click on the Handle Knowledge Mannequin button from the Knowledge tab on the ribbon to open PowerPivot
Opening PowerPivot in Excel
Opening PowerPivot in Excel
  1. In Excel, change the worth of the StartProductID again to 727
  2. Swap again to PowerPivot and click on the Refresh button
Refreshing the Data in PowerPivot in Excel
Refreshing the Knowledge in PowerPivot in Excel

Lastly, you would possibly ask about how one can reconcile the info in our exams. Properly, we solely must open SQL Server Administration Studio (SSMS) and run the next SQL assertion:

EXEC [dbo].[uspGetBillOfMaterials] @StartProductID = 802, @CheckDate = N'2013-01-01'
Reconciling the Results by Running the Query in SSMS
Reconciling the Outcomes by Operating the Question in SSMS

That’s it. We made it. I hope you discover this weblog put up useful. In case you have questions or feedback, use the feedback part under to speak with me.



Supply hyperlink

Leave a Reply

Your email address will not be published. Required fields are marked *