I don't think I quite understand the exact outcome. The workbook did not have worksheets in it when I opened it up. Should UserRights be applied to both instances of Customers in each data source?
I have serveral users from business point of view.The Sheet1 (datasource) has combined data for all. ACL has customers and the respective usernames. Users can only view data where customer name for that user is equal to customer name in sheet.
See the attached sheet, both have name customer. In acl each is linked with its user name and in Sheet1 (datasource) with its data
UserRights.twbx.zip 34.0 KB
Here's one method:
In the secondary (user rights sheet), create a "Verify Username" calculation that has the fmula:
IF [UserName] == Username() THEN 1 END
Then add a filter to your worksheet on MIN(VerifyUsername) that just filters for non-Null values. This will restrict it to only returning data for the user. I set this up in the attached, using a parameter instead of the Username() function.
UserRights jtd edit.twbx.zip 31.9 KB
there is one limitation to this solution: Some common field like customer in this datasource should be in view.With just filter of that value (customer), it does not work. It gives error:
cannot blend aggregated data from the secondary data source with data on this sheet.Because there are no linking fields in this view
You can put the Customer Field (that has to be on the view) on the "Level of Detail" shelf and it also works. What is still not working is when the user should have access to ALL Customers.