Help needed to pass a dynamic parameter value to a dataset when embedding a report

[StephenL_CA]

Hi, I have a dataset that is the result of a stored procedure accepting an input parameter value.

First, I have no issue to embed the report using JavaScript. But my web application, depending on the case, passes in a different parameter value that should be used by the sp. I am hoping to pass this value to the sp to each time get a different set of data (the schema is the same). I read the Dynamic M query parameters in Power BI Desktop article. But I am confused, in Power BI Desktop, I could not find the "Bind to parameters" in Model View when following the steps to set up my list table.

What do I do wrong? I have Power BI Desktop Version: 2.118.828.0 which should be the latest.

Or do you have other suggestion to achieve my goal, which is to use the dataset that accepts a dynamic parameter value when the report is embedded by the web application?

Thank you in advance for your help.

[Anonymous]

HI @StephenL_CA,

How did you design the report? have you parameterized your stored procedure with query parameters?
You should parameterize the query parameters with your stored procedure at the first.

After you publish and embeded this report, you can use updateParameters and setFilters methods to update and interact with backend query parameters.

Sample code:

// Get a reference to the embedded report
var report = powerbi.embed(reportContainer, config);

// Define the list of parameter values
var parameterValues = [
  {
    name: "Parameter1",
    value: "Value1"
  },
  {
    name: "Parameter2",
    value: "Value2"
  }
];

// Update the parameter values
report.updateParameters(parameterValues)
  .then(function () {
    // Set the filters based on the updated parameter values
    var filters = [
      {
        $schema: "http://powerbi.com/product/schema#advanced",
        target: {
          table: "Table1",
          column: "Column1"
        },
        filterType: 1,
        operator: "In",
        values: [parameterValues[0].value]
      },
      {
        $schema: "http://powerbi.com/product/schema#advanced",
        target: {
          table: "Table2",
          column: "Column2"
        },
        filterType: 1,
        operator: "In",
        values: [parameterValues[1].value]
      }
    ];

    // Set the filters on the report
    report.setFilters(filters);
  });

Power BI embedded analytics Client APIs | Microsoft Learn

Regards,

Xiaoxin Sheng

[StephenL_CA]

Thank you for the reply.

Now I have change mind. I use .Net SDK to do what I am trying to accomplish: update the parameter value then get the embedded report. But then I get "This operation is only supported for the dataset owner".

Some background info: my web app is written in C# MVC. The app is registered through Azure AD. It is a service principal , App Owns Data approach. I have the tenant id, appId, App secret. In the C# code I have the pbiClient instantiated with the proper credentials, proof is that I have no issue embedding the report to my web app. The dataset that I am trying to update the parameter, the service principal has all permissions in Power BI service on it though someone else designed the report on Power BI Desktop, thus the dataset creator or owner is that person, I am not sure if this is relevant. Here is the code snippet.

var updateParametersRequest = new UpdateMashupParametersRequest

{

UpdateDetails = new List<UpdateMashupParameterDetails>

{

new UpdateMashupParameterDetails

{

Name = "PortalFacilityID",

NewValue = pbiModel.ReportParameters.Where(p=>p.Name.Equals("FacilityID", StringComparison.OrdinalIgnoreCase)).Select(p=>p.Value).FirstOrDefault().ToString(), 

}

}

};

pbiClient.Datasets.UpdateParametersInGroup(workspace.Id, pbiReport.DatasetId, updateParametersRequest);

Please, help to solve the error "This operation is only supported for the dataset owner".

PS: in the report design I have properly parametize the sp.

let
facilityId = Text.From(PortalFacilityID),

Source = Sql.Database("sac1v-sqlent-1", "DivrProd",
[
Query="exec dbo.SelectRecoveryTrendDataParameter @PortalFacilityID = ' " & facilityId & " ' "
]
)
in
Source

[StephenL_CA]

Ok, continue on the previous reply from myself. I overcome the error by adding TakeOver()

Also, I added RefreshDatasetsInGroup(), thinking that would refresh the report with new data loaded with the new parameter value. But no, the embedded report still has the old data. And when I go to Power Bi service the dataset I see:

Please, could you tell me what else is missing in my steps?

[pcho]

Hi StephenL_CA, I know this is an old post, did you get a final solution? thank you for the post.

-Peter

[StephenL_CA]

Not really finding a solution, I gave up going down this path.

[Anonymous · Accepted solution]

Hi @StephenL_CA,

The error message seems related to the data source credentials, I think you may need to re-verify them before invoke the API to refresh.

Regards,

Xiaoxin Sheng