Forum Discussion

RussellBode's avatar
RussellBode
Icon for Helper I rankHelper I
1 year ago
Solved

Integrating Web API Parameters in Power BI Paginated Reports

 

Hello, I’m currently working on integrating Power BI Paginated Reports with a Web API.

We have the following M code query that works in Power BI Desktop because the parameter PAR_SEL_PROJECT can be passed from Power BI Desktop, but this does not work in Report Builder:

 
let Source = Json.Document(Web.Contents("https://my429741.businessbydesign.cloud.sap/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ0CF89333C75D1A77ED7826QueryResults?$filter=(PAR_SEL_PROJECT eq '" & PAR_SEL_PROJECT & "')&$format=json&$select=KCZ7A54A713DA9CF850D77A13")), #"Converted to Table" = Table.FromRecords({Source}), #"Expanded d" = Table.ExpandRecordColumn(#"Converted to Table", "d", {"results"}, {"d.results"}), #"Expanded d.results" = Table.ExpandListColumn(#"Expanded d", "d.results"), #"Expanded d.results1" = Table.ExpandRecordColumn(#"Expanded d.results", "d.results", {"__metadata", "KCZ7A54A713DA9CF850D77A13"}, {"d.results.__metadata", "d.results.KCZ7A54A713DA9CF850D77A13"}), #"Expanded d.results.__metadata" = Table.ExpandRecordColumn(#"Expanded d.results1", "d.results.__metadata", {"uri", "type"}, {"d.results.__metadata.uri", "d.results.__metadata.type"}), #"Changed Type" = Table.TransformColumnTypes(#"Expanded d.results.__metadata", {{"d.results.__metadata.uri", type text}, {"d.results.__metadata.type", type text}, {"d.results.KCZ7A54A713DA9CF850D77A13", Int64.Type}}), #"Renamed Columns" = Table.RenameColumns(#"Changed Type", {{"d.results.__metadata.type", "ProjectID"}, {"d.results.KCZ7A54A713DA9CF850D77A713DA9CF850D77A13", "ACount"}}), #"Added Parameter Column" = Table.AddColumn(#"Renamed Columns", "FilterParam", each PAR_SEL_PROJECT) in #"Added Parameter Column"

I have tried to build a semantic model in Power BI Desktop, published it, and then tried to access it in Report Builder, but it didn’t work because I am unable to change the parameter value from Power BI Report Builder.

Could you please suggest how I can enable this, so that the value of KCZ7A54A713DA9CF850D77A13 can be displayed in the report I built in Report Builder?

Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

    Hi RussellBode 

    The key difference is that Power BI Desktop and Paginated Report Builder handle parameters differently.

    In Power BI Desktop, you can pass PAR_SEL_PROJECT directly inside the M query and it will resolve because Desktop supports dynamic parameters in the query editor.But in Paginated Reports, you cannot pass Power Query M parameters directly  Report Builder expects you to define Report Parameters and then link them to your dataset query.

    How to fix it:

    1. Define a Report Parameter

    • In Report Builder, create a report parameter (for example @PAR_SEL_PROJECT).
    • This will allow the user (or the report action) to pass a value.

    2. Modify the Power Query / Data Source in Report Builder

    • In the query designer (or dataset properties), replace your hard-coded parameter reference with the @ syntax that Report Builder understands.

    Example:

    let
   Source = Json.Document(
       Web.Contents(
               "https://my429741.businessbydesign.cloud.sap/sap/byd/odata/ana_businessanalytics_analytic.svcc/RPZ0F… eq '" & @PAR_SEL_PROJECT & "'&$format=json"
       )
   ),
   Converted = Table.FromRecords({Source})
   // expand as before …
in
   Converted

    3. Notice here:

    • In Report Builder you must use the @ParameterName style.
    • The parameter value will be substituted at runtime.

    4. Bind the Parameter

    • Go to Dataset Properties >> Parameters and map your report parameter (@PAR_SEL_PROJECT) to the query parameter you used in the URL.
    • This step makes the report parameter flow into the dataset query.

    5. Deploy and Test

    • When you run the Paginated Report, it will prompt for the project ID (or you can pass it programmatically when embedding).
    • The API call will now correctly use the dynamic value.

6 Replies

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    Hi RussellBode 

    The key difference is that Power BI Desktop and Paginated Report Builder handle parameters differently.

    In Power BI Desktop, you can pass PAR_SEL_PROJECT directly inside the M query and it will resolve because Desktop supports dynamic parameters in the query editor.But in Paginated Reports, you cannot pass Power Query M parameters directly  Report Builder expects you to define Report Parameters and then link them to your dataset query.

    How to fix it:

    1. Define a Report Parameter

    • In Report Builder, create a report parameter (for example @PAR_SEL_PROJECT).
    • This will allow the user (or the report action) to pass a value.

    2. Modify the Power Query / Data Source in Report Builder

    • In the query designer (or dataset properties), replace your hard-coded parameter reference with the @ syntax that Report Builder understands.

    Example:

    let
   Source = Json.Document(
       Web.Contents(
               "https://my429741.businessbydesign.cloud.sap/sap/byd/odata/ana_businessanalytics_analytic.svcc/RPZ0F… eq '" & @PAR_SEL_PROJECT & "'&$format=json"
       )
   ),
   Converted = Table.FromRecords({Source})
   // expand as before …
