topic Re: Help with code to pass parameter in report in Developer

Help with code to pass parameter in report

danielcarreira1 — Fri, 11 Feb 2022 16:07:47 GMT

Hi all,I have made a date parameter for a t-sql stored procedure and tried to insert the name of the parameter on the code, but it doesn't work, see below code from advanced editor and also the error message, appreciate if someone can tell me what i am doing wrong.

let Source = Sql.Database("sap", "entie", [Query="execute [1test] "&Date]), #"Extracted Date" = Table.TransformColumns(Source,{{"Date", DateTime.Date, type date}}), #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB")[Date]), "Date", "Balance", List.Sum) in #"Pivoted Column"

ERROR MESSAGE:

Expression.Error: We cannot apply operator & to types Text and Date.
Details:
Operator=&
Left=execute [1test]
Right=01/01/2020

Best Regards,

Daniel

Re: Help with code to pass parameter in report

lbendlin — Sun, 13 Feb 2022 03:42:01 GMT

[Query="execute [1test] " & Text.From(Date)]

But you likely still won't be able to run that. What date format is the SP expecting?

Re: Help with code to pass parameter in report

danielcarreira1 — Mon, 14 Feb 2022 09:28:59 GMT

Hi Ibendlin,

It didn't worked, the SP is expecting a date format

Best Regards,

Daniel

Re: Help with code to pass parameter in report

lbendlin — Mon, 14 Feb 2022 16:09:10 GMT

which exact data format ? yyyy-MM-dd ?

Re: Help with code to pass parameter in report

Anonymous — Tue, 15 Feb 2022 05:35:42 GMT

Hi @danielcarreira1 ,

Please update the applied codes in your Advanced Editor as below, the part with red font is updated one. Please note that the Date with blue font is date parameter name....

let
Source = Sql.Database("sap", "entie", [Query="exec [1test] '"& Date.ToText(Date) & "' #(lf)#(lf)#(lf) #(lf) "]) ,
#"Extracted Date" = Table.TransformColumns(Source,{{"Date", DateTime.Date, type date}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Extracted Date", {{"Date", type text}}, "en-GB")[Date]), "Date", "Balance", List.Sum)
in
#"Pivoted Column"

In addition, you can also refer the following links to update the codes...

Execute SQL Server Stored Procedure With User Parameter In Power BI

parameters to SP or Sql query

Best Regards

Re: Help with code to pass parameter in report

danielcarreira1 — Tue, 15 Feb 2022 10:23:13 GMT

Hi Ibendlin,

The date format is dd/mm/yyyy

Best Regards,

Daniel