Firstly, I’m sorry for such a long winded post, I am trying to be as precise as I can, so that I can give you enough info to assist me.

I work for a school and have been tasked with fixing up the school assessment reports. One of the reports I’m working on is a comparative summary of the grades for each subject in a given year level.

For example, English:

A    11
A+   8
B    3
B+   13 etc and so on.

Each subject area has what’s called an Assessment Code.  So year 10 English would be 10ENG, year 9 Maths would be 09MAT etc.

The report is simply passed the Assessment Code as a parameter and returns all the grades associated with it and then does a running tally for each. Simple.

My issue is that year 10 Maths has been separated into three Assessment Codes with only the final character different (10MAT, 10MAF & 10MAE). "They" want all three Assessment Codes to be tallied together as if they were one. 

I figure that because the Assessment Code is pretty standard (5 characters, first 2 being a number) I could simply replace the final character with a wildcard. So I’d end up with something like 10MA%.

So in the Record Selection formula I did this:

{ AssessmentCode} LIKE LEFT({?Pm- AssessmentCode},4) + '*'

I test the SQL that’s generated and I get

WHERE AssessmentCode LIKE ‘10MA%’

Perfect, except, when I run the report the tally doesn’t add up. The results I should get (according to the SQL) are:

A    11
A+   8
B    3
B+   13
C    9
C+   7
D    6
D+   12
E    3
E+   4

The results I’m actually getting (according to the report) are:

A     0
A+    0
B     0
B+    0
C     1
C+    0
D     1
D+    0
E     1
E+    1

I've been working and researching this all week and I’m utterly stumped if I can figure out what I’m doing wrong. I'd appreciate ANY guidance on the matter.

Kind regards,
Brad

Edited by brad_lucas - 21 Jul 2011 at 7:32pm

So, are you saying I can use just this and it'll work?:

{ AssessmentCode} LIKE LEFT({?Pm- AssessmentCode},4)

Thanks for the tip on running a trace, however, it's confused me even more. The query that CR is passing to the database is correct:

SELECT
   AssessResultsResult
FROM
   vStudentReportsSemesterResults
WHERE
   AssessmentCode LIKE '10MA%'
ORDER BY ID DESC

I guess that means I've at least ruled out the SQL as the issue. Now to look at the running totals.

Thanks for your help.
Brad

simply mistake...