PowerPivot and Dynamic SQL Server Saved Procudure

Replace September 2022:

I wrote a brand new weblog put up protecting the identical state of affairs in Excel 365.

On this put up, I specific a straightforward option to refresh a PowerPivot mannequin dynamically based mostly on SQL Server Saved Procedures. Let’s begin with SQL Server Administration Studio (SSMS) 2012 and use Journey Works 2012 database. Run the next script to see the leads to SSMS:

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

First parameter: Begin Product ID

Second Parameter: Verify Date

Outcomes:

Now we wish to see the outcomes for the next script:

exec [dbo].[uspGetBillOfMaterials] 762, ‘2009-01-02’

 Outcomes:

Now we wish to do the identical with PowerPivot.

·         Open Microsoft Excel, I’m utilizing Excel 2013, then go to PowerPivot tab within the ribbon and click on “Handle”

·         Click on “Get Exterior Knowledge”

·         Click on “From Database”

·         Click on from SQL Server

·         Sort a pleasant title for the connection, sort a server title and database title then click on Subsequent

·         Choose “Write the question that can specify the information to import” then click on Subsequent

·         Sort a pleasant title for the question and put the next script within the SQL Assertion textual content field then click on End

exec [dbo].[uspGetBillOfMaterials] 727, ‘2009-01-02’

·         As you possibly can see 24 rows are transferred

·         Now you possibly can see the leads to PowerPivot

·         Shut PowerPivot and go the information tab within the Excel ribbon then click on “Connections” from connections part

·         Choose SP_Connection1 then click on Properties

·         Go to “Definition” tab. As you possibly can see the connection is a read-only connection and we’re unable to vary the command textual content

·         The unhappy information is that the connection is read-only perpetually and we’re unable to switch it from VBA.

·         Click on OK to shut the connection properties window. DO NOT shut the “Workbook Connections” window

·         Choose the SP_Connection1 once more and this time click on the “Add…” button and the clicking “Add to the Knowledge Mannequin”

·         Go to the “Tables” tab and choose “PowerPivot_SP” then click on “Open”

·         This may add a brand new connection to the workbook with the identical settings

·         Choose the brand new connection that’s routinely named “SP_Connection11” then click on the “Properties” button to rename the connection to SP_Connection2

·         Change the connection title to SP_Connection2. If you happen to click on on the “Definition” tab then you possibly can see that the brand new connection is NOT read-only, so we’ll be capable of modify it utilizing VBA.

·          Click on OK to shut the connection properties. Now the connection is renamed to SP_Connection2.

·         Go to PowerPivot once more to examine what we have now within the mannequin now

·         As you see there’s a new desk added to the mannequin with the identical outcomes because the “PowerPivot_SP” desk

NOTE: DO NOT RENAME THE TABLE OR ANY OF THE COLUMNS. IF YOU DO SO, THE NEW CONNECTION WILL GET READ-ONLY AND YOU’LL BE UNABLE TO CHANGE THE SQL QUERY ANYMORE.

·         Now press “Alt+F11” to open visible primary for Excel

·         From “Microsoft Excel Objects” double click on on “ThisWorkbook”

·         From the objects drop down choose “Workbook” and the choose “SheetChange” process

·         Copy and paste the next VBA scripts to vary the SP_Connection2 dynamically. The values of A2 and B2 cells can be handed to the SQL Server saved process and the connection can be refreshed to fetch the outcomes from SQL Server:

NOTE: The above code will refresh the PowerPivot knowledge everytime you modify the worth of the B2 cell within the present lively worksheet. You may take away the MsgBox line from the code. I’ve put this half for testing functions.

·         Press “Alt+F11” once more to return to Excel and put 762 within the A2 cell and 2009-01-02 within the B2 cell and press Enter

·         As you possibly can see the message field exhibits us that the SP_Connection2 is modified based mostly on the values of cells A2 and B2.

·         Open PowerPivot once more to examine if the information are loaded to the mannequin accurately

·         Lastly we should always save the file. Choose “Excel Macro-Enabled Workbook (*.xlsm)” from the save as sort, in any other case you’ll get the next message

Now you can delete the PowerPivot_SP desk from the PowerPivot mannequin.

Now you possibly can insert the pivot desk in excel, modify the values of A2 and B2 cells and the pivot desk will routinely refresh.

Right here is the outcomes of the saved process with totally different values for the parameters:

Begin Product ID = 727

Verify Date = 2009-01-02

Begin Product ID = 762

Verify Date = 2009-01-02

We’re achieved now.

Perhaps a few of you guys suppose that it’s actually a ache that you simply can not rename the desk and it’s columns from PowerPivot. Nicely, I ought to say that I do agree with you. However, sadly, it’s the way it works for now. I’ve achieved numerous investigations to discover a option to modify the SQL Assertion in “Edit Desk Properties” from PowerPivot, however, it appears it’s untouchable trough VBA. You could find the “Edit Desk Properties” from PowerPivot, Design tab then click on on “Desk Properties”.