topic Re: Running the schedule updates to get data from SharePoint lists in Report Server

Running the schedule updates to get data from SharePoint lists

Gilmar-Neto — Tue, 17 May 2022 20:00:40 GMT

Hello everyone,

I'm trying to find the best solution to an unfortunate situation here:

We created a big report with BI Desktop using SharePoint lists as data source, everything is working fine and looking good so far.

Now we had to put it into production on our Report Server (because scheduled updates are exactly what we need) and for some strange reason it doesn't support the web version SharePoint lists (even though both products are from Microsoft)... How is this designed to be used in a situation like this?

If I try to connect through the Report Server I can only authenticate as "Anonymous" or with "Windows Login", but as we are dealing with data that only a group of people in the company have access to we need to be able to log in using some credential such as an e-mail that has permission to view this information.

Thanks.

Re: Running the schedule updates to get data from SharePoint lists

d_gosbell — Tue, 17 May 2022 22:43:26 GMT

So I hate to be the bearer of bad news, but Sharepoint Online is listed here as not supported for scheduled refresh. https://docs.microsoft.com/en-us/power-bi/report-server/data-sources

You options are to either:

manually refresh the report
build another process which can pull the data to another location (like a local SQL database) which is supported for scheduled refresh
move the list to on-prem Sharepoint (if you have it)
move your report to the Power BI Service and use a Data Gateway if you also need data from on-prem

Re: Running the schedule updates to get data from SharePoint lists

Gilmar-Neto — Wed, 18 May 2022 12:39:55 GMT

Thanks d_gosbell for the answer.

Manually updating the report like "open the desktop version and click update" would probably not be enough, we're looking for something the user can open this report online and it just works.

I was wondering: would it be possible to use Power Automate to export the SharePoint lists as an Excel file (as .xls) and store it in OneDrive and then use Report Server to update the reports using those new files in OneDrive? Is there something I'm missing that might make this not work?

I can change the connections in BI to the new files without much problem, right?

Thanks!

Re: Running the schedule updates to get data from SharePoint lists

d_gosbell — Wed, 18 May 2022 22:26:44 GMT

Gilmar-Neto wrote:

I was wondering: would it be possible to use Power Automate to export the SharePoint lists as an Excel file (as .xls) and store it in OneDrive and then use Report Server to update the reports using those new files in OneDrive? Is there something I'm missing that might make this not work?

Nope. It's not so much the data location that is the issue, its the AzureAD/OAuth2 authentication that is the problem. OneDrive uses the same authentication as Sharepoint Online so its not supported for scheduled refresh either.