Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
Susan13
Frequent Visitor

Using a stored procedure as data source with multiple parameters.

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.

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.