Our QA Department needs a report to look for the "root cause" of poor performance at our three "One-Stop" Centers.

The data is captured into a SQL 2005 database from an online form (ASP.Net web form) and consists of data such as ReviewID (PK), ReviewerName, CaseManagerName, ClientLastName, ClientFirstName, OneStopCenter, Program, ReviewDate and 50 to 60 questions where the answer and value in the field will be either "Y", "N", "N/A", blank (empty string) or NULL. So, each record has a field for each of the 50-60 questions.

This makes a table that's difficult to chart and graph, so I have a stored procedure that returns the data to Excel with the columns: ReviewID, ReviewerName, CaseManagerName, OneStop, Program, ReviewDate, [Question], [No], [Yes], [NA], [Blank], [oNULL] and [Finding]. The [Yes], [No], etc. fields contain a "1" in which ever field corresponds to the answer submitted for that question so I can count the responses to each question (using a pivot table currently). *The [Finding] field usually has the same value as the [No] field because a negative response usually indicates a "finding", but just a few questions actually result in a "finding" on a positive ("Yes") response, so the [Finding] field is necessary to count "Findings". We don't care about yes's and no's, just "findings".

Okay, the complicated part: They want to see a bar chart showing the percentage of Findings by Program at each of the three One-Stop Centers. Say there are three Programs; WTP, WIA and WP and three One-Stop Centers; North, Central and South. The chart would show North, Central and South grouped under "WTP", then North, Central and South grouped under "WIA", etc. Each Program should show, for example, WTP: North=33%, Central=34%, South=33%; WIA: North=30%, Central=40%, South=30%, etc. Okay, that's the first level before drill-down.

Next level they want to see the same chart, but filtered by Quarter, based on the ReviewDate, then filtered by Month in the Quarter. They are drilling down to get more and more specific.

Next level shows only one selected One-Stop Center by Month.

Next they want to drill down to see the total findings in a selected Month by CaseManager.

The questions will be grouped into general categories. This is not implemented in the database yet. They ultimately want to drill down to show the percentage of "Findings" for each CaseManager by question type/category.

I'm not asking for any of this to be done for me. I'm looking for suggestions on the most appropriate technology to accomplish the task.

I have a partial solution in Excel, but the resultant sheet re-calculations take forever (I have a couple of SUMPRODUCT formulas on the raw data sheet to calculate percentages by question and count "cases" or records since the table now consists of a row for each question of each actual record. So, I have #of records X #of questions rows on the sheet. This will soon exceed the limits of Excel 2003, as it already has 46,000 rows with two years of data.)

Solutions I've contiplated and can work with include:
1. Excel (not ideal because of row limits and performance),
2. ASP.Net and either existing charting technology or possibly a third party charting software (my preference) or
3. Windows Forms/VB.Net/Visual Basic 2008 (I don't think my VB skills are up to creating the graphics from scratch)
4. Some other suggestion?

If anyone has done this sort of project before, I'd really appreciate some guidance as to the best technology to use, resources to look at or examples. I'm still a beginning programmer so I'll have to take any advanced techniques in "baby steps" ;-).

Thanks,
David

This post has been edited by megnin: 2 Nov, 2009 - 08:28 AM

Hi,

Well with SQL Reporting Services and VB.Net/C#, you can do drill down reports.

It sounds a bit crazy what you are attempting but the apprach would be getting the data into a dataset as SQL Reporting can only bind to datasets. So you would have to do a couple of loops to get the data into a format that you can work with in your Report Designer.

This is all in a nutshell.

Reporting Services is free if you have MSSQL and there is a Report Designer in Visaul Studio 2005/2008 Professional.
Not to sure about the expess editions.

This is a Microsoft-specific question ad should be addressed in a Microsoft forum.