February 27, 2017

Leveraging PowerBI for Rich SharePoint Reporting

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.

Relevant Insights

Are You Falling into the Cloud Conversation Gap?

Most conversations about migrating services to the cloud focus on migration of application workloads and data. The vision is often...

How Aggregation Tables Improve Performance for Power BI Reports

In this blog, Marcus Radue, Solution Architect, offers high-level guidance in the advantages of aggregation tables in Power BI. For a...

Getting Started with Azure DevOps – Views

Like most Microsoft products, there are many ways to look at or perform the same activities. Azure DevOps is no...