Problem with Cross tab

[ChrisMan2]

I'm having a problem with a cross tab report. I am building it around school courses (rows), possible grades (columns) and the values in the columns as the summarized field. My problem is when a course doesn't have any data for a grade, the grade is not returned and made part of the cross tab object. I have been asked to solve this in Crystal as I've been told doing it in the SQL would have a negative impact at the DB server. I was hoping to bring all the grades for a course/categories in with a sub report and then share the values with the cross tab. Is this possible? Below is an illustration of the problem and what I would like to happen.


Problem:

AO Area of concern Good
MATH
Interpets Stories X
Solves Problems X
Multiplication Tables X

Area of concern Good
Social Studies
Reading Maps X
Understands concepts X
Retains Information X


What I need it to do

AO Area of concern Good
MATH
Interpets Stories X
Solves Problems X
Multiplication Tables X

AO Area of concern Good
Social Studies
Reading Maps X
Understands concepts X
Retains Information X


Does this make any sense?

Thanks for you help,
Chris

 

[synapsevampire]

This "as I've been told doing it in the SQL would have a negative impact at the DB server" sounds like clear evidence that you need to introduce the dba to your new pet Kimoto Dragon.

Anytime a dba suggests that returning the proper dataset is better served other than on the database server they are either incompetent or don't understand the question, or possibly one leads to the other .

BTW, your problem and what you need to do are identical.

What you're talking about is fabricating data.

Add the table which contains the courses in and do a left outer to your current data (hard to say because you didn't share anything technical).

Now in the record selection criteria place things like:

(
isnull({b.course})
or
{a.course}={b.course}
)

Now you'll get all of the courses and data for those that are applicable.

Try including real technical information:

Software versions (Crystal and database)
Example data (show tables)
Expected output (you did this)

-k

 

[ChrisMan2]

Thanks k, We don't have a DBA, it was an application developer wearing a different hat. He was concerned that the time it takes to return the data in its "incomplete" state is already close to if not exceeding our service level agreements and he doesn't want to bolt on anything else. There are many courses, categories, and students being returned. I do think its going to have to happen on the database though.

The two samples aren't identical there is the extra AO category in the lower Social Studies category (I probably should have excluded Math because they are identical) but you hit the nail on the head about fabricating the data. I haven't seen the actual tables yet (different project, big secret) but from what I've seen of their previous models a simplified view would look something like this:

Course Table
Course_id Course
1 Math
2 Social Studies


Course_Grading_Category Table
Course_ID Category_id Category
1 1 Interprets Stories Accurately
1 2 Solves Story Problems
1 3 Mastered MultiplicationTables
2 4 Reading Maps
2 5 Understands Concepts
2 6 Retains Information

Grades_For_Course_Category Table
Grade_id Grade
1 AO (Adequate Output)
2 Area of Concern
3 Good
4 Poor
5 Excellent
6 OK

Grades_For_Course_Category_Index Table
Category_id Grade_id
1 1
1 2
1 3
2 1
2 2
2 3
3 1
3 2
3 3
4 1
4 2
5 1
5 2
5 3
5 4
5 5
6 1
6 2
6 3

Student_Course Table
Student_id Course_id
1 1
1 2

Students_Grades Table
Student_id Course_id Category_id Grade_id
1 1 1 2
1 1 2 1
1 1 3 3
1 2 4 1
1 2 5 2
1 2 6 2

The database is SQL Server 2000 and I am using Crystal 9.
I'm going to create some sample tables on the db and start messing with it.
Thanks for your help,
Chris