Object level security - Paginated Report (missing column)

[PetrarteP]

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

[AbhiSSRS]

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]

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]

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!

[PetrarteP]

Hi Abhinav,

I am strugling with the code and need help. Field for which I added expresion "=code.GetValue(Fields!Val_Opening_Balance_Amt.Value)"  is rendering to "#error" even for me in Report Builder. Maybe it is because I am putting decimal type field and it is formatted in the matrix as number and not the string. Also I need to Return Nothing and not " ". Can you help to adjust the code? Thank you!

[AbhiSSRS]

Hi Petra! Please pass only the field as the argument is field type 

ByVal dataElement as Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.Field

so the code call has to be  : =code.GetValue(Fields!Val_Opening_Balance_Amt). This works 

[PetrarteP]

Hi Abhinav,

thanks for the info and all the help, now report is working for me as I am in the Role without OLS restrictions but users under different roles are getting same error:

Could not connect to the data source.
"There was an error communicating with Analysis Services. Please verify that the data source is available and your credentials are correct. Query (14, 84) The value for 'Val Stock Net Movement Amt' cannot be determined. Either the column doesn't exist, or there is no current row for this column.".

From the error I see that probably it fails on the PowerBI Dataset as I am querieng it with DAX in Paginated Report. That is why I think I need to do something on the DAX code itself to catch an error. I am greatfull for you answers as this code will be usefull in other scenarios.

Petra

[v-kkf-msft]

Hi @PetrarteP ,

In Power BI, we can only limit data access on row level.

Support for OLS in Power BI is in Preview.

Visualizations referencing a dataset object with OLS configured display the same message as for a deleted or non-existing object.

Because users under OLS roles do not have access to the column in datasource, you cannot use DAX functions such as IFERROR to try to catch errors.

As a workaround, you could use import mode, and you could unpivot your source table to convert column headers into rows. Then you can control users' access to specific columns via applying RLS indirectly. And you can put the converted column into Column group within Matrix visual to make it appear like a "column".

Reference: Unpivoting Sensitive Columns in Power BI to Apply Row-Level Security

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@PetrarteP , if the paginated report is erroring because of object-level security, then it is an issue. Release doc is not clear on whether it will work on a paginated report or not

Check and log an issue at - https://community.powerbi.com/t5/Issues/idb-p/Issues

[PetrarteP]

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