As Business Intelligence has evolved over the years the number of tools we have to choose from for presenting data has advanced drastically. With so many tools to choose from it can be rather confusing (especially when your company is just getting their feet wet in BI) to determine which tool is the right one for an organizations reporting needs. Just within the Microsoft suite of tools (not including third-party tools) you have Reporting Services, Excel, PowerPivot, PerformancePoint and Power View.
Many companies try to marry themselves to one or two reporting tools and fit their needs into the restrictions of the tool(s) they have chosen. The truth is not a single one of these tools can solve all reporting needs. While each one of these tools by themselves may be able to present your data, you will find that using a combination approach will conclude in a much more well rounded and impressive reporting solution.
That’s why in this blog series I hope to demystify the decision process and educate you a little bit on each of the tools so that you can easily determine which tool would be best in different scenarios. Like I mentioned earlier the Microsoft reporting tools we will cover will be:
- Reporting Services
- Excel (From the perspective of building PivotTables without PowerPivot)
- PowerPivot
- PerformancePoint
- Power View (Yes Power View is supposed to be
considered part of Reporting Services in SQL Server 2012, but it really
is an entirely different tool than SSRS is traditionally thought of as)
After discussing each of the tools each individually I’ve bring it all together by showing you how to use a decision matrix to determine which tool makes sense for your work examples.
Reporting Services
What it is
Reporting Services is traditionally used for developing static reports. The word static here is referring to the report layout rather than the report data (obviously the data will change). For example, I want my sales team to be able to review a line item detail of each of their sales from the prior day. With Reporting Services I could have the sales team either log onto a web front end to view a live version of the report or I could have the report emailed to them daily.
The reports developed with the tool are highly customizable. In fact just about any object of the report can be manually changed or made dynamic with the expression language it uses.
If you’re looking for a tool for creating dashboards then Reporting Services also has the capability of filling that need. With charts, gauges, indicators, sparklines and maps you have many of the necessary tools for creating an impressive dashboard. There are other tools that like PerformancePoint that can create more impressive interactive dashboards but Reporting Services can get the job done.
Reporting Services is also highly scalable. Without much effort the workload for report processing can be shared across multiple instances of reporting services. After installing multiple instances the native wizard guides you through creating a farm of report servers.
Another thing worth noting because it will not be true of all the other tools I discuss in this series is that it can connect to virtually any data source you have.
What it isn’t
While there are some parts of Reporting Services that can provide ad hoc reporting capabilities the core of the tool is for static reporting. If you really have a need for ad hoc reporting and could care less about report layout than you would likely pick another tool like Excel.
Who Uses it
While Reporting Services does have functionality for a power user to create their own reports it is typically thought of as a developers tool. So report developers would use the tool Business Intelligence Development Studio (BIDS), which is part of the SQL Server install) to create incredibly detailed reports with almost endless flexibility when it comes the visualizations.
The tool that power users can use for creating reports is called Report Builder. It has almost all the same functionality as BIDS but has been simplified a little for end users with more wizards and easier ways to bring in datasets. Because it has a lot of the same functionality of the developers too you will find that this tool is for highly technical end user.
How is it consumed
Like I mentioned earlier Reporting Services reports can be consumed through a web front end of the native Report Manager, SharePoint, or even embedded in a custom application you’ve written with the Report Viewer control.
In addition to viewing the reports from a web browser they can also be delivered directly to the users via email or in a shared folder that they can access. This is done using an incredibly useful part of the tool call Subscriptions.
Limitations
Again, Reporting Services is a static reporting tool therefore if your users find the need to change report layout frequently than that may eliminate this tool as an option for that particular report.
Another limitation you may run into is some of the functionality with Subscriptions, the report delivery tool mentioned earlier, are enterprise only features. For example, if you wanted to make the reports delivered dynamic based off a list of emails you have in the database that must be done using Data Driven Subscriptions, which are Enterprise only.
Summary
As we go through this series remember these high level characteristics about Reporting Services:
- For static report development
- Extremely customizable
- Dashboard capable
- Highly scalable
- Traditionally thought of as a developers tool
- Has a native web front end but can also view reports through SharePoint and direct delivery with subscriptions
I hope you’ve found this helpful and stay tuned for the Part 2 in this series on Excel. To read any of the other parts to this series follow the links below.
