In a typical SharePoint scenario, we build many small scalable custom applications where the SharePoint list(s) hold the data.
One of the major challenge faced by SharePoint developers is BI Reporting of the data produced by such applications. Power BI is certainly a go-to tool for such a scenario and is the tool recommended by Microsoft also, however, the biggest hurdle is the additional license cost of the Power BI Professional product.
No doubt, Power BI is super rich with several BI reporting features in comparison to excel based BI reporting, but still it may not be viable for most of the small and medium businesses or even for the large-scale industries especially when the SharePoint application is not yet scalable enough to make further investments.
There are many feature benefits of Power BI as compared to excel based BI reporting, one of the major feature where Power BI Scores over Excel-based BI reporting is with the real-time data refresh through a web-based interface. Though it is possible to perform data refresh through the online interface of excel within SharePoint. There are a few nuances which need to be taken care. With the proper plan upfront, it is still possible to have a seamless connection with the SharePoint list(s) in Excel online.
In this article I am providing the steps to
- Establish the connection with SharePoint Online in Excel using OData feed option, generate a simple BI report
- Publish these dashboard reports to SharePoint Online site
- Use the refresh option so that BI reports can be displayed based on real-time data through excel online.
In the follow-up blog post I will be covering the nuances associated with these refresh feature and options to overcome these nuances.
Prerequisites & Preparation:
- Have at least SharePoint Online Trial or Development version.
- Have Microsoft Excel 2013.
- Ensure to create all the required lists based on your business requirement within SharePoint.
For the demo purpose I have used the tables from very famous North-wind DB and re-created each table as lists and populated them with dummy data.
Let’s begin to explore this option. Mentioned below are the steps in sequence.
- Open the blank sheet on Excel and go to Data tab.
- Here under Get External Data section click on “From Other Sources” button.
- Now select “From OData Data Feed” option.
- Now On the next screen and under the “Link or File” enter the OData URL.
- To import data from the lists within SharePoint Online site to excel using OData feed, you are required to append “/_vti_bin/listdata.svc” at the end of the Site URL. Example: if your SharePoint Online site URL is “https://demo.sharepoint.com/sites/demosc” then your Odata URL will be “https://demo.sharepoint.com/sites/demosc/_vti_bin/listdata.svc”
Under Log on credentials select the option “Use the sign-in information for the person opening this file” then click on Next > button.
- The next screen lists down all the lists within the site for which we have added the OData URL in the above step.
- Now on this screen select all the lists whose data you are planning to use within excel for Dashboard reporting. click Next button.
- In the Next Screen Update the fields “File Name” and “Friendly Name” as per your convenience
- Click on Finish button
- The next screen presents various option as mentioned in the picture
- Select the option which best suits for your scenario and click on OK
- Next using the imported data structure generate the dashboard reports as per your requirement,
- I have created a dashboard to get the bar chart using the slicer for various Product Categories whose order is spread over different periods as shown in the picture
- Now Upload the Excel file to the document library within SharePoint online.
- You can open this uploaded file in the SharePoint online
To get the latest list data, you need to click on “Refresh all connections” option under “Data” Tab.
Limitations: Listed below are few limitations of this approach
- OData based linking of SharePoint lists with excel will cause issues if the lists contain lookup columns.
- It is not straightforward to fetch Lookup information using this method. However, there are alternative methods you can use to build a relation between lists instead of lookup-based linking.
Only the users who have permission to the linked lists in SharePoint will be able to refresh the data to fetch latest information.
Excel for BI Reporting and Publishing Through SharePoint – Part 2
