Pass parameters to Adcanced Editor

[volkanbygl]

Hello,

I have a stored procedure which requires three parameters. I am using Import mode, and I can use the stored procedure with fixed parameters like this: 

exec SP_MY_STORED_PROCEDURE '2017-10-01', '2017-12-31', 19

I need to enable users to enter their own parameters when starting this document. 

I created three parameters FromDate, ToDate, Number and now I need to incorporate them in the query inside ADVANCED EDITOR. 

Can you please tell me what is the syntax? Also what datatypes to use for parameters? 

Thank you

[volkanbygl · Accepted solution]

I found this and it works:

let
Source = Sql.Database("DB_SERVER", "DB_NAME", [Query="exec STORED_PROCEDURE_NAME'"&Date.ToText(Date.From(DateFrom), "yyyy-MM-dd")&"', '"&Date.ToText(Date.From(DateTo), "yyyy-MM-dd")&"' , 62", HierarchicalNavigation=true])
in
Source

And it works! Thank you guys 

[Rfranca]

hi, @volkanbygl

If interested you can refer to

Power-BI-desktop-parameters-using-list-queries

or

Table-that-Dynamically-Pulls-Start-and-End-dates

Please try and test using it. And please mark the right reply as answer if your issue has been resolved, otherwise, please feel free to ask if you have any other issue.

Best Regards,
Rfranca

[volkanbygl]

Thanks, but your reply doesnt help me at all...

[stretcharm]

You have to do some string manipulation

I've used some parameters in some of my queries in this example pbi doc.

http://community.powerbi.com/t5/Data-Stories-Gallery/SSIS-Catalog-DB-Dashboard/m-p/244677#M1110

This is not a proc call, but hopefully you get the idea. If your params are dates convert them to text with  Date.ToText

let
    SQLCmd = 

"
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

DECLARE @startexecutionid INT
SELECT @startexecutionid = MIN(execution_id)
FROM    [catalog].[executions] ex
WHERE ex.[start_time] > DATEADD(DAY, -1* "&Number.ToText(MaxDaysData)&", CONVERT(DATE, GETDATE()));

   SELECT 
            [executable_id] AS ExecutableID ,
            [executable_name] AS ExecutableName ,
            pk.package_id AS PackageID ,
            exb.[package_name] AS PackageName ,
            [package_path] AS PackagePath,
            pr.project_id AS ProjectID,
            pr.name AS ProjectName
   FROM     catalog.executables AS exb
            INNER JOIN catalog.packages AS pk ON pk.name = exb.package_name
            INNER JOIN [catalog].executions ex ON ex.execution_id = exb.execution_id
            INNER JOIN catalog.projects pr ON pr.name = ex.project_name
                                              AND pr.project_id = pk.project_id
WHERE	ex.execution_id >= @startexecutionid
GROUP BY exb.executable_id, exb.executable_name, pk.package_id, exb.package_name, exb.package_path, pr.project_id, pr.name


",
    Source = Sql.Database(ServerName, "SSISDB", [Query=SQLCmd]),
    #"Filtered Rows" = Table.SelectRows(Source, each ([PackagePath] <> "\Package")),
    #"Invoked Custom Function" = Table.AddColumn(#"Filtered Rows", "PackageType", each fnVLOOKUP([PackageName], PackageTypes, 2, true)),
    #"Changed Type" = Table.TransformColumnTypes(#"Invoked Custom Function",{{"PackageType", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "PackagePath", "PackagePath2"),
    #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column",".dtsx","",Replacer.ReplaceText,{"PackageName"}),
    #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","\Package","",Replacer.ReplaceText,{"PackagePath2"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value1", "PackagePath2", Splitter.SplitTextByEachDelimiter({"\"}, QuoteStyle.Csv, true), {"ParentPath", "Child"}),
    #"Changed Type0" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"ParentPath", type text}, {"Child", type text}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type0", "Parent", each if [ParentPath]="" then [PackageName] else [ParentPath]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom2",{{"Parent", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "ChildKey", each Number.ToText([ProjectID])&":"&Number.ToText([PackageID])
&":"&[Child]&":"&[PackageName]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ParentKey", each Number.ToText([ProjectID])&":"&Number.ToText([PackageID])
&":"&[Parent]&":"&[PackageName]),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"ChildKey", type text}, {"ParentKey", type text}})
in
    #"Changed Type2"