Level Five: SQL Server Reporting Services (SRSS)Why SQL Server Reporting Services (SSRS) deserves our focus
In Level Four we looked at ODBC connections as a way of connecting Excel directly to EXO database. You must have guessed that not only Excel can connect to SQL. There is a large number of third party reporting tools that can do so. If you know one – chances are it can connect to Microsoft SQL Server.
In broad terms, I would classify report writers as follows:
- Full reporting suites: SQL Server Reporting Services, Crystal Reports and others.
Such suites include a proprietary reporting engine and a client interface (a program or a web page). There is usually a report writer program that comes as a developer tool. For a company using EXO that comes with Clarity report writer, a full reporting suite can be a consideration only if it addresses limitations of Clarity reports (which are few and far between).
- Excel based solutions: Alchemex is one example of such tools.
These solutions use Excel as an engine (ODBC connections) as well as client interface. The report writer comes as an add-on to Excel. Developer tools can be quite sophisticated, but usually lack the scale and robustness of full reporting suites.
- Cloud based solutions. A quick Google search will yield the reader a stack of those.
While there is a great variety of such tools, some of them provide not more than a slick client interface. The “guts” of report building should be done prior to loading clean data into the tool. Common considerations in using such tools are data security and cost benefit analysis.
In my opinion, SQL Server Reporting Services (SSRS) deserves our focus for a number of reasons.
- SSRS is part of Microsoft SQL Server. Even the free Express Edition of SQL server has SSRS with minor limitations to features.
- SSRS is most likely already available on an existing SQL server installation. If installed correctly, it only takes an hour to configure the back-end and get it up and running.
- Choice of built-in reporting website or native integration with SharePoint portal.
- Native integration with SQL Server Analysis Services for Business Intelligence and Data Mining applications.
Web (Intranet) Access
- Can be integrated into EXO URL Dashboard Widget or any other application that can load a web page (including web-shops).
- Standalone use: no EXO user licence required.
- Out of office access (subject to network configuration: VPN / part of company website).
- Windows login based security is used to access reports (seamless single sign-on is available when using Internet Explorer).
- Reports are published on an intranet website, which makes their use rather intuitive.
- User can set a “subscription”, so their reports are delivered via email or saved to network folder.
- Subject to permissions, users can create “their” versions of report by changing parameters and even editing reports (subject to permissions).
- Reports can be exported to variety of formats when viewed or delivered as subscription. As a report writer I like native Excel / Word / HTML export that SSRS offers.
SSRS is a very powerful tool that helps deliver information to users in a medium to large enterprise environment. SSRS is not a replacement for Clarity reports, rather a supplement. It adds “web” and “self-service” elements to reporting and allows a broader user base get access to enterprise information.
It is important to note that with power comes complexity. Unlike Clarity, which is a super-user friendly package, SSRS reports are built using Microsoft Visual Studio. Creation of reports will require functional knowledge of SQL queries and language, and some basic development techniques.
I hope above won’t turn the reader off the tracks of exploring the rich SSRS offering. Below are screenshots that illustrate user-facing side of SSRS. There’s also examples of SSRS GL reports Kilimanjaro team has built recently and a full P&L report exported to Excel.
Screenshot 1: Internet Explorer showing a report folder with two reports.
Screenshot 2: A generic P&L Report for Motopartz.com.au demo database