More reporting

In the past I’ve worked on a lot of reporting solutions.  My most recent work project has no reporting component which is contrary to what I think software needs.  If your system is collecting and storing data, your users will most likely want to get that data back out in some meaningful way. 

I’ve seen reporting solutions be as simple as a grid listing in the software itself that can double as a report when printed (i.e. a Customer Listing report).  I’ve also built a couple of systems that were database driven solutions which could dynamically create a selection criteria window, assign the user selections to a T-SQL statement and alter the appearance of the report formatting based on database entries (see Crystal Report vs Active Reports for brief descriptions).

Currently I’m working on a side project that uses all the newest technologies.  The application is written in C# 2.0, the database backend is SQL Server 2005 and the reporting solution  I’m using this time is SQL Reporting Services 2005.  I’m intending on writing a fully detailed account of Reporting Service in the next short while that will be provide the final comparison between SRS, CR and AR.

On this side project I’m once again working on a fully database driven solution, but I’ve made a few changes to the way that I’m structuring it.  One of the most significant changes is that I’m no longer using T-SQL stored in a database field as the source for the reports queries.  Instead I’m using stored procedures (the name of the procedure is still stored in the database) and parameters.  One of the reasons for choosing to put the report queries in the database was to allow for the report to be easily altered without need for software upgrades.

For a couple of reasons I think the use of T-SQL was flawed.  One of the reasons was that it didn’t really make the maintenance all that much easier.  SQL statements stored in a database field were tremendously difficult to recover, reformat, alter and upgrade (primary difficulty being the reformatting).  Another reason was that we did, on occasion, create SQL statements that were larger than a varchar(8000) field could hold.  To overcome that we used a text data typed field, but it added more difficulty to the retrieval of the queries.  Because we were inserting the selection criteria of the users straight into the base T-SQL queries, we had also opened up the possibility for SQL injection attacks.

By using the stored procedures with parameters I have significantly reduced the possibility of a SQL injection attack, the stored procedures are much easier to work with through the upgrade cycle and flexibility does not appear to have been compromised.  The only thing that has become more difficult is the passing of multi-valued selections into the stored procedures.  There are ways around this and I will outline the one I’m using in a future post.

One of the things I did on the last framework I built was create a utility program that allowed for the creation and editing of the database components of the reporting solution.  I’m planning on creating this feature again, but this time with the additional ability to create an easy upgrade process for disseminating the new, or altered, reports.

Currently this reporting framework is not as functional as the ones I’ve built in the past.  It still requires the ability to alter the report layout and groupings, as well as the ability selection criteria to trigger changes in the state of other selection criteria.  Once these things have been created, along with some sample reports, I’m planning on posting a thorough article on the framework and it’s portability.

I”m the Igloo Coder and I’m reporting today from deep inside the bowls of a mink whale while I wonder which end of the whale I will be extricated from.