issue w/getting an excel file stored on SharePoint/OneDrive setup on a schl refresh with credentials

[Me4502]

Good morning all, 

Let me start off with this,I am new to here, but I have been lurking using the community as a resource for a while now. This site is invaluable resource to PBI users to learn or if they get stumped. THANK YOU ALL for all you do.

Sorry if I get some wordage incorrect, writing is not the best… If you have questions on something that wasn’t understandable let me know.

On to my issues. I am using PBI Report Server Version: 2.105.1143.0 64-bit (May 2022). As the title said.  I am having an issue with getting an excel file stored on SharePoint/OneDrive setup on a schedule refresh. I setup a simple dashboard with one data source that gets its data from an excel file that is saved on SharePoint. I have tried setting the connection as a SharePoint folder and also tried setting it up using the web url to the file on the SharePoint server. With the setup using the web url I can pull the data fine using a manual update. If I go back and try to setup a data refresh based on the URL when I test the Data source connection it says that

“Couldn't connect

The report server couldn't connect to the data source using the information you entered. Make sure you've entered the connection string and any credentials correctly.

Hide error details

Log on failed. Ensure the user name and password are correct.”

The log in is correct. I can open up the same file and manually refresh, I just can not get the Credentials to connect on the Data Source tab so I can then setup a scheduled Refresh.

Help!!! I have tried all the credentials on the data source tab, I have tried all the credentials on the data source tab Anonymous, BASIC And Windows Auth,. All give the above error. I have also set the file as a file or the web url same error.  

I just need to be able to pull a excel file or a CVS file that will be auto saved onto the companies SharePoint site or really anywhere it will take a auto refresh. We have tried the personal gateways and pulling from local computer or Virt system, but the company has it set to log out after X time so the update using the Person gateway fails.

This is the sample data that is stored on the SharePoint in a xlsx or CSV file. And some of the setups. Not sure what all you will ask for but if you need something let me know.

[d_gosbell · Accepted solution]

Because you mention OneDrive I assume you are also referring to Sharepoint Online. If this is the case then unfortunately this is not a supported data source for scheduled refresh. Search for "Sharepoint Online" in this page Power BI report data sources in Power BI Report Server - Power BI | Microsoft Learn 

Your only real option is to use a supported data source. For files this probably means storing this file in a network share. Or maybe writing some script to pull the file contents into an on-prem SQL Server or something like that. 

Or the other option if all the data is in Sharepoint Online is to host your pbix file in a cloud based workspace instead of using the on-prem Report Server. 

[Tom_Y]

Hi, do you mean that I can't refresh my dataset and report on Power BI services IF all my data comes from an excel file in Sharepoint (Organization)? My case is like this.

Using Power BI desktop and using "web, import" to call excel files on sharepoint to do all the visuals. Can refresh smoothly.

Once I "publish" it to Power BI Services, the credentials fails (I tried all kinds of credentials and make sure login/password are correct) and I can't refresh on Power BI services.

The current method is I refresh on desktop everytime and then publish it again... not really good for collaboration with colleagues that they are working on the Power BI Services.

Below is the error message when I refresh:

Data source errorThe credentials provided for the Web source are invalid. (Source at xxxxx

The exception was raised by the IDbCommand interface. 

I have spent 10 hours fixing this but in vein. Thanks very much in advance.

[d_gosbell]

---

@Tom_Y wrote:

Hi, do you mean that I can't refresh my dataset and report on Power BI services IF all my data comes from an excel file in Sharepoint (Organization)?

---

No - your scenario should work. This forum is dedicated to the ON-PREM Report Server product which has different limitations to the cloud service.

I would not use the web import for data on sharepoint as I don't believe that will allow the OAuth authentication that Sharepoint requires. Instead I would suggest trying one of the Sharepoint connectors. If you have more questions there is a forum dedicated to the cloud service here Service - Microsoft Fabric Community 

[mjbernier]

Sorry to be the bearer of bad news, but SharePoint Online and OneDrive for Business are not supported as data sources for data refresh for reports published on a Power BI Report Server (believe me, I really, really wish Microsoft would add support for it). They are, however, supported on Power BI Desktop and the Power BI cloud service.

To use SharePoint Online data with the report server, you will first need to extract the information from SharePoint or OneDrive and store it elsewhere like a SQL database or an Excel workbook sitting on a conventional file server. We had to do this for a client who stores their data in SharePoint but wants to publish their reports to PBIRS; we wrote almost 40 ETL tasks to pull each individual list and drop them into tables in a SQL database.