In SharePoint I have two lists. The first contains data where each row has row level user/group security permissions defined. The second list has a lookup to the first list in it – creating a one to many relationship between list 1 and 2. The second table has a view which filters based on the lookup not being blank. This then shows the user only the rows that they have permission to view in list 2.
I would like to create some reporting in excel based on the values in the second list. The reporting needs to be viewed by different users and only show data that they have permission to view. Can this be achieved using one excel spreadsheet? i.e. When each different user opens the spreadsheet or refreshes the data only their data is imported from excel, I assume using odata? Or would a copy of the workbook need to be created for each group of users? If so, where would the filtering be defined? Would the odata query need a filter? Or can you make it pull data from a List View instead of the list directly?
The environment is SharePoint 2013 on Office 365. ADFS is configured for single sign on between the domain and office 365.
