+ Post New Thread
Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 38
MIS Systems Thread, Edit Excel Output in Technical; Now that I have upgraded to June Release I will have to investigate producing another report that produces the Net ...
  1. #16
    Sivadam's Avatar
    Join Date
    Feb 2009
    Location
    Dronfield - Derbyshire
    Posts
    1,449
    Thank Post
    136
    Thanked 208 Times in 175 Posts
    Rep Power
    55
    Now that I have upgraded to June Release I will have to investigate producing another report that produces the Net Points over a date range.
    Before today it was not possible but now, according to the documentation, it should be!
    Mind you - the documentation said I could not produce the Total points per Pupil over a date range for Ach and for Beh - but my reports, indicated above, do just that!

  2. #17

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

  3. #18

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,887
    Thank Post
    749
    Thanked 1,466 Times in 1,218 Posts
    Rep Power
    364
    Blackwill / Simon
    On the support net forum the following thread which you can search for by the number at the end might be useful though doesn't give much more detail than you can gather from this thread:

    SIMS Reports into Excel Templates (21774)

  4. #19

    Join Date
    Mar 2008
    Location
    Shrewsbury
    Posts
    9
    Thank Post
    2
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Yes please that would be great.

  5. #20

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22

    Editing SIMS excel reports

    I think the easiest way is for me to attach the document hereSims1 net_excel_macro_reporting_188.doc

  6. 4 Thanks to Hecate:

    blackwill (13th July 2009), CaterinaAnna (16th July 2009), simon37 (14th July 2009), vikpaw (14th July 2009)

  7. #21

    Join Date
    Mar 2008
    Location
    Shrewsbury
    Posts
    9
    Thank Post
    2
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Great thanks

  8. #22

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

    excel code for this purpose

    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.

    Code:
    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

  9. Thanks to simon37 from:

    Crazyfray (14th July 2009)

  10. #23

    Join Date
    May 2009
    Location
    Sheffield
    Posts
    274
    Thank Post
    27
    Thanked 39 Times in 29 Posts
    Rep Power
    22
    Net count for behaviour and attendance - I've got an ongoing support log on this one as sims fails to run the report for this! If anyone's made it work let me know...

  11. #24

    Join Date
    Mar 2008
    Location
    Shrewsbury
    Posts
    9
    Thank Post
    2
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Thanks

    Thank you all for your help and suggestions and after taking a bit of advice from you all I finally got it to work.

  12. #25

    Join Date
    May 2007
    Posts
    13
    Thank Post
    4
    Thanked 1 Time in 1 Post
    Rep Power
    0
    MattMichell - my understanding is the June update will provide the behaviour one. From the update release notes:
    A net points total is now displayed on each pupil/studentís Behaviour Management page, which is a result of subtracting behaviour points from achievement points, providing a useful overview of a
    pupil/studentís conduct in your school.
    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"?

  13. #26
    Dancer's Avatar
    Join Date
    Mar 2009
    Location
    Norfolk
    Posts
    26
    Thank Post
    2
    Thanked 6 Times in 5 Posts
    Rep Power
    13

    Sims Analysis Reports

    The SIMS analysis reports are similar to excels pivot tables.
    Weíre not on the June release yet so not sure about net points but certainly works separately for behaviour and achievement reports.

    e.g. I have set a report to calculate achievement points like so


    Iíve filter the achievement subreport on incidents after a date
    You need to ensure suppress duplicates box is unchecked.

    Run to an analysis report and changed the settings to-


    You can change the row to reg or year group to show total points for the groups (we have rewards for best tutor group as well as students here)

    We have just changed our reward/behaviour policy to include points so it not fully up and running yet but these are reports Iím design for next term and seem to work fine.
    Ralph

  14. #27

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

  15. #28
    Sivadam's Avatar
    Join Date
    Feb 2009
    Location
    Dronfield - Derbyshire
    Posts
    1,449
    Thank Post
    136
    Thanked 208 Times in 175 Posts
    Rep Power
    55
    Dancer,
    That screenshot looks remarkedly familiar!

    Simon
    Mine has pupils in Rows!
    And is filtered by Reg Group!
    Just change the Column to Total and see what happens! (Mine I mean!)
    Last edited by Sivadam; 16th July 2009 at 03:44 PM.

  16. #29
    Dancer's Avatar
    Join Date
    Mar 2009
    Location
    Norfolk
    Posts
    26
    Thank Post
    2
    Thanked 6 Times in 5 Posts
    Rep Power
    13
    Quote Originally Posted by Sivadam View Post
    Dancer,
    That screenshot looks remarkedly familiar!
    If only for my own professional pride I will point out that I did devise the solution independently of Sivadam's report. Which I have now looked at and yes they are remarkedly similar.

  17. #30
    Sivadam's Avatar
    Join Date
    Feb 2009
    Location
    Dronfield - Derbyshire
    Posts
    1,449
    Thank Post
    136
    Thanked 208 Times in 175 Posts
    Rep Power
    55
    Great minds think alike eh Dancer?

    Or is it something about fools ........................... ?



SHARE:
+ Post New Thread
Page 2 of 3 FirstFirst 123 LastLast

Similar Threads

  1. excel spreadsheet editable online
    By Uraken in forum Web Development
    Replies: 4
    Last Post: 21st November 2007, 05:23 PM
  2. pdf file editting
    By russdev in forum Windows
    Replies: 7
    Last Post: 27th September 2005, 02:45 PM
  3. Excel frustration
    By GrumbleDook in forum Windows
    Replies: 3
    Last Post: 24th August 2005, 11:01 PM
  4. Replies: 1
    Last Post: 15th August 2005, 07:44 AM
  5. ISa 2004 Standard Edition
    By mrtechsystems in forum Windows
    Replies: 2
    Last Post: 31st July 2005, 05:33 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
  •