Using a stored procedure as data source with multiple parameters.

[Susan13]

I wonder if you can help me with an issue I have with Power BI. My data source is a stored procedure which has several parameters with default values, however I would like the users to have the ability to change the parameter values when refreshing the data.

This is the current script in the query editor, which uses the default settings:

let

    Source = Sql.Database("ServerName", "DatabaseName", [Query="EXEC [dbo].[brptARAge] #(lf)#(tab)  @Company = 1, #(lf)#(tab)  @Month = '7/30/2019' ,#(lf)      @AgeDate = '7/30/2019',#(lf)      @BegCust = 0,#(lf)      @EndCust = 99999999,#(lf)      @RecType = NULL,#(lf)      @IncludeInvoicesThrough = '7/30/2019',#(lf)      @IncludeAdjPayThrough = '7/30/2019',#(lf)      @AgeOnDueorInv = 'D',#(lf)      @LevelofDetail = 'I',#(lf)      @DeductDisc = 'Y',#(lf)      @DaysBetweenCols = 30,#(lf)      @AgeOpenCredits = 'N',#(lf)      @BegCustName = ' ',#(lf)      @EndCustName = 'zzzzzzzzzzzzzzz',#(lf)      @Sort = 'S';", CommandTimeout=#duration(0, 0, 10, 0)])

in

    Source

I have created all the parameters but I am not sure how to modify the script so that when the data is refreshed the user is prompted to enter new parameter values if required, if not then they will use the default values.

[Greg_Deckler · Accepted solution]

Simply replace the text with your parameter name. For example, if you have a parameter called Parameter1 that has the name of the Company, then you could do this:

let

    Source = Sql.Database("ServerName", "DatabaseName", [Query="EXEC [dbo].[brptARAge] #(lf)#(tab)  @Company = Parameter1, #(lf)#(tab)  @Month = '7/30/2019' ,#(lf)      @AgeDate = '7/30/2019',#(lf)      @BegCust = 0,#(lf)      @EndCust = 99999999,#(lf)      @RecType = NULL,#(lf)      @IncludeInvoicesThrough = '7/30/2019',#(lf)      @IncludeAdjPayThrough = '7/30/2019',#(lf)      @AgeOnDueorInv = 'D',#(lf)      @LevelofDetail = 'I',#(lf)      @DeductDisc = 'Y',#(lf)      @DaysBetweenCols = 30,#(lf)      @AgeOpenCredits = 'N',#(lf)      @BegCustName = ' ',#(lf)      @EndCustName = 'zzzzzzzzzzzzzzz',#(lf)      @Sort = 'S';", CommandTimeout=#duration(0, 0, 10, 0)])

in

    Source