simon37

I had issues like this most of which were related to problems in my code, so: 1) make sure where you refer to cells, you've selected the correct area, and you've referred in such a way that the area only ever uses 'active' cells (from row 1 to xl.end function or whatever it is so its looking at the last used row). 2) the columns have headers/titles 3) I had a problem when a-c had data in, pivot table was only looking at a-c but d also had bits of data in (but no header), so either delete unnecessary columns or make sure they have headers or something 4) the pivot tables seem not to like having rows with empty data, which when exporting from SIMS can be a bit of a problem because you often have empty cells. One way to get around this is to include in your code a replacement value, so record a macro where you select your data area and use 'replace' (ctrl+h) leave 'find what' blank and change 'replace with' to some value (probably a letter, numbers might affect formulae). Hope that helps, post back if you solve/want further suggestions/help

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

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

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

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.

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

I can definitely see the purpose of those reports Dancer, but there's no option to have pupils as rows, so I think for that purpose the only way is to export into a user defined excel (maybe rtf too, I don't know) report. I put a few reports on my googlesite last night if anyone wants to import them. sjgknight - School Data ICT

Posts here: http://www.edugeek.net/forums/mis-systems/38519-edit-ecxel-output-2.html#post359947 discuss an example, offer a 'how to' doc and give some code.

MattMichell - my understanding is the June update will provide the behaviour one. From the update release notes: Surprised attendance doesn't work, perhaps you could clarify what you mean, although I think it should probably go into a new forum post. As for this post, what do you mean by "the report"?

If anyone is interested, the (very rough) code I've used, using the customisable reports discussed in the doc above is below. It prints a total behaviour and total achievement score and then subtracts the behaviour from the achievements, places some conditional formatting on that. For various reasons it's quite unattractive but the principle is there. Sub BehaviourBalance() ' Macro recorded 14/07/2009 by sknight 'turn on automatic calculation With Application .Calculation = xlAutomatic .MaxChange = 0.001 End With ‘turn off pivot chart autofunction thing Application.GenerateGetPivotData = False 'name the sheets Sheets("Sheet1").Name = "Data" Sheets("Sheet2").Name = "PupilSummary" 'define the data area ActiveWorkbook.Names.Add Name:="Data", RefersToR1C1:= _ "=Data!R1C1:(OFFSET(Data!R1C1,0,0,COUNTA(Data!C1),8))" 'set the point columns as numbers not text Range("E2:E2000").Select Selection.NumberFormat = "0" For Each xCell In Selection xCell.Value = xCell.Value Next xCell Range("H2:H2000").Select Selection.NumberFormat = "0" For Each xCell In Selection xCell.Value = xCell.Value Next xCell 'for the second selection I need to replace blank values with 0s Range("Data").Select Selection.Replace What:="", Replacement:="0", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False 'change field text Sheets("Data").Select Range("E1").Activate ActiveCell.FormulaR1C1 = "BehaviourPoints" Range("H1").Activate ActiveCell.FormulaR1C1 = "AchievementPoints" 'some code I found on http://www.mrexcel.com/forum/showthread.php?t=151356 to solve a problem 'create the first pivot table from 'data' for behaviour, add name and behaviour sum as columns Dim PTCache As PivotCache Dim PTBehav As PivotTable Worksheets("Data").Activate Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _ SourceData:=Range("Data").CurrentRegion.Address) Set PTBehav = PTCache.CreatePivotTable(TableDestination:=Sheets("PupilSummary").Range("B3"), _ TableName:="Pivot1") With PTBehav .PivotFields("Name").Orientation = xlRowField .PivotFields("BehaviourPoints").Orientation = xlDataField .NullString = "0" .PivotFields("Sum of BehaviourPoints").Caption = "Sum Behav" With .PivotFields("Name") .Caption = "Name " End With End With 'create the second pivot table from the first for achievement, add name and achievement sum as columns Dim PTAchiev As PivotTable Worksheets("Data").Activate Set PTAchiev = PTCache.CreatePivotTable(TableDestination:=Sheets("PupilSummary").Range("e3"), _ TableName:="Pivot2") With PTAchiev .NullString = "0" .PivotFields("Name").Orientation = xlRowField .PivotFields("AchievementPoints").Orientation = xlDataField .PivotFields("Sum of AchievementPoints").Caption = "Sum Achiev" With .PivotFields("Name") .Caption = "Name " End With End With Application.CommandBars("PivotTable").Visible = False Worksheets("PupilSummary").Activate Range("h5").Select 'subtract behav from achiev ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-5]" Range("h5").Select Selection.AutoFill Destination:=Range("h5:h2000") Range("h5:h2000").Select 'set autoformat on h column Columns("h:h").Select Selection.FormatConditions.Delete Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _ Formula1:="-4" Selection.FormatConditions(1).Interior.ColorIndex = 3 Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _ Formula1:="2" Selection.FormatConditions(2).Interior.ColorIndex = 4 Range("H3").Activate ActiveCell.FormulaR1C1 = "Balance" 'whack an auto filter on to select tutor group level data Range("I5").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Data,2,FALSE)" Range("I5").Select Selection.AutoFill Destination:=Range("I5:I2000") Range("I5:I2000").Select Range("I3").Select ActiveCell.FormulaR1C1 = "Tutor Group" Columns("I:I").Select Selection.AutoFilter Range("I3").Select Selection.Font.Bold = True Columns("I:I").EntireColumn.AutoFit 'laziness - deletes the columns I left blank Range("G:G,D:D,A:A").Select Range("A1").Activate Selection.Delete Shift:=xlToLeft Range("A1").Select End Sub

Hecate - if you could PM me that document I'd be very grateful. blackwill, did you have a go at importing the report I linked you to on ScoMIS - General Publications I believe it does what you want (although unfortunately not quite what I want). I know you're not asking for it 'by tutor group' but on that report you can see overall and narrow the selection down to tutor group if you'd like - it provides an overall point score for each pupil (although not by behaviour type - if you want that I guess automating a pivot is the best option!)

I'm trying to do the same thing. You might find the "Behaviour Points grouped by Reg Group" report here: ScoMIS - General Publications useful. Now I need to work out how to do the same for achievement points...and then work out a balance (ideally automatically). Let me know how you get on! Using that the Analysis option just gives me a tutor group total by date, where what I want is a pupil total by date alongside pupil total for achievement points also by date. Obviously some SIMS reports have pivot tables on them, but I don't know how you add these, and I think this is the question blackwill is asking.

SimpleSi, Did you get those images from a free image site? Would you mind sharing where if so? I've found some places with ok looking post-it/note style images, but I've had problems keeping the image quality and putting text on to them. Best, Simon