[sims] Edit Excel Output

[blackwill]

I'm trying to change a template for a report generated in SIMS.NET.

 

Basically I want to run a report that pulls all the behaviour points for a group of students and then adds them up in a pivot table. Tried finding where the template is stored so I can alter it but as of yet no joy. Can anyone suggest anything please.

 

P.S.

 

I can do this after the report has run, but don't want to go through designing pivot tables with admin staff, prefer it if it done it when run from SIMS.

[MattMitchell]

You can do it in SIMS using an analysis report, but it will run client-side, so it may depend on your hardware and what time interval you're covering.

[blackwill]

Showing my complete an utter ignorance here, but how do you access anaylsis reports in SIMS?

[Sivadam]

Check out the Sample Reports that I have uploaded to User Posts, File Sharing area of SupportNet.

[Hecate]

The template tempdata.xls is usually saved in the TempSimsRpt folder in my documents. Basically you can copy this and edit the macro to do what you want, then use this as your report template - I've done something similar to yours. I think the detailed instructions can be found in the advanced reporting handbook. If not I may be able to find my version for you

[Sivadam]

An Analysis Report does all the hard work for you! Why would you want to do it in Excel?

Unless of course you want the individual Behaviour or Achievement Incidents as well as the Total Points!

But they could be produced in a normal report!

Incidently - my reports contradict KB32648 (updated 23/06/2009), which says that you cannot report on Total Points over a date range!

Edited July 10, 2009 by Sivadam

[blackwill]

Again I repeat my last statement, and express my ignorance the only reports that I know of that do that are Assessment Manager type reports. If anyone has a howto I would be very grateful.

[Sivadam]

Analysis Reports are part of the normal SIMS Reporting. Instead of creating a Word, Excel or Mailmerge Template etc. you select the Analysis Option.

If you download the Templates I mention above, then you will see how it is done and how easily things can be accomplished by using SIMS, rather then exporting to Excel and then manipulating data further.

 

I find it extremely strange that this sort of question crops up in these Forums when there is a wealth of expertise to tap into on the SupportNet Forums.

 

There are obviously quite a lot of Technical experts on here and that is good. The real experts in how to use the SIMS Software are on SupportNet, however. With apologies to those on here that are expert in the software of course!

[MattMitchell]

Analysis reports are for doing cross-tabulation - what you'd call a pivot table in excel / access / SQL server. They do run at the client end, so if you're analysing 40000 behaviour entries it may not be instant, but if a report outputs the data then you can do it. There's a strong case for not using excel as this simplifies things and means the report will always be live.

[simon37]

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.

Edited July 13, 2009 by simon37
update

[blackwill]

Yes simon37 is correct I've had a look at the anaylsis reports and while they do produce a certain amount of information, it's not grouped in manner that makes it easily analyse for the staff involved. Thus my orginal question was how do I alter the excel template attached to the report, I have a report already that does this but it has errors in it that you have to sort out. Again I 'm trying to cut down on the amount of training for staff and if it's felt to be too complicated they won't use it. So thank you sivadam for your suggestion while it helps me, it's not quite what I'm looking for.

 

Regards

[MattMitchell]

Depends - if you want behaviour points by maths teaching group, for example, you might find that tricky in sims...

[blackwill]

All I want is a report that will show the total number of points for each student agianst behaviour types, for either individual forms or year groups. I can pull the data myself and make a Pivot table in excel but I want sims to do it at run time, I have a report that does that but for some reason it stops at the letter 'd' in the students surname. Thanks for all your suggestions.

[Hecate]

I have a SIMS document which goes into more explicit detail about how to edit a report template if you would like me to pm you - the example it uses is a pivot table

[vikpaw]

Hecate could you email it to me please.

 

i think i have the same document, it was from SupportNet, but i'm searching there now and can't find it.

 

i only have a hard copy in a folder, however it has no identifying codes / resource numbers on it. very useful it will be i think.

 

as for the pivot table not going past 'd' it might be that if you've reused a report template with a pivot table in it, the macro has some number hard coded into it (number of rows or some such value). if you edit the template and the macro you'll find somewhere that you have to change the number manually to fit the data you are throwing at it.

 

the document i'm referring to that shows how to edit the pivot table macro in excel explains doing this ( i think it's the same guide Hecate is referring to as well).

[Sivadam]

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!

[simon37]

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!)

[vikpaw]

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)

[blackwill]

Yes please that would be great.

[Hecate]

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

[blackwill]

Great thanks

[simon37]

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

[MattMitchell]

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...

[blackwill]

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

[simon37]

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