SSRS to Online O365 Sharepoint List - Has anyone done this?

Epicor ERP 10
1

Hello,

Has anyone created a shared data source that connects to an online SharePoint list?

I can’t seem to get it to connect. All the articles I find say, enter the connection string (I did this) and then create a dataset using the datasource (I did this), and then click Edit Query and you should see a list of objects to select from… Instead of seeing a list I get Server was unable to process request. —> Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
(Microsoft Visual Studio).

I am not sure what to do about the credentials, I have tried all the option available to me in report builder. I can’t seem to connect to the datasource.

Anyone doing this right now and having success?

In the end I would like to merge a sharepoint list with some Epicor data.

2

Not sure what you’re actually try to accomplish here.

In O365, the current preferred method to access SharePoint items is through MS Graph, i.e. REST.

3

Mark,

We have a list in our office 365 SharePoint site. Here is an article on it, but I can’t seem to connect to ours SharePoint List Connection Type - SQL Server Reporting Services (SSRS) | Microsoft Learn . Our list is a list of sales order numbers, lines, and pack ID locations in our warehouse. I am trying to connect to the list and join it to OrderDtl in Epicor so that our pick list SSRS label prints out with pack IDs and their location in the warehouse.

This is the error I am getting when trying to open the query editor in my dataset after creating the Data Source:

“Server was unable to process request. —> Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))
(Microsoft Visual Studio).”

Here are the steps I am following:

  1. Create the Data Source
    a.
    sharepoint%20connection
    sharepoint%20connection770×562 30.2 KB

    b.
    datasourcecreds
    datasourcecreds755×546 29.4 KB
  2. Create the Data Set using the Data Source – This is where I am having trouble.
    a. I click the Query Designer and expect to see a list of SharePoint lists from that site, but instead I get an error message.
    i.
    testtest
    testtest786×724 16.5 KB

    b.
    errormessage
    errormessage935×694 21.6 KB

Do you have any idea why I am being denied access? I don’t know SharePoint that well, but I do know that my user has access to that site.

@Mark_Wonsil , how can I make a rest connection and tie that into an SSRS report, do you have any documentation on that process?

Respectfully,

Utah

4

Are you looking to use SharePoint to pull Epicor data using REST and not specifically report data? If so, I can provide you an example. I think the report data would also be possible but have never tried that one.

5

@danbedwards the other way around, I am looking to use SSRS to pull sharepoint data so I can use it in an epicor SSRS report.

6

Ah - I better finish that first cup of coffee next time before responding.

7

Thanks for responding, coffee or not.