Forum Discussion

MRZ's avatar
MRZ
Frequent Visitor
10 years ago
Solved

parameters to SP or Sql query

There is probably a simple answer but after looking all over for an answer and trying various methods, the right code still escapes me.  The scenario is I have a stored procedure that uses two input parameters, a start and end date. If I hard code the date in the call, getting the data works fine. I defined two parameters in Power BI and have been trying to figure out how to call the SP with these 2 paramters. My paramters are named Sdatex and Edatex. Both are defined as date type variables.

here is my call that works:

DECLARE @return_value int

EXEC @return_value = [dbo].[Capacity_SP]

@SDate = '8/11/2016',

@EDate = '8/12/2016'

SELECT 'Return Value' = @return_value

 

I also tried to execute the SP code (a CTE) insted of the SP and I have the same issue. Hard coded dates work but I do not know how to tell Power BI that I want the paramters (Sdatex and Edarex) used instead of the date.

Any help would be appreciated.

  • MRZ's avatar
    MRZ
    10 years ago

    Thank you so much. Will give this a go as soon as possible.

  • MRZ's avatar
    MRZ
    10 years ago

    By the way, this work fine. Thanks so much. Now I need to find out how to update the parameters in PowerBI.com so that i can refresh the data as needed. Thanks again.

28 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Hi MRZ,

    I made a test to call stored procedure with parameters in Power BI Desktop. You can review the following example to apply it to your scenario.

    In SQL Server, I create a procedure named p4test in test database of v-kaxion2015tes\sql2016tabular server.

    Create PROC p4test @sdate date,@edate date
AS
BEGIN
SELECT @sdate StartDate, @edate Enddate
END


    1. In Query Editor of Power BI Desktop, click "New source"> "SQL Server", enter server name, database name and statement "exec p4test '8/11/2016', '8/12/2016'", click ok.

    2. Right click on that query on the left panel and select Advanced Editor, and paste the below code.

    let
    SQLSource = (param1 as date, param2 as date) =>
let  
    Source = Sql.Database("v-kaxion2015tes\sql2016tabular", "test", [Query="exec p4test '"& Date.ToText(param1) & "','" & Date.ToText(param2)&"' #(lf)#(lf)#(lf) #(lf)"])
in
    Source
in
    SQLSource

    3.Click “Invoke” button and enter parameter values as shown in the following screenshot.



    Thanks,
    Lydia Zhang

    • Sunkari's avatar
      Sunkari
      Responsive Resident
      9 years ago

      Anonymous: May i know how this works in Power BI Service. If it is not going to work in Power BI Service, then is there any alternative to achieve the same kind of functionality in Power BI Service

      • Fugi's avatar
        Fugi
        Helper I
        9 years ago

        We are also looking for a solution to this from the service... it doesn't appear to exist as far as I can tell...

    • Raghuvardhan's avatar
      Raghuvardhan
      Frequent Visitor
      9 years ago

      Hi All,

       

      Can any one Plz help me out ,

       

      Is it possible to get the Invoke function , like as a parameter in desktop to pass i/p value as a parameter to my direct query (SP) .

       

      Based on that i/p value my data has to be refreshed or updated . 

       

      Is it possible to desible  the "Refresh data" or "Edit Permission" warnings messages always .

       

      Thanks 

       

      Raghu

      • anakoom's avatar
        anakoom
        Helper I
        9 years ago

        I have similar issue let me know if you find any solution

    • SvenTexas's avatar
      SvenTexas
      Frequent Visitor
      9 years ago

      Thanks Lydia, this worked perfectly.  Would you have any idea how to just pass a textual value, instead of date.  So Text.ToText or similar? 

       

       

      • Firoj's avatar
        Firoj
        New Member
        8 years ago

        let
        SQLSource = (param1 as text) =>
        let
        Source = Sql.Database("gx-zwesqld038.database.windows.net", "ITXTestInterikea2", [Query="EXEC [ITX].[ExcludedTransactionsReportByTransactionCount] '"& (param1) & "' #(lf)#(lf)"])
        in
        Source
        in
        SQLSource

         

        This will Work 

         

         

         

        Regards,

        Firoj Shaikh

        [email protected]

  • KarenL7's avatar
    KarenL7
    Advocate V
    6 years ago

    Hi 

    Using this query I have been able to manage building a parameter to dynamically use my Stored Procedure Parameters just like I use in my SSRS reports.

     

    However, I have an extra Text Parameter so when Invoke the parameter I get an error

    "1 arguments were passed to function which expects between 2 and 3 power bi parameter function"

     

    This is the code which I amended from above - No syntax errors were found

     

    let

        SQLSource = (Title as text, StartDate as date, EndDate as date) =>

     let  

        Source = Sql.Database("ServerName", "DatabaseName", [Query="EXEC [Schema].[StoredProcedureName] '

        "& Text.Contains(Title) & "','" & Date.ToText(StartDate)&"''" & Date.ToText(EndDate)&"' #(lf)#(lf)#(lf) #(lf)"])

     in

         Source

     in

         SQLSource

     

    The first parameter can store a list of text fields as set up in the stored procedure - SSRS parameters can handle these presumably Power BI parameters can too - perhaps I am using the wrong text fields?

     

    Any help would be great

     

    Thanks

     

    Karen