Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Url filter on Dataset query parameters

 https://docs.microsoft.com/en-us/power-bi/service-parameters  - Following these steps I can now just return the data I want to a specific user's report. The problem is that we have alot of users and...
parameter
Report Server
URL
ITGeek555's avatar
ITGeek555
Icon for Helper II rankHelper II
7 years ago

I do not know how you have configured the parameter and what data mode is in use (Import/DirectQuery/Live) but there are a couple of ways to effect limiting the data to only what a given user is allowed to see:

  1. Row Level Security is a possible answer (the parameter will not be required).  
  2. If the report uses a direct data source where the data is security trimmed per user then configuring the data source to connect in the user's security context will work (the parameter will not be required).
  3. Creating a SSAS tabular cube configured with row level security will work (and the parameter will not be required). Bonus with this approach is that you can create additional reports against the cube and RLS will be in effect. 
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thank you for the suggestions, I'll have a look at these ! 

 

For now it's DirectQuery but I'll need to make this work for Live connections as well. 

The parameter is set up like this : 

 

And then i want to filter on the Id parameter in the URL http://localhost/Reports/powerbi/my-report?Id=myId

 

I've got custom secturity set up so if someone enters the wrong Id they won't get authenticated. So the only problem with this solution is that the report isn't filtered on Id

  • ITGeek555's avatar
    ITGeek555
    Icon for Helper II rankHelper II
    7 years ago

    For Live Connections you do not need the parameter as SSAS cubes perform the RLS for you if you set it up in the cube and configure the report data source to connect to the cube in the security context of the user running the report.

     

    In terms of the DirectQuery parameter, what is the data source type and how are you using the parameter value?

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Okay I'll need to read up on Cubes and RLS

       

      The parameter is used when querying the DB for data

      i.e. SELECT * FROM data_table where CustomerId = Id

      This query will then query the data_table, which contains data for all customers, and only return data for that CustomerId.