cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
raghads Regular Visitor
Regular Visitor

Security and Stored Procedures Parameters

Here is what I'm trying to do:

 

I want to create a report whose data is fetched from a stored procedure. Meaning: I need to pass dynamic parameters to the Stored Procedures. I also want to apply a security filter. Meaning: I want the slicer to be filtered based on the user who is logged in and viewing the report.

 

Example: A Dashboard that displays a company's department employees status. Each director of each department should view his department's data only. Meaning: the slicer should only display the value of the department the user belongs to.

 

I hope this made sense.

2 ACCEPTED SOLUTIONS

Accepted Solutions
Community Support Team
Community Support Team

Re: Security and Stored Procedures Parameters

Hi @raghads,

 

It seems both of the two requirements are aim at filtering data for users. 

Regarding the first one, you can create a parameter and modify the Power Query code like below.

let
    Source = Sql.Database("dale2016", "contosoretaildw", [Query="exec selectFromDimProduct " & PColor])
in
    Source

Security_and_Stored_Procedures_Parameters

But I'm afraid we can't pass the username to it.

Regarding the requirement two, the RLS will be the answer. Please refer to desktop-rls#define-roles-and-rules-within-power-bi-desktop and power-bi/service-admin-rls.

 

Best Regards,

Dale

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

Re: Security and Stored Procedures Parameters

Hi @raghads,

 

That's exactly what the RLS will do. Please refer to the snapshot below.

Security_and_Stored_Procedures_Parameters2

 

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Community Support Team
Community Support Team

Re: Security and Stored Procedures Parameters

Hi @raghads,

 

It seems both of the two requirements are aim at filtering data for users. 

Regarding the first one, you can create a parameter and modify the Power Query code like below.

let
    Source = Sql.Database("dale2016", "contosoretaildw", [Query="exec selectFromDimProduct " & PColor])
in
    Source

Security_and_Stored_Procedures_Parameters

But I'm afraid we can't pass the username to it.

Regarding the requirement two, the RLS will be the answer. Please refer to desktop-rls#define-roles-and-rules-within-power-bi-desktop and power-bi/service-admin-rls.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
raghads Regular Visitor
Regular Visitor

Re: Security and Stored Procedures Parameters

So can I apply RLS on slicers? For example, I have 5 departments: 1, 2, 3, 4, and 5. They are listed inside a slicer. The Director of department 1 cannot see the data concerning the other departments. Meaning: he cannot click on departments 2, 3, 4, or 5 in the slicer. Is this possible to do?

Community Support Team
Community Support Team

Re: Security and Stored Procedures Parameters

Hi @raghads,

 

That's exactly what the RLS will do. Please refer to the snapshot below.

Security_and_Stored_Procedures_Parameters2

 

 

Best Regards,

Dale

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