MIS Systems Thread, Advanced Reports in Excel in Technical; Evening all
2 years ago now I created a report in SIMS that sent some data out to excel, did ...
11th December 2013, 08:51 PM #1
Advanced Reports in Excel
2 years ago now I created a report in SIMS that sent some data out to excel, did a bit of formatting, did some fancy sums, created a little table and set a print area. I was very pleased with my self.
Now, I need to produce something far more advanced however I can't even remember the basics of where to start in creating the report.
I used to have a copy of the Advanced SIMS Reporting guide however I can't find that anywhere either.
Can anyone help me out by either telling me where I have missed the starting point for creating my report or able to send me across the Advanced SIMS Reporting guide?
12th December 2013, 10:40 AM #2
It's all to do with macros. There is a guide on here somewhere, I think @vikpaw has it.
I remember making a new Excel template in the Reporting tool, adding a new function (Public Sub) in the module containing Capita's code and calling it as part of the sheet's startup just after it formats the gridlines.
Thanks to CAM from:
Cache (12th December 2013)
12th December 2013, 10:48 AM #3
Aye to be sure - it's in this thread Edit Excel Output
3 Thanks to vikpaw:
Cache (12th December 2013), CAM (12th December 2013), Steven_Cleaver (7th January 2014)
12th December 2013, 09:11 PM #4
Perfect, thanks both.
I hit another snag today in that there is a limit to the number of Sub Reports you can have within a report. Therefore, me wanting to extract and process the data automatically for 20 subjects means I can't do it (SIMS spit's it's dummy out with a varchar/numeric error after 11 subreports) unless someone can suggest a better way then basing it on the Student?
13th December 2013, 12:08 AM #5
What are you trying to achieve?
When you say sub reports you mean the sub query you can add by clicking the red icon on the right on data field choice screen?
You could dish your data out without filtering on subject and let Excel sort it for you. With a pivot table or otherwise. You can even populate custom ordering if that helps. Best to clone one of the Capita pivot reports to begin with.
There is an assessment / result focus as well isn't there? I forget, but student focus is probably easier.
13th December 2013, 08:24 PM #6
Basically, I want to take All Subject results for each student within the Autumn Result Set, take it out to excel, calculate whether they are currently achieving 5A*-C inc English + Maths, 5A*-C in any subject, C+ in Maths, C+ in English.
So what I setup was the report with Student Name and then using the Red Icon created a filter for each result which linked it to the correct aspect and the Autumn Result Set. I couldn't see anyway of pulling multiple Aspects through for a single result set and having come out into individual columns.
I've achieved it manually through a marksheet, excel and formulas, I just wanted it accessible to anybody at any time automatically. I've just found the Assessment Analysis book and missed the appenidx which means I might be able to do most of it in a marksheet, it's just a shame that if I can that it wouldn't be accessible to everybody at any time without granting an excess of permissions and huge raft of un-necessary information to people (where as running through the SIMS .net reporting it would be). I should have taken a screenshot of the broken report, it would probably explain it better, attached below
Last edited by Cache; 13th December 2013 at 08:38 PM.
14th December 2013, 11:09 AM #7
Definitely better off with a marksheet. You can either try to use formulae to calculate the 5A*-C and just mass replace the result sets for each year group when you clone the sheet or export it to Excel and do it manually.
Thanks to CAM from:
Cache (14th December 2013)
14th December 2013, 06:12 PM #8
I'll try marksheets then if I get chance, I just wish assessment was more flexible.
14th December 2013, 07:06 PM #9
Isn't this an area that Discover can handle. Or at least some of it.
15th December 2013, 03:02 PM #10
It could probably handle the %'ages but the last time I looked at it I think you had to reconfigure all of your Aspects in some way.
Maybe need to sit down and look at it again.
6th January 2014, 10:12 PM #11
- Rep Power
I do this in a marksheet - grade tally columns. I then alter column headings to say 'has achieved English' and then export and find/replace the 1s in the 'has achieved' columns for 'yes'. Gives me a non-data-person friendly output with absolutely no formulae is this what you mean or have I totally missed the point?
7th January 2014, 11:00 AM #12
That is what I meant and I have managed to do it in Assessment using a combination of Tally Columns and Nested If then Else statements for the colour coding however I would have still prefered to done this through the reporting dictionary so that anybody could access it at any time rather then having to have someone export the completed marksheet, unprotect it, remove some headings and the unnecessary shading and some other general tidying up every term. (I know I am being picky, however assessment does my head in!)
Last edited by Cache; 7th January 2014 at 11:01 AM.
7th January 2014, 11:16 AM #13
- Rep Power
I would recommend that you go on one of the Advanced Excel reporting courses that Capita run. You can configure some excellent reports based on the data in Assessment manager.
7th January 2014, 11:30 AM #14
Im going on the advanced reporting with excel course Wed & Thurs of this week. Hopefully i will be able to create some nice looking reports at the end of it!
7th January 2014, 02:22 PM #15
That thread i linked to earlier will make good presearch, especially the attachment.
Originally Posted by DSapseid
By Chunks_ in forum MIS Systems
Last Post: 25th August 2012, 01:39 PM
By GillNash in forum MIS Systems
Last Post: 6th December 2011, 01:00 PM
By projector1 in forum How do you do....it?
Last Post: 14th February 2007, 02:41 PM
By MrDylan in forum Windows
Last Post: 10th March 2006, 02:54 PM
By pinemarten in forum How do you do....it?
Last Post: 20th January 2006, 10:22 AM
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)