Forum Discussion

Susan13's avatar
Susan13
Frequent Visitor
7 years ago
Solved

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 abili...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Community Champion
7 years ago

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