"Duplicates suppressed in complex reports" not suppressing duplicates
I recently wrote a report to list pupil achievement & behaviour points, create a pivot table for both, and subtract one from the other to give a kind of behaviour balance for individual pupils over a specified date range.
Originally I had unticked "Duplicates suppressed in complex reports", however, now the report's being used and it's apparent this won't work because SIMS reports seem to combine the possibilities in some way, so for example, if someone has 4 positive and 4 negative they're combined - so it shows 16 combinations, meaning we have 16 positive 16 negative points. If it were predictable that'd be one (tedious) thing, but I can't guarantee someone won't have no positives or negatives (which avoids the issue) or what the point scores will be.
Now I've ticked the Duplicates suppressed in complex reports box (and written some VBA so the pupil names are duplicated down the rows), but for the second column (achievements) it's still showing all the combinations with the behaviour, but some of the behaviours are blank/suppressed, so I have 4 behaviour and 16 achievement (behav1 with achievement 1, then a blank for 3 alongside achiev2 3 and 4, then behav 2 with achiev1 and so on).
To clarify, my filter ('between date') is running at the data selection level, so where you "select fields to be included in the report", both filters have the "include records with no such events" unticked. Telling it to only show the first record of course does just that, so that's no use. I have just tried running using the "define filter" bit on the 3rd page of options, as follows:
1) clear filters
2) add filter a) behaviour exists
3) add an 'or' then set it to "achievements exist"
4) for both of the above add a 'between' dates option, and a 'set yeargroup/s' option
SO! The question is, does anyone have any suggestions for how to filter out/suppress these extra rows?
1) Is there a way to actually identify unique records (if there is, I can write vba to delete the duplicate rows)
2) any suggestions for additional ways to filter using the SIMS methods
3) is there some way to run two reports, or separate the data so I can then merge them correctly - i.e a report (or tab) for achievements and one for behaviour which I then merge
and finally - is this a bug...or is there a 'feature' (in the non-euphemistic sense)
Thanks for any responses,