February 27, 2017

Leveraging Power BI for Rich SharePoint Reporting

Post by: Data Team

Often, I have clients ask me for ways to view and report on data within SharePoint. While the modern list experience has substantially improved ‘out of the box’ list views, it is still difficult to create the data visualizations that business users desire. With that being said, Power BI is changing the game. Its integration with SharePoint is a great way to produce reports and data visualizations. Let’s walk through the basics.

Reporting on SharePoint List Data

Note the data types in the list – which include: String: TitleDate/time: Delivery Date, Order DateCurrency: Sale AmountManaged Metadata: Bicycle Model, DistributorInteger: IDAlso worth mentioning, this list contains approximately 3100 items.Power BI ReportTo report on this data, we start in Power BI Desktop, and create a new connection using the “SharePoint Online List” data type:

From there, we connect to the SPO site that contains the sales data discussed earlier. Then, we choose the list(s) we want to query.   The nice thing about Power BI is the amount of customization you can do, and easily. For example, here are the visualizations available for reports as of this post:

Note the ellipsis at bottom-right, which allows you to import custom visuals as well if desired. For the sake of this post, let us assume we have already connected to our data source and chosen the columns we want to use. The next step is to start building your visualizations. The sample below is fairly simple, but powerful nonetheless. Report – default view (no filters, slicers, etc. applied):

After clicking “Diverge” to see sales of that bicycle model only. Note the updated counts by distributor:

Report – after clicking “Ryan’s Bike Shop” to see sales from that distributor only:

As you can see, the result is an elegant and aesthetically pleasing report. The report can be published to a SharePoint site in one of two ways: either as an iframe in a script editor web part, or (for first release tenants) via the Power BI (Preview) app part. Keep in mind that by leveraging Power BI for our reporting, we have the ability to schedule data refresh, define row-level permissions, and many other compelling capabilities. 

Here is another take on the same data, this time focused on revenue dollars instead of sales / product numbers: Revenue Report – default view (no filters, slicers, etc. applied):

Report – after clicking “Ruby” to see sales of that bicycle model only. Note the updated revenue by distributor:​

As you can see, Power BI provides rich reporting and data visualizations, which can greatly enhance the ability to see data in new ways. With just a little time, the right licensing, and some trial and error, you will be a Power BI pro no time.

New call-to-action

Subscribe to our Newsletter

Stay informed on the latest technology news and trends

Relevant Insights

Should You Disrupt Yourself to Accelerate Digital Transformation?

It has been interesting to watch Microsoft transition from a company that makes its money via licensing to one that...

Cybersecurity Myth Busted: Tools Are the Solution

When thinking about security, people often gravitate towards implementing various security tools, solutions, or products. If you bring up a...

Time to Reconsider MP-BGP EVPN for Your Datacenter Network?

VxLAN was defined in 2014 by RFC 7348 and has been used as a component in several SDN (software defined...