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: Title
Date/time: Delivery Date, Order Date
Currency: Sale Amount
Managed Metadata: Bicycle Model, Distributor
Integer: ID
Also worth mentioning, this list contains approximately 3100 items.
PowerBI Report
To 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.
Ryan Dennis
Ryan Dennis is a Solution Architect at Blue Chip. He has over 10 years of experience in the information technology industry, and has played key roles in several large-scale content management, deployment, application development, cloud, and migration projects.
Ryan’s core skills include architecting SharePoint environments and solutions, as well as configuring, customizing, and implementing SharePoint-based Intranet portal, Internet websites, and secure Extranet portals. Ryan also has significant experience delivering SharePoint content management, forms, upgrade, and migration solutions. One of his areas of specialization includes Windows PowerShell.
