So, what does every organization need to do with SharePoint and, build dashboards!! This has a been a requirement for I think every client I have ever worked with over the past few years. The ability to create dashboards of content, that contains reports for the executives is more important than often the core functionality.
SharePoint On-Premises contains core features for dashboards based on components such as Excel Services (not SharePoint 2016) all the way to PerformancePoint. Each one has its merits and serves its purpose. Utilizing Excel Services allows for Spreadsheets to be the core design canvas resulting in no learning as such if you already know how to create an Excel chart or list of data.
Though this works well, it is isolated to Excel files, although you can consume external data through Excel, also if you are moving to SharePoint 2016 this is not your option.
With the enhancements of PowerView and PowerPivot the data can be sliced and diced to allow creation of rich charts as part of the dashboard.
Outside of Excel, PerformancePoint offers a rich experience through its “Dashboard Designer”. It is Office type application ready to configure your data connections and layouts.
Using this tool, you can create the data models all the way to the reports that you wish to render, which can include any type of ODBC data connection allowing for a rich dashboard. What’s great about this is that SharePoint List data can be consumed easily and then used to create the reports and charts you need. The core components are:
Using this data, it can then be transformed into a chat that allows for drill down as well as other filtering.
Combining these components with features such as SQL Reporting services you are able to generate fairly complex dashboards.
On-Premises SharePoint as you can see if a little limited on what can be done, most of the Business Intelligence components have not received any updated even into SharePoint 2016. Office 365 has received all new features such as Power BI that allows greater design, creating and publishing of dashboards using On-Premises data via the Data Gateway or cloud data using in-built connectors.
Power BI truly is the way the I would recommend you create dashboards, especially as you can embed them into your SharePoint site. In fact, if you have moved to SharePoint 2016 and also SQL 2016 with SQL Reporting Services 2016, then element of the reports can be embedded into the On-Premises and into Power BI.
Once data has been added to Power BI either online or through the desktop application you can layout the different controls as needed.
Drill downs are easy to create as they are tied to the component you determine that needs it.
This brings great power to the end and business users as they can simply click away and see the data as they want to. Office 365 brings great power to a dashboard design process.
Now of course to this you need an Office 365 subscription with Power Bi and then probably the data gateways On-Premises so that you can truly create hybrid dashboards.
If you wish to learn more you can watch the On-Demand webinars I gave on Business Intelligence within On-Premises and Cloud.