Latest Enterprise DNA Initiatives


Role Security - Paginated Report - Con't

Hello all.
I know there is a previous thread asked about this and the reply was to look for a Role Level Security in a PBI dataset that you could use for your Paginated report. I do not have one.
I am now asking if anyone has found a way within the Paginated report in the SQL to use the USERPRINCIPALNAME value and tying it into your Active Directory?

My scenario is to only allow a user to see their Sales Orders at their Plant by their Active Directory group and the Sales Order Plant value = this Group name.

Also using the USERPRINCIPALNAME to only show certain users only their Sales Order by their name = the Sales Rep Name in the Sales Order table.

Any help and/or examples would be great.
Thank you
Jim

Hi @jrubino77, I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your current work-in-progress PBIX file
  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

Hi @jrubino77, we’ve noticed that no response has been received from you since May 6th. We are waiting for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details. In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

Hello,

This is not a PBIX file. This is an RDL file for a paginated report. I already know how to do the Role Level security in a PBIX report. It is the RDL report that is different than a PBIX report when it comes to the Role Level security.
I need to have this RDL limit the parameters to what the user can only select (Plant value(s), SalesPerson name (their name) ).
Some how I am sure that using the UserPrincipalName there should be a way to use that in the SQL statement and the the datasets list of values. I cannot seem to figure it out. I am not very strong in SQL.
Thank you for any additional help.
Jim

Hi @jrubino77, while waiting for the response from our members and experts, have you checked out the course by @hafizsultan on SQL? You might find some relevant tutorials there.

The course can be accessed through this link: Enterprise DNA

Have you tried using the UserID field?

Understanding paginated report data in Power BI Report Builder - Power BI | Microsoft Docs

SSRS + get current logged in user to be used as parameter (microsoft.com)

Hi @jrubino77

You can configure the RLS in your model, so use DAX or MDX to build your paginated report (do not use SQL).

1 Like

Hello,

Thank you for your reply and suggestion. With me being relatively new to paginated reporting, doing mostly basic reports with totals - it would be a great help if I had an example of what could be done with the userid and parameters to limit what the user could see. Would either of you have some example that I could see and use for me to start with. They say a picture is worth a 1,000 words.

Thanks again,
Jim

HI @jrubino77

1.- Setup Role on Power BI Desktop and publish to Power BI Services

2.- Assigned user to the role - Power BI Service

3.-Added user to the workspace or App with role viewer

4.- Create your paginated report and publish to premium workspace and test with user with viewer role

Note. This is not the best practices to handler RLS is only a sample for you.

1 Like

Hi @jrubino77, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

I have not received what I am needing. The last reply with the RLS is for the Power BI report server designer. I know how to do that and it all works fine for that type of report. I am not using a Report Server Power BI report only a paginated report. I will get together an example of my paginated report to show what it is that I am needing to do, post it here for you all to see. Give me a couple days.
Thank you,
Jim

Hi everyone,

Here is my scenario: I have a purchasing report that need to be limited my the plant that a user can see. I have Active Directory Groups setup for Purchasing called, Purch_KC, Purch_DM, Purch_FW. These are for users in the Purchasing Dept for the plants - Kansas City, Des Moines and Fort Worth respectively.
In the screen below Amanda Spotswood belongs to the Group Purch_DM, so when she runs this report she should only see Des Moines plant in the drop down list. This is what I have having trouble with doing in the SQL.

Thank you
Jim

Hi @jrubino77! We noticed that your inquiry was left unsolved for quite some time now.

Looks like your inquiry was out of the experts and users’ bounds.

While our users and experts do as much as reasonable to help you with your inquiries, not all concerns can be attended to especially if there are some learnings to be done.

Please also note that course series on Paginated Reports will be available soon in our On-Demand Platform. With the coming of the course by Sue Bayes, we hope that we learning on how to solve this issue will be easier for our members.

Thank you!