Surveys and Reporting - Part 1

At work my big project is to rewrite - almost re-imagine - our Annual Report process.

The way that it works right now is this:

1. I construct the questionnaire using an home gorwn tool, that is built in MS Access - it goes against an SQL backend.
2. I get review and approval from the research department.
3. I take about the same amount of time to build the survey to make it available in our production web portal.
4. I hand code a bunch of stored procedures to produce charts and graphs that are available to our online users.
5. I hand code a bunch more Stored Procs that make reporting available to our internal staff.
6. The internal reporting gets reviewed and approved by the research dept.
7. Once it is approved, I then publish that to production.

Its not so bad. Except for the part where I'm wanting to put a spike through my eye, into my cranium every time I work on it.

The worst part is that all the database fenagling(sp?) makes this process VERY SENSITIVE to change. The Annual report, since its annual changes every year. Making this at minimum a four month process.

Believe it or not, I've found bugs in the old Stored Procedures that were introducing errors into calculations. This means that some of the old reporting was inaccurate.

From a philisophical standpoint, I dont believe that the database has any business implementing any logic for reporting, but in the existing system, it does math, and it decides how things should be counted. This is an error.

The abstract changes that I am thinking about these days are these: 

1. Create a report framework that uses the database engines aggregation functions, rather requiring code to do math.
2. Change my role from one of programming the report, to managing the database that drives the report.
3. Enable the research staff to manage the reporting process, build it toward the future in which the research staff will be able to take over maintenance and design of the report, with no assistance from me.

So, I'm thinking about this new report in these ways:

We'll be using a web service that delivers and stores surveys and their responses as the tool to create and disemminate the Annual Report Questions, and to retrieve the data for our reporting purposes.

Potentially I will know very little about the survey itself.

I will need to have access to the survey responses and the survey structure.

This last part is important to the output of the report, because how a question is presented to an end user says something about the intent of its presentation on the report.

How am I going to manage this very important detail, which can make or break the report?

The problem is this: If there is an array of checkboxes in a question, does that array need to appear in the results of the report? For the purposes of matching the two up, I'd say yes. For the purposes of matching the existing requirements of the report, I'd also say yes.

Now, assume that we have some survey data to collect. We go and get it, we even load it into our trusty well normalized database.

When we go to look at it, its only data. Its raw responses from the survey. The data itself tells us nothing about the presentation of the survey, and therefore the report that we need to generate.

Hows that for a problem.

The fact is that this problem has been solved before.

Stay tuned for part two, where i'll start to talk about other problems that I will be have to solve, and solutions I'm cooking up - honestly, i have not had time to think up any.