Edit Excel Output
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.
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.
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.
Showing my complete an utter ignorance here, but how do you access anaylsis reports in SIMS?
Check out the Sample Reports that I have uploaded to User Posts, File Sharing area of SupportNet.
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
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!
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.
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!
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.
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.
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.
Depends - if you want behaviour points by maths teaching group, for example, you might find that tricky in sims...
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.
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
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).