In a Report Builder 3 report that pulls data into its data sources from various sub site lists the report fails to run unless I give every user who needs to run the report access to the sub site lists. I do not want to give all users read access to the data but I do want the report to aggregate that data.
The report has a SharePoint list as its Data Source when I choose to use the "Use this user name and password" and enter an account with access to the SharePoint list the following error is always returned:
"The give key was not present in the dictionary."
When I choose the option to use "Use current Windows user" option the report will only run if the current user has read access to the data source list. In the case of a sub site (web) we do not want to give every user read access on the site but we do want to run the report that pulls data from a list in those subsites - so we want to run the report under an account that does have access.
Currently it does not matter what account we try that does have the correct access to the lists in those sub sites.
This seems to be a bug because if I put in an account that does not exists it gets a logon failure. So the report is attempting to authenticate but failing with above error.
There does not currently seem to be a way to run reports that connect to SharePoint lists as their datasources as any account other than the currently logged in user.
Is there a way to run the report without giving read access to all users to the sub sites?