Leveraging PowerBI for Rich SharePoint Reporting

February 27, 2017

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, PowerBI 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.PowerBI 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. More details, including a systematic walk-through, in a future post.

Post by

Core BTS | National IT Solutions Provider

blog rachael wilterdink

Getting Started with Azure DevOps – Area Paths Tips

If you read my first blog about Azure DevOps, then I hope you’ll continue to tune in as I cover many different tips and tricks...
blog rachael wilterdink

Getting Started with Azure DevOps – Template Tips

If you are new to Azure DevOps, or you know the basics but haven’t explored more deeply, then this is the perfect blog series for...
Core BTS - Website - Paul Blog

A Year in Review: Where We’ve Been and Where We’re Going

As we enter March 2021, my first year as Chief Executive Officer of Core BTS is coming to an end. Today, we are a distinctly...
X