SQL Server Reporting Services is an amazing tool which creates feature-rich reports customized to match business and user needs. With a little time and effort, SSRS can become a viable tool at your disposal which integrates seamlessly into your SharePoint environment. The most surprising part of this tool is that you most likely have it and don’t even know it.
A lot of organizations will overlook reporting, or set it as a to do list item for down the road. When the time comes to start reporting on internal/external metrics, a calf scramble takes place looking for a proper solution to their needs. Before going out and spending thousands of dollars on reporting solution, check to see if SSRS will suit your needs then compare the cost implementing other industry standards with this solution.
Data Transformation
As with any database setup, you have all kinds of information at your fingertips. The real question is: “How can we get this organization’s information to the business user and in a way that they causes informed action?”. With this use of custom SQL queries and SSRS, all of the information the business user needs will be available in a simple link. (A word to the wise though; be careful what queries you run!)
A best practice would be to keep your active queries optimized as to not put strain on the system. If you have reports which require lots of processing time and return a large number of records, schedule those reports to be run during off-peak times.
Scheduling and Output
SSRS offers a scheduling features which allows the administrators or business users to choose the time and date to run the report. This is a standard feature that is available in all versions of SSRS. The resulting report can be sent via email, stored in a windows file share, or uploaded to a SharePoint library. A couple of the more common file types include PDF, XLS, and CSV.
Integration
SSRS can operate in two modes, standard and integrated. Standard mode creates a report server through IIS where reports are deployed and executed from. This is a typical web based system that allows for maintenance, security, and your day to day operations.
If you have a SP environment, you can use the second operating mode ‘Integrated’. Here the Share Point environment becomes the report server. All of the reports are stored in SP, maintained in SP, and executed through SP. Another benefit to the integrating SSRS and SP is that SSRS inherits the permissions setup in SP. Also, because the two are integrated retain the ease of use in Share Point.
Example Scenario
At a previous position with a non-profit organization local to Houston, the volunteer coordinator came to me looking for a way to automate her monthly volunteer report. Once a month she would print out a list of volunteers including the number of hours they volunteered. She would then manually count and total everything up. The solution I proposed and implemented was a monthly report which did all of her calculations utilizing a simple SQL ‘Select’ statement. The report was stored in the local Share Point intranet and could be executed at any time. On top of this, we had the report scheduled to send an email to the Volunteer Coordinator and her supervisor on the first Sunday every month. The email generated contained the content of the report in the body of the email and also an excel attachment of the report.
SSRS is a flexible, robust, and available tool. This in combination with other business integration services allows every business to find the solution that best fits their needs.