[sims] Advanced Reports in Excel

[Cache]

Evening all

 

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?

 

Thanks

[CAM]

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.

[vikpaw]

Aye to be sure - it's in this thread http://www.edugeek.net/forums/mis-systems/38519-edit-excel-output.html#post359319

[Cache]

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?

[vikpaw]

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.

[Cache]

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

Edited December 13, 2013 by Cache

[CAM]

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.

[Cache]

I'll try marksheets then if I get chance, I just wish assessment was more flexible.

[vikpaw]

Isn't this an area that Discover can handle. Or at least some of it.

[Cache]

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.

[CarpeDiem]

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?

[Cache]

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!) Edited January 7, 2014 by Cache

[number34]

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.

[DSapseid]

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!

[vikpaw]

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!

That thread i linked to earlier will make good presearch, especially the attachment.

[Cache]

It's annoying because I know what I want to do and how to do it, but SIMS isn't able to cope with it

 

I discovered later on that the report I was trying to run would never run because it had too many subreports (the screenshot I've attached here http://www.edugeek.net/forums/mis-systems/128768-advanced-reports-excel.html#post1101956) and just gave an error so a marksheet seems to be the only way to do it.

 

Unless those of you who have been on the advanced reporting course can suggest a better way of pulling out data of 25 Aspects using the standard SIMS reports?

[vikpaw]

You can pull data out of the result field we do it to make proof reading sheets. You can also filter by name and or resultset . A good naming convention helps.