User Passing Parameters to a Report

[xChristine7714]

I'm trying to build a report where the table containing the data is supplied by the user.

The use case is that, a user will upload a delimited file into a UI. The UI ingests the file and places the data into its own SQL database table. It then offers up a link to a PowerBI report which displays statistics related to the data in the file. Every time a file is uploaded, it's assigned a GUID and a new table is created wtih that GUID.

So, User1 might upload a file into dbo.myTable_3c1c029e06fa4083a69f8e9e3a671b8e. User2 might then upload a file into dbo.myTable_f3c2a3f7c144410b9a059ef242de9a1e. I need to create a Power BI report that can pull from either table.

The Approach I Used: I created a Power BI report with a parameter called "myTable." The report's query is tied to that parameter:

let
    Source = Sql.Database("myserver.blahblah.whatever", "MyDatabase", [Query="select * from " & MyTable])
in
    Source

So my question is, how can the user pass the MyTable parameter into the report so that the data is pulled from the correct table? I was assuming I could just give the user a link like mysite.powerbi.com/........&myTable=myTablef3c2a3f7c144410b9a059ef242de9a1e, which contains a querystring parameter that specifies the name of the table from which to get the data.

Unfortunately this isn't working. The report just ignores that querystring parameter... both in cloud and on-prem environments. So, my question is "what am I doing wrong?" And, if my approach isn't supported, what's the best way to achieve this goal? Surely there is a way to pass in report parameters...

[xChristine7714]

I'm trying to build a report where the table containing the data is supplied by the user.

The use case is that, a user will upload a delimited file into a UI. The UI ingests the file and places the data into its own SQL database table. It then offers up a link to a PowerBI report which displays statistics related to the data in the file. Every time a file is uploaded, it's assigned a GUID and a new table is created wtih that GUID.

So, User1 might upload a file into dbo.myTable_3c1c029e06fa4083a69f8e9e3a671b8e. User2 might then upload a file into dbo.myTable_f3c2a3f7c144410b9a059ef242de9a1e. I need to create a Power BI report that can pull from either table.

The Approach I Used: I created a Power BI report with a parameter called "myTable." The report's query is tied to that parameter:

let
    Source = Sql.Database("myserver.blahblah.whatever", "MyDatabase", [Query="select * from " & MyTable])
in
    Source

So my question is, how can the user pass the MyTable parameter into the report so that the data is pulled from the correct table? I was assuming I could just give the user a link like mysite.powerbi.com/........&myTable=myTablef3c2a3f7c144410b9a059ef242de9a1e, which contains a querystring parameter that specifies the name of the table from which to get the data.

Unfortunately this isn't working. The report just ignores that querystring parameter... both in cloud and on-prem environments. So, my question is "what am I doing wrong?" And, if my approach isn't supported, what's the best way to achieve this goal? Surely there is a way to pass in report parameters...

[mwegener]

Hi @xChristine7714 ,

maybe you could create a Power BI template app for this.

https://docs.microsoft.com/en-us/power-bi/service-template-apps-create

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[xChristine7714]

Thanks for responding. I'm not sure how a template app will help resolve my scenario tho...

Is it possible to use an API call or some script to copy a report/data set with a new parameter value?

[mwegener]

Hi @xChristine7714 ,

has your question been answered?

If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[mwegener · Accepted solution]

Hi @xChristine7714 .

Yes, you can use the API.

Look at this:

https://powerbi.microsoft.com/en-us/blog/duplicate-workspaces-using-the-power-bi-rest-apis-a-step-by...

https://docs.microsoft.com/en-us/rest/api/power-bi/reports/clonereportingroup

https://docs.microsoft.com/en-us/rest/api/power-bi/datasets/updateparametersingroup

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

[littlemojopuppy]

An alternative could be load all possible data and use row level security to present certain data to a given user....