Passing Parameters to a Stored Procedure

[tlundman]

I have been struggling passing a value to a SQL stored procedure using the Power BI parameters as user inputs.

Example 1

I have created a SQL query as follows;

select *
from [XXXXX$E_D_I_ Inventory Advice Line] ial
join (select [Sell-to Customer No_], max(No_) as InvAdvNo from [XXXXX$E_D_I_ Inventory Advice Header] group by [Sell-to Customer No_]) iah on ial.[Inventory Advice No_] = iah.InvAdvNo
where iah.[Sell-to Customer No_] = XXXXX

This query creates a table of Inventory Advice lines that can then be filtered by the customer number for which those lines are created.

I subsequently created a stored procedure that requires the Sell-to Customer No_ as an input.

When I use the above mentioned stored procedure as a Data Source in Power BI it appears as as follows;

let

Customer=Customer,
Source = Sql.Database("xxx-xx-xxx\xx", "XXX_Production", [Query="exec xxxInventoryAdvice '" & Customer2 & "'"])
in
Source

I set my Parameter as Customer2

I then attempt to create a new parameter in Power BI as follows;

The value I select at this level as the current value updates the stored procedure as Customer2 and upon applicaiton or refresh the resulting table is created perfectly.  

The problem I am having is that I cannot identify a way for the end user of the report to supply that current value to the parameter, which then updates the underlying stored procedure, from the actual report.

The only way I can see to udpate the parameter is to select the Edit Queries button under the home tab, highlight the parameter, select which paramter value to use from a list I created on the manage parameters page, then select the Apply and Close button.  This updates my stored procedure and creates the correct table.

I have created a splicer and a What If Parameter slider but it does not seem to connect to the underlying stored procedure.  The what if parameter does not seem to apply in this case.  The splicer either contains only the value that I set as the current value in the manage parameters page, or, if a table of customer numbers in this case is used, the only value that will return any results must be the one already set on parameter as the current value.  

I would like to have the list of values that I entered under the parameter, or even better,  a second query results to populate the splicer selections.  The selections made on the splicer would then update the original stored procedure as the customer2 value.

If anybody has any idea how to do this I would be very interested.

Thanks,

[v-lili6-msft]

hi, @tlundman 

first, Slicer and a What If Parameter slider is based on the data model, it won't affect the basic data query.

new parameter in Power BI is based on the data source.

For your case, i think there are two ways for you refer to:

1. Continue to use new parameter in Power BI.

Then change the parameter in power bi service as below:

https://docs.microsoft.com/en-us/power-bi/service-parameters

https://www.mssqltips.com/sqlservertip/4475/using-parameters-in-power-bi/ 

2. Adjust your Stored Procedure, Import all the data into power bi.

Then drag [Sell-to Customer No_]  into a slicer to filter report(visual).

Best Regards,

Lin