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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MRZ
Frequent Visitor

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.

2 ACCEPTED SOLUTIONS

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

View solution in original post

MRZ
Frequent Visitor

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.

View solution in original post

28 REPLIES 28
Anonymous
Not applicable

 

Guys, you dont need to use Text.ToText, It is required for only non text values. 

for example: '"& VariableName & "'

Hi Sven, just dont add the "Text.ToText" part. it's already text.

 

for example:

 

let
    SQLSource = (param1 as text, param2 as date) =>
let  
    Source = Sql.Database("MyServer", "MyDatabase", [Query="exec [MyDatabase].[dbo].[myProc] '"& param1 & "','" & Date.ToText(param2)&"'"])
in
    Source
in
    SQLSource

 I hope this helps.

 

Alan

Thank you Alan - can't believe I missed the obvious!  Thank you for putting the response on, am sure it will help others out too.

@v-yuezhe-msft: 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

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

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

MRZ
Frequent Visitor

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.

Seems this is a solution for PBI desktop only.

Dont know what will be the benifit of this if we have to modify parameter every time a new range of data is required and then publish the report. 

 

I was looking for similar implementation in service side but no success so far.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors