crystal formula to show 2 different values in same row with a criteria

crystal formula to show 2 different values in same row with a criteria

Have a query that displays result as follows
Student ScoreType Score Course Total Grade 120001 EXAMS SCORE (70%) 37.1
Mathematics 61.1 C5
120001 CLASS SCORE (30%) 24 Mathematics 61.1 C5
120001 EXAMS SCORE (70%) 46.9 English 67.9 C4 120001 CLASS SCORE (30%) 21
English 67.9 C4
120001 CLASS SCORE (30%) 28.5 ICT 90.1 A1
120001 EXAMS SCORE (70%) 61.6 ICT 90.1 A1
In crystal report, I have grouped by Course, now in the same line, I want
the results to be something like this Course Class Score Exams Score Total
Score Grade Mathematics 24 37.1 61.1 C5 English 21 46.9 67.9 C5 ICT 28.5
61.6 90.1 A1 The difficulty am having is the Class Score & Exam Score,
that is dependent on the ScoreType field so i create a crystal formula
called class Score like this Class Score = if {table.ScoreType} = "CLASS
SCORE (30%)" then {table.Total} else 0 Exam Score = if {table.ScoreType} =
"EXAMS SCORE (70%)" then {table.Total} else 0 Unfortunately, these
formulas give wrong information for almost all the columns, what can i do.
I have tried with cross-tab and it doesn't give what i want, any help on
the formula will be great since the scoreType will not change. Thank you