in
   Converted

    3. Notice here:

    • In Report Builder you must use the @ParameterName style.
    • The parameter value will be substituted at runtime.

    4. Bind the Parameter

    • Go to Dataset Properties >> Parameters and map your report parameter (@PAR_SEL_PROJECT) to the query parameter you used in the URL.
    • This step makes the report parameter flow into the dataset query.

    5. Deploy and Test

    • When you run the Paginated Report, it will prompt for the project ID (or you can pass it programmatically when embedding).
    • The API call will now correctly use the dynamic value.

    • RussellBode's avatar
      RussellBode
      Icon for Helper I rankHelper I
      1 year ago

      Hello  rohit1991  I m getting this error 

       

      Query 

      let
      // Reference the parameter value directly, in the correct Power Query form
      PAR_SEL_PROJECT = par_SEL_PROJECT, // This assumes the parameter is named PAR_SEL_PROJECT

      // Construct the URL dynamically using the parameter value
      url = "https://my429741.businessbydesign.cloud.sap/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ0CF89333C75D1A77ED7826QueryResults?" &
      "$select=CE_PLAN_ST_DAT_01,KCZ7A54A713DA9CF850D77A13&" &
      "$format=json&" &
      "$filter=PAR_SEL_PROJECT eq '" & PAR_SEL_PROJECT & "'",

      // Fetch the data from the URL
      Source = Json.Document(Web.Contents(url)),

      // Convert the JSON response to a table
      Converted = Table.FromList(Source[d], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

      // Expand the JSON data (assumes the response is in a nested structure)
      #"Expanded Data" = Table.ExpandRecordColumn(Converted, "Column1", {"results"}, {"Results"}),

      // Further expand the results if necessary
      #"Expanded Results" = Table.ExpandListColumn(#"Expanded Data", "Results"),
      #"Expanded Final" = Table.ExpandRecordColumn(#"Expanded Results", "Results", {"__metadata", "KCZ7A54A713DA9CF850D77A13", "CE_PLAN_ST_DAT_01"}, {"Metadata", "ACount", "Adate"}),

      // Change column types as needed
      #"Changed Type" = Table.TransformColumnTypes(#"Expanded Final", {
      {"ACount", Int64.Type},
      {"Adate", type text},
      {"Metadata", type text}
      }),

      // Extract Unix Timestamp and convert to DateTime (if applicable)
      #"Extracted Timestamp" = Table.AddColumn(#"Changed Type", "UnixMilliseconds", each Number.FromText(Text.BetweenDelimiters([Adate], "(", ")"))),
      #"Converted Date" = Table.AddColumn(#"Extracted Timestamp", "ConvertedDate", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0, [UnixMilliseconds] / 1000)),

      // Format the Date for better display
      #"Formatted Date" = Table.AddColumn(#"Converted Date", "DateString", each Date.ToText(DateTime.Date([ConvertedDate]), "dd/MM/yyyy"))

      in
      #"Formatted Date"

       

      i m doing anything wrong here?

      it is like checking parameter with in that window if i pass which crteated before with ending 2 it works fine but i can't change realtime value of this.

    • RussellBode's avatar
      RussellBode
      Icon for Helper I rankHelper I
      1 year ago

      Hello  I m getting this error 

       

      Query 

      let
      // Reference the parameter value directly, in the correct Power Query form
      PAR_SEL_PROJECT = par_SEL_PROJECT, // This assumes the parameter is named PAR_SEL_PROJECT

      // Construct the URL dynamically using the parameter value
      url = "https://my429741.businessbydesign.cloud.sap/sap/byd/odata/ana_businessanalytics_analytics.svc/RPZ0CF89333C75D1A77ED7826QueryResults?" &
      "$select=CE_PLAN_ST_DAT_01,KCZ7A54A713DA9CF850D77A13&" &
      "$format=json&" &
      "$filter=PAR_SEL_PROJECT eq '" & PAR_SEL_PROJECT & "'",

      // Fetch the data from the URL
      Source = Json.Document(Web.Contents(url)),

      // Convert the JSON response to a table
      Converted = Table.FromList(Source[d], Splitter.SplitByNothing(), null, null, ExtraValues.Error),

      // Expand the JSON data (assumes the response is in a nested structure)
      #"Expanded Data" = Table.ExpandRecordColumn(Converted, "Column1", {"results"}, {"Results"}),

      // Further expand the results if necessary
      #"Expanded Results" = Table.ExpandListColumn(#"Expanded Data", "Results"),
      #"Expanded Final" = Table.ExpandRecordColumn(#"Expanded Results", "Results", {"__metadata", "KCZ7A54A713DA9CF850D77A13", "CE_PLAN_ST_DAT_01"}, {"Metadata", "ACount", "Adate"}),

      // Change column types as needed
      #"Changed Type" = Table.TransformColumnTypes(#"Expanded Final", {
      {"ACount", Int64.Type},
      {"Adate", type text},
      {"Metadata", type text}
      }),

      // Extract Unix Timestamp and convert to DateTime (if applicable)
      #"Extracted Timestamp" = Table.AddColumn(#"Changed Type", "UnixMilliseconds", each Number.FromText(Text.BetweenDelimiters([Adate], "(", ")"))),
      #"Converted Date" = Table.AddColumn(#"Extracted Timestamp", "ConvertedDate", each #datetime(1970,1,1,0,0,0) + #duration(0,0,0, [UnixMilliseconds] / 1000)),

      // Format the Date for better display
      #"Formatted Date" = Table.AddColumn(#"Converted Date", "DateString", each Date.ToText(DateTime.Date([ConvertedDate]), "dd/MM/yyyy"))

      in
      #"Formatted Date"

       

      i m doing anything wrong here?

      it is like checking parameter with in that window if i pass which crteated before with ending 2 it works fine but i can't change realtime value of this.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi RussellBode ,

    Just checking in to see if you query is resolved and if any responses were helpful.
    Otherwise, feel free to reach out for further assistance.

    Thank you.