Forum Discussion
Report building for multiple clients
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?"
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.
6 Replies
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:
If you are using SQL Server, you can refer to the following article to learn how to reference parameters in a query:
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.
- "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?"
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.
