Bar Charts with non-stacked subgroups

[jimsteph2001]

I need help creating a chart that shows the total number of each grade type for each trimester at an elementary school. I'm using Crystal Reports 2008 querying a SQL Server 2005 database.

My grouping so far is first by grade level then by subject (Math, Language Arts, etc.). Within each of these I need a single chart that shows how many students received each of the possible grades (ADV, PRO+, PRO, APP, BASI, BLB). The reason I'm asking for help is because they also want it broken down by trimester, so the chart would look something like:

            3    123     23   1     1
           23    123    123   123   12
Math      123    123    123   123   123    123
          ADV    PRO+   PRO   APP   BASI   BLB

Each of the grades would have three subgroups (bars) representing the three trimesters. The y-axis scale would be the count of students who received each grade, with the maximum value set to the total number of active students at the end of the year (plus a small fudge factor in case the T3 enrollment is smaller than the T1 or T2 enrollment).

All data this chart needs is contained in a single table in the database, call it Grades. Each record has the following data in it (names have been changed to make them make more sense here):

StudentID, T1_Active, T1_Grade, T2_Active, T2_Grade, T3_Active, T3_Grade

The count for a specific trimester would only occur if the student was active that trimester, i.e.: T1_Active = "A"

My questions:
1) Does CR support bar graphs with subgroups that aren't stacked?
2a) If so, what would be my next step – after the initial grouping mentioned above – to making this work? Is the secret in running totals, formulas, or something else?
2b) If CR doesn't support this directly, would it be worth while to fake it with six separate bar charts – a chart just for ADV next to a chart just for PRO+, etc. – or would the performance penalty be too much? Would I need to have them each in their own subreport, or could they all live on the same level?

Thank you.

 

[jimsteph2001]

I solved it!

The first step was to simplify everything drastically with a SQL Command. I used something like:

select  StudentID, CourseNum, Section, T1_Grade as Grade, 'T1' as Trimester
from    Grades
where   T1_Active = 'A'
UNION ALL
select  StudentID, CourseNum, Section, T2_Grade as Grade, 'T2' as Trimester
from    Grades
where   T2_Active = 'A'
UNION ALL
select  StudentID, CourseNum, Section, T3_Grade as Grade, 'T3' as Trimester
from    Grades
where   T3_Active = 'A'

as my table, and joined it to the Student table so I'd have the grade level (I suppose I could have used some joins in the SQL Command to put the grade level in it, but I didn't want to get too complicated).

Next, I created a Cross-Tab using Grade as the Columns, Trimester as the Rows, and Count of {Grades.Grade} as the summarized field.

Finally – and this is the accidental part that would have saved me a ton of frustration if I had only known earlier – I right-clicked on the Cross-Tab and chose Insert Chart and, voila! I have exactly the chart I needed.

 

[lbass]

Thanks for the explanation.

-LB