Data masking in paginated reports

[vpawan]

Hi,

I have build a paginated report with excel source which contains below data. is there any way to apply data masking based on user login.

User Group	User Email	Customer name	Salary	Region
ADGroup1	abc@gmail.com	John	13490	UK
ADGroup1	xyz@gmail.com	Venus	75734	UK
ADGroup2	lyz@gmail.com	keeny	9876	US
ADGroup2	jkm@gmail.com	Ian	3567	US

when any user from ADGroup1 login to the report they should see the customer name and salary related original records,

when any user from ADGroup2 login to the report they should see the "confidential" word on both customer name and salary instead of original records.

ADGroup1 output:

User Group	User Email	Customer name	Salary	Region
ADGroup1	abc@gmail.com	John	13490	UK
ADGroup1	xyz@gmail.com	Venus	75734	UK

ADGroup2 output:

User Group	User Email	Customer name	Salary	Region
ADGroup2	lyz@gmail.com	confidential	confidential	US
ADGroup2	jkm@gmail.com	confidential	confidential	US

Please help me to achive this directly in paginated report instead of power bi dataset.

[Anonymous · Accepted solution]

Hi,@vpawan .I am glad to help you.

According to your description, you want to realize to set RLS in Pagination report, according to the user ID who access the report, so that it can only see the dataset of the group in which your userID is located.
You mentioned that your data source is excel type, after my test, excel type data source can set RLS, but due to the difference of data source type, it can't realize sql query; and for the way of creating parameter, the parameter of Pagination report can't directly use the field data in the field (table data), so for excel type (including the data source of enter data) data source, the complexity of its RLS settings is drastically limited, (Pagination report can choose from very few conditional filter function)
After testing, I can only realize the filtering based on user's ID column (UserEmail), but can't realize the value of UserEmail, and then get the corresponding GroupName based on the value of the current Email, and finally filter the corresponding data through the GroupName (such as your example), the system doesn't support excel type. The system does not support the excel type of filter and then use other complex parameters.
The limitation in Power BI Report Builder:

And for Pagination report set RLS, it is more recommended that you make the data source in excel into SQL Server (or other relational databases) in the table, when the type of data source for the database, Pagination report to set the complexity of the RLS can be greatly enhanced, because of the support of SQL query, you can customize the conditions of the query to the needs of custom data display conditions , which is very simple and convenient , also makes the data source easy to manage .

Here is my test:
Here is my data source table.

Set a parameter to get the user account currently accessing the Pagination report (using the UserID method)
ReportParmater1:

2. Create query filters for the data set

Create a filtering query to filter out groups based on the account ID of the user accessing the report.
(Nest a subquery within the parent query, which returns only the group data that meets the conditions)

The query code:

SELECT
  Dynamic_RLS.ID,
  Dynamic_RLS.username,
  Dynamic_RLS.[domain],
  Dynamic_RLS.[group]
FROM
  Dynamic_RLS
WHERE 
  Dynamic_RLS.[group] in
(
SELECT

  Dynamic_RLS.[group]
FROM
  Dynamic_RLS
WHERE 
  Dynamic_RLS.username = @ReportParameter1
)

Finally, publish it to the service and manage gateway connections.

The final RLS presentation result is as follows:

You can see that only a few records with group = 1 are displayed, and the RLS settings are successful.

Here's the official documentation, hope it helps (the official documentation also uses SQL type data source as a case study, as it supports filter query, which allows for more complex RLS rules)
URL:
Set up row-level security in Power BI paginated reports - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[Anonymous · Accepted solution]

Hi,@vpawan .I am glad to help you.

According to your description, you want to realize to set RLS in Pagination report, according to the user ID who access the report, so that it can only see the dataset of the group in which your userID is located.
You mentioned that your data source is excel type, after my test, excel type data source can set RLS, but due to the difference of data source type, it can't realize sql query; and for the way of creating parameter, the parameter of Pagination report can't directly use the field data in the field (table data), so for excel type (including the data source of enter data) data source, the complexity of its RLS settings is drastically limited, (Pagination report can choose from very few conditional filter function)
After testing, I can only realize the filtering based on user's ID column (UserEmail), but can't realize the value of UserEmail, and then get the corresponding GroupName based on the value of the current Email, and finally filter the corresponding data through the GroupName (such as your example), the system doesn't support excel type. The system does not support the excel type of filter and then use other complex parameters.
The limitation in Power BI Report Builder:

And for Pagination report set RLS, it is more recommended that you make the data source in excel into SQL Server (or other relational databases) in the table, when the type of data source for the database, Pagination report to set the complexity of the RLS can be greatly enhanced, because of the support of SQL query, you can customize the conditions of the query to the needs of custom data display conditions , which is very simple and convenient , also makes the data source easy to manage .

Here is my test:
Here is my data source table.

Set a parameter to get the user account currently accessing the Pagination report (using the UserID method)
ReportParmater1:

2. Create query filters for the data set

Create a filtering query to filter out groups based on the account ID of the user accessing the report.
(Nest a subquery within the parent query, which returns only the group data that meets the conditions)

The query code:

SELECT
  Dynamic_RLS.ID,
  Dynamic_RLS.username,
  Dynamic_RLS.[domain],
  Dynamic_RLS.[group]
FROM
  Dynamic_RLS
WHERE 
  Dynamic_RLS.[group] in
(
SELECT

  Dynamic_RLS.[group]
FROM
  Dynamic_RLS
WHERE 
  Dynamic_RLS.username = @ReportParameter1
)

Finally, publish it to the service and manage gateway connections.

The final RLS presentation result is as follows:

You can see that only a few records with group = 1 are displayed, and the RLS settings are successful.

Here's the official documentation, hope it helps (the official documentation also uses SQL type data source as a case study, as it supports filter query, which allows for more complex RLS rules)
URL:
Set up row-level security in Power BI paginated reports - Power BI | Microsoft Learn

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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

[vpawan]

Hi @Anonymous 

Thanks for your reply. i have tried this solution to test i have pubslidhed this report in my workspace but getting below error message. Could you please help me to fix

[Anonymous · Accepted solution]

Hi,@vpawan .Thank you for your reply.

It looks like you have successfully implemented configuring RLS for Pagination reports, can I ask if RLS is working correctly when you save the Pagination report to the Report Server local server. If your original post problem has been solved, I suggest you can open a new help thread for your current new problem.
For the description and screenshot of your current new issue, it appears that you are experiencing the issue while publishing a Pagination report with RLS configured to Power BI Service in the cloud. The screenshot shows an issue with your gateway and credentials, please make sure your credentials and gateway are set up correctly and working properly.

You can refer to my test below:
Note that since you have configured RLS for the Pagination report, please make sure that your report user has the right to access the data source for this Pagination report.
publish the Pagination report to the Power BI Service

Select the right workspace

The following error screenshot is normal, at this point the Pagination report can not be rendered properly, you need to reconfigure the credentials information for the report

Configure the credential information:
Select Pagination Reports>Manage>Configure the appropriate data source gateway connection (credential information is set correctly)

Re-open the report, you can see that the report is displayed normally and the RLS is working normally

I would more than recommend that you start a whole new thread to get more help and good luck with your problem.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

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