Forum Discussion
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.
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 SourceBut 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
Hi Anonymous,
That's exactly what the RLS will do. Please refer to the snapshot below.
Best Regards,
Dale
3 Replies
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 SourceBut 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
Hi Anonymous,
That's exactly what the RLS will do. Please refer to the snapshot below.
Best Regards,
Dale