Security and Stored Procedures Parameters

[Anonymous]

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.

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

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

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

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

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

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

[Anonymous]

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?

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

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

Best Regards,

Dale