+ Post New Thread
Results 1 to 10 of 10
MIS Systems Thread, "Duplicates suppressed in complex reports" not suppressing duplicates in Technical; I recently wrote a report to list pupil achievement & behaviour points, create a pivot table for both, and subtract ...
  1. #1

    Join Date
    May 2007
    Posts
    13
    Thank Post
    4
    Thanked 1 Time in 1 Post
    Rep Power
    0

    "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?

    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

  2. #2

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    Do two separate reports and inner join on admission number - upn isn't guaranteed to have a value.

  3. #3

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    275
    Thank Post
    27
    Thanked 40 Times in 30 Posts
    Rep Power
    22
    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.

  4. #4

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    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.

  5. Thanks to vikpaw from:

    simon37 (15th September 2009)

  6. #5

    Join Date
    May 2007
    Posts
    13
    Thank Post
    4
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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.

  7. #6

    Join Date
    May 2007
    Posts
    13
    Thank Post
    4
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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

  8. #7

    Join Date
    Jan 2007
    Posts
    101
    Thank Post
    0
    Thanked 35 Times in 24 Posts
    Rep Power
    22
    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

  9. #8

    Join Date
    May 2007
    Posts
    13
    Thank Post
    4
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Hi Greg,

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

    Simon

  10. #9

    Join Date
    Jan 2007
    Posts
    101
    Thank Post
    0
    Thanked 35 Times in 24 Posts
    Rep Power
    22
    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

  11. 2 Thanks to Greg:

    simon37 (19th September 2009), vikpaw (19th September 2009)

  12. #10

    Join Date
    May 2007
    Posts
    13
    Thank Post
    4
    Thanked 1 Time in 1 Post
    Rep Power
    0
    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



SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 3
    Last Post: 11th September 2009, 10:01 AM
  2. Replies: 2
    Last Post: 11th August 2009, 12:08 PM
  3. "Print Limit Pro" or "Print Managent Plus"
    By burgemaster in forum Windows
    Replies: 24
    Last Post: 10th June 2009, 10:32 AM
  4. "Error 403" & "Moved to here" message
    By tech_guy in forum Windows
    Replies: 4
    Last Post: 24th January 2008, 02:07 PM
  5. Replies: 6
    Last Post: 28th September 2006, 08:06 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •