Forum Discussion

PetrarteP's avatar
PetrarteP
Frequent Visitor
5 years ago

Object level security - Paginated Report (missing column)

Hi,

 

we implemented Object level security on PowerBI dataset (February release) and it is working perfectly in PowerBI service and Analyze in Excel. Users can see only columns and tables they are allowed to. I am working on creating Paginated report on top of that dataset and request is to create huge report that can be consumed by anyone no matter if they are under Role that has no access to some column or not, only request is to have BLANK for the column that is for them under OLS. Right now, that paginated report when opened by user under Role that has no access to some column is throwing an error (missing column) which is expected. I tried to use in DAX IFERROR() function but same error they are getting. Is there a way to catch error in DAX and for those missing columns user can't see put BLANK()? I tried with DMV (data management views) to get the role user is in but I don't know how to create conditional EVALUATE SUMMARIZECOLUMNS statement in DAX to place BLANK() for missing columns. 

 

Thanks for help and ideas.

Petra

 

Need Help

9 Replies

    • PetrarteP's avatar
      PetrarteP
      Frequent Visitor
      5 years ago

      Hi ,

       

      I believe this is expected as it was clear from documentation that column/table secured by OLS for users will not be visible by any tool, XMLA, nothing as for them metadata of that field simple does not exists. When they connect to the published PowerBI dataset with Powerbi Desktop even in the Model View they don't see them.  It is not like it is hidden, it is not there for those users and I am ok with that, this is what OLS serves for. I hope to have some way of catching this missing column error and replacing it with the blank.

      Best regards,

      Petra 

       

  • AbhiSSRS's avatar
    AbhiSSRS
    Solution Sage
    5 years ago

    Hi Petra! 

     

    The missing column errors in Paginated are tricky and need to be handled by custom code which you would need to put in XML. You would need to put a check on column in your Dataset field level to overcome this. 

    Please refer to my article below on how to add custom code. 

    https://www.linkedin.com/pulse/power-bi-paginated-reports-custom-code-vb-abhinav-singh/

    The code that youw ill need here is below. Please call this with your field that may go missing to catch the error on runtime!

    public function GetValue(ByVal dataElement as Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field) as String


    If dataElement.IsMissing Then
    Return " "
    Else
    Return dataElement.value
    End If

    End Function

     

    Please let me know if you face issues implementing code.

    Thanks!
    Abhinav 

     

    Please mark as solution if this helps! 

    • PetrarteP's avatar
      PetrarteP
      Frequent Visitor
      5 years ago

      Hi Abhinav,

       

      thanks for the code, this could be very helpful workaround and I'll try it. My only concern is that Roles and OLS in PowerBI Dataset could be changed, so tomorrow some other field could be secured for some Role and I have no way of identifying it dynamically in the paginated report and prevent future crashes, unless I call this function for basically any field I am using in the report. That is why I was hoping that I can handle error on the DAX side somehow when fetching measure that for some users will not even exist.

      I’ll for sure try this approach end let you know if I had any issues.

      Thanks!

      Petra

      • AbhiSSRS's avatar
        AbhiSSRS
        Solution Sage
        5 years ago

        Sure Petra! 

         

        And you may not bother about any perf impacts as I have widely tested this on missing fields with almost all having to use the code check. This worked even when the datset result goes missing altogether with no columns returned.

         

        Thanks!