Report building for multiple clients

[VenkatC]

I have a database containing client details and want to create a report for each client. Although the reports will be similar, I aim to implement a single report that can be used for multiple clients. Which method is better: 1) filtering a report using query string parameters in the URL, or 2) dynamically binding datasets to a paginated report? Are there any other methods you would recommend?"

[v-jianpeng-msft]

Hi, @VenkatC 

For method 1, if you filter by URL, the report will query all the data corresponding to the fields in your data set, and then filter by the parameters of the URL, and then present the results.
For method 2, the report will query the data source for the data you specify according to the parameter you set, and then present the results.
For query speed and efficiency, method 2 is better. For portability of operation, method 1 is more suitable because we only need to specify the corresponding fields and values ​​in the URL to complete the filtering.
Method 2 requires us to set dynamic query parameters when creating the report.
For the above two methods, you can refer to the following articles:

Method 1

URL access - SQL Server Reporting Services (SSRS) | Microsoft Learn

Method 2:

Associate a Query Parameter with a Report Parameter (Report Builder) - SQL Server Reporting Services...

If you are using SQL Server, you can refer to the following article to learn how to reference parameters in a query:

Tutorial: Add parameters to your report (Report Builder) - SQL Server Reporting Services (SSRS) | Mi...

How to Create SSRS Report with Dynamic Query and Multi-Value Parameters | IT Nota

If you use AS or semantic model, you can refer to the following case:

Filtering with a sub filter with multiple value se... - Microsoft Fabric Community

In this case, I have detailed testing how to reference a parameter to another dataset. 

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VenkatC]

"Can Method 2 (Dynamic Query Parameters) be applied to Power BI reports, or is it specific to SQL Server Reporting Services (SSRS)? If it can be used in Power BI, could you explain how to set up dynamic query parameters for Power BI reports?"

[v-jianpeng-msft]

Hi, @VenkatC 

Thank you for your reply. Regarding your question, first of all, the above method is applicable to paginated reports, whether in SSRS or PBIRS. When you build a paginated report, you can use the above method to build a dynamic parameter query.
Second question, if you need to use dynamic parameter queries in Power BI reports, you need to ensure that your connection mode is DirectQuery and publish it to Power BI Service instead of Power BI Report Server, and then refer to the following article for practice:

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

Dynamic Data Source in Power BI / Blogs / Perficient

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VenkatC]

Thank you for your previous response. I have a question regarding dynamic parameter queries.

I’m looking to build a single Power BI report for multiple clients who all have the same database structure. Is it possible to use dynamic parameter queries in Power BI reports to achieve this, or would you recommend another approach? My goal is to ensure that the report can be filtered dynamically based on the client, while keeping each client’s data separate and secure.

What would be the best approach for this scenario?

Thank you!

[VenkatC]

Additionally, I want to be able to filter the report based on the selected client, so that when a specific client is selected, the report will display data for that client only. We plan to embed this report into our website for client access.

What would be the best approach to ensure that the data remains secure and the filtering works efficiently?

[v-jianpeng-msft · Accepted solution]

Hi, @VenkatC 

Thank you for your reply. You can build a dynamic query for your Power BI report according to the following demonstration, bind it to a specific table, and then set RLS for this table so that users can only access the data they have permission to access.

Pass parameter to SQL Queries statement using Power BI | Microsoft Learn

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

In your case, you should set an RLS for the client table to ensure that only users with corresponding permissions can access the corresponding rows, and then pass it as a parameter in Power Query, which will dynamically query the data source.

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.