3

As my title states, I'm looking for some help/resources on how I can pull data from SQL server and display it on SP Online. The added kicker is, we'd like this data to be view-able on mobile devices.

I've read and it appears there are a few options we have.

  1. Use report server. We do have a report server but users must be on local domain to access the reports.
  2. Use excel and create a connection to the data set.
  3. Use BCS and ECT to create a list that displays the data.

What would be the best way to allow users access to the data and also make sure it's accessible on mobile devices? I am not sure what exactly I should be searching for to accomplish this so I'm sorry if this is a double post.

  • Gautam's answer is certainly comprehensive. What sort of data volume are you looking at, what sort of query and what sort of Mobile Device? The mechanics are all there, but what sort of UX do you need? – MikeAinOz Nov 2 '16 at 23:43
2

IMO, BCS and ECT would be the simplest, easiest no-code solution for your requirement to pull data from SQL. This BCS should ideally be used in conjunction with Secure Store for security purposes for secure authentication of external data.

BCS connects to External data store(in your case, SQL table) to get data. The data can then be displayed in an External list.

Once the data is pulled into the External list, you can use the OOTB business data webparts or list view webpart to show the data. Mostly, these webparts are responsive and work well with mobile devices.

However, if you think that these are not responsive enough, you can perform REST/CSOM queries on the External list to show data on a page. The result of these queries is usually JSON object, which can passed to your frameworks like jQuery, Angular, Bootstrap or Knockout etc to bind it into HTML. Usually, we use publishing feature and Content editor webpart(CEWP) or Script editor webpart(SEWP) to show the External SQL data.

Below image shows how you can connect a SQL Azure database to a SharePoint online site.

enter image description here

Reference -

Step by step guide to connect SQL to SPO

Use the Business Data Web Parts

Primarily of use to SharePoint developers - BCS REST API reference for SharePoint 2013

Displaying an external list with the Business Data List Web Part

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.