Read value from Report URL

[Anonymous]

Hi guys, 

I've done a lot of research but could not find any conclusive data about this. 

We are working on deciding a BI tool for one of our customers. The model is basically a Web App that resides on-premise, which will need to embed a set of Power BI reports, which will be connected to a HANA database in the cloud (SCP). 

This is all achievable, except for one important thing, which is communication between the web app and the embedded report. 

The main challenge is that the HANA models (based on calculation views), they all use HANA input parameters in the lowest level joins to filter data from the bottom. These input parameters are nothing but prompts that you pass to the database when you call a calculation view, and the populated value is used to restrict the SQL clause. 

I know we can call a Power BI report in an iFrame and pass filters to it in the URL, but this only restricts the data set after import, which makes it unusable to us. We would be returning ALL data everytime a user runs the app (hundreds of millions of records). 

Ideally, these input parameters should be part of the embedded report URL so we can give them a value from the web app, but this is not the case. I was thinking that since I'm pretty much limited to URL filters, which consist of table/field value. I could create a dummy table and pass a filter to it, and in theory I should be able to capture those filters from the URL, and assign them to my HANA input paremeters from the query advanced editor. 

For instance, this is the generated syntax for one of my calculation views, regarding input parameters:

let
.......
{
{Cube.ApplyParameter, "IP_TENANT_ID", {{"58"}}},
{Cube.ApplyParameter, "IP_ANHO", {{"2016"}}},
{Cube.ApplyParameter, "IP_MES", {{"06"}}},
{Cube.ApplyParameter, "IP_TIPO_MAT", {{"CERV"}}},
.....
in
#.....

Those starting with "IP" are input parameters. All I need is to capture a value from the URL and assign it dynamically to each of them, so, for instance, IP_TENANT_ID will need to use a variable read from the report URL instead of a hardcoded value.

I also thought of using Power BI parameters but although these can be changed during runtime via DAX, they cannot be parametrized in the URL either.

Any ideas?

Thanks a lot in advance.

[v-shex-msft]

Hi @Anonymous ,

>> All I need is to capture a value from the URL and assign it dynamically to each of them, so, for instance, IP_TENANT_ID will need to use a variable read from the report URL instead of a hardcoded value.

You can try to custom function to parametrize your parameter list as query parameters then you can modify query parameter to achieve dynamic changes.

Regards,

Xiaoxin Sheng

[Anonymous]

Hi v-shex, thanks a lot for your reply.

Parameter list would not work, since those values should come dynamically from the external Web site.

Real life example: 

1. The user logs in the website where a Power BI dashboard is embedded in an iFrame. 

2. He interacts with the web site, making certain selections, and activates the iFrame. 

3. The power BI report must receive those selections via URL, and capture them. 

4. Those selections are used to provide values to the HANA input parameters at query level, so these selections can be pushed down to HANA (where the input parameters are used to filter the data at lowest level). 

So two questions remain:

1. How to pass parameters from the web to power BI.

2. How to capture those values so they can be passed to the query editor. 

I already tried passing filters in the URL, and these can be captured in a measure, but these measure is not visible at query editor level. There is no way to know the active filters in the report at query level. 

[v-shex-msft · Accepted solution]

HI @Anonymous ,

You can use following api to pass query parameters:

Power BI Embedded Query Parameters API

Regards,

Xiaoxin Sheng

[Anonymous]

Basic question by the way, is it at all possible to use the dashboard generated objects (a measure) in the query advanced editor?