Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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

    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

  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi Anonymous,

     

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

    Security_and_Stored_Procedures_Parameters2

     

     

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

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

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      Hi Anonymous,

       

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

      Security_and_Stored_Procedures_Parameters2

       

       

      Best Regards,

      Dale