"Duplicates suppressed in complex reports" not suppressing duplicates

[simon37]

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?

 

Possible routes

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,

 

Simon

[MattMitchell]

Do two separate reports and inner join on admission number - upn isn't guaranteed to have a value.

[MattMitchell]

Multiple subreports gives you all the various combinations -it's best to have each subreport in a separate report, unless you filter them so each can only return one line.

[vikpaw]

You can make a list in Excel unique by using the Advanced Filter option and copying the data to another tab/sheet with the 'unique records only' check box ticked. You sound like you know what you are doing with VBA so could automate it. I've only ever done it manually.

[simon37]

Hi vikpaw. I didn't know about the advanced filter method, but I had thought to do something similar. My problem is, I can't see any way to identify whether or not a record is genuinely unique, or whether it is in fact a separate but identical incident, i.e. whether or not it's SIMS being weird, or whether the pupil has just received two identical incidents, e.g. has twice been given a 'minor disruption' behaviour, the second of which would presumably be (perhaps sometimes incorrectly) wiped out by using any kind of duplicate filtering.

 

Having said that and talked to some people I have decided to 'give it a go' on the basis that giving two 'minor disruption' incidents in a single lesson is just poor use of our behaviour scale, esp. given you can manually increase the number of points associated with any behaviour (or achievement) when you enter them. I think 'merits' are a bigger issue...but we'll see how it goes, and it saves the admin team having to learn how to use pivot tables, and the intricacies of what formatting you need to cut/paste/use in formulae, paste special and so on. Thanks for your help, I'll post a report.def and excel template with vba here if I remember.

[simon37]

Of course, you can't actually report on what exact time it was recorded....I'm going for the imprecise 'subject,date,type,staffname' combo which needs to be unique for it to be counted. With duplicates suppressed (so only one unique behaviour copy is shown) I've sorted the achievements alongside each name, and then "zeroed" all the duplicates. The rows still exist, but without any meaningful data. I don't think edugeek will accept the file extensions as attachments, so copies of the excel code and report def are here: sjgknight - School Data ICT

[Greg]

Hi Simon37,

Have you tried adding your user to the Third Party Reporting group in Sysman, you can then add ID to the Behaviour reports to get the unique ID for each recorder incident

[simon37]

Hi Greg,

 

I've never heard of/used that module(?) &/or function. I wonder i you could elaborate a bit? Cheers

 

Simon

[Greg]

Hi Simon37,

 

No problem

 

In SIMS, go to Focus System to open System Manager

Close the Welcome screen and go to Focus | Groups

Double Click on the third Party reporting Group and add yourself to this with the plus (change the from date at the bottom to yesterday so you have the permissions immedately)

 

Close SIMS and Sysman and log back in.

Now when you go to design reports you have extra fields that you can select for your report.

 

For example, start a new report on Students and add the Behaviour sub category, within this now is ID and Student ID under the CESThirdPartyFields branch - ID will give you the unique id for each behaviour incident (multiple students in the same incident will be under the same incident ID here).

 

Most areas of reporting have extra fields under CESThirdPartyFields, they can be very useful.

 

Hope this helps

Greg

[simon37]

Thanks for that Greg. I've cobbled something together which should fulfill all the needs without any errors, as long as staff never give the same achievement point more than once to the same student in the same lesson & date. The report def is on the link above.

 

I actually left the school I wrote it for in July, but still had remote access, I've just started a PGCE so whether a) I can get more access quickly, and b) I have time to play more who knows - but it is certainly useful to have the information should I wish to write reports at another school or to improve this one for another school.

 

The only issue with my report, and I can only assume this is a glitch rather than a coding/reporting error, is the first pupil on each report seems to have the wrong entries when they ought to have more than one of either behaviour or achievement, they only ever show the first one of either where all of the others are working fine (as is the pivot tabling), if anyone has any ideas on what's going on here I'd be interested to know.

 

Cheers,

 

Simon