How can open the report dynamically in power bi service

[sonu123456]

I have 25 databases and schema are same. I have created report by using one database. I want dynamically change the database according to users related database in power bi service. Ex. User 1 have user1 database, user2 have user2 database, etc. when user1 open the report he can see user1 database data and user2 open the report he can see user2 database data. Similar for others. Users will open workspace or report link,it will work dynamically/automatically. Is this possible in power bi. If possible please suggest me.

[lbendlin · Accepted solution]

You can make this work, but only if 

- you use Direct Query

- all databases are on the same server

- the gateway connection user has access to all databases

- the table structure matches exactly between all databases

- you use Value.NativeQuery with folding enforced

- you force users to select a single database

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

[Anand24 · Accepted solution]

@sonu123456 ,
Create a landing page with RLS applied with respect to the database-username combination. Use blank buttons in-front of the card values which would navigate to the respective reports.

By doing this, users will be able to view only buttons to those reports for whose database they have access.

Long work-around but might work.

[SaiTejaTalasila]

Hi,

You can check deployment pipelines(you need premium workspaces)

https://learn.microsoft.com/en-us/fabric/cicd/deployment-pipelines/get-started-with-deployment-pipel...

Or you can check RLS(append all database tables to single table and apply RLS)

https://learn.microsoft.com/en-us/fabric/security/service-admin-row-level-security

[lbendlin · Accepted solution]

You can make this work, but only if 

- you use Direct Query

- all databases are on the same server

- the gateway connection user has access to all databases

- the table structure matches exactly between all databases

- you use Value.NativeQuery with folding enforced

- you force users to select a single database

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

[sonu123456]

Client requirement is that if user 1 opens the report then he can see only user 1's database data, if user 2 opens the report then he can see only user 2's database data, this is same for all 25 users' database data. In one workspace he wants to change database data dynamically while users are using/opening the report.

Client main requirements is power bi report run on a browser on the local workstation and connect to the customer's local SQL database automatically.

He want to change 25 database data dynamically as per the report users. All users have own database.

[lbendlin]

I already described your only option.