+ Post New Thread
Page 1 of 2 12 LastLast
Results 1 to 15 of 17
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 ...
  1. #1
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,305
    Thank Post
    487
    Thanked 190 Times in 184 Posts
    Blog Entries
    3
    Rep Power
    67

    Advanced Reports in Excel

    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

  2. #2
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Reigate Area, Surrey
    Posts
    4,423
    Thank Post
    884
    Thanked 416 Times in 316 Posts
    Blog Entries
    61
    Rep Power
    328
    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.

  3. Thanks to CAM from:

    Cache (12th December 2013)

  4. #3

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    Aye to be sure - it's in this thread Edit Excel Output

  5. 3 Thanks to vikpaw:

    Cache (12th December 2013), CAM (12th December 2013), Steven_Cleaver (7th January 2014)

  6. #4
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,305
    Thank Post
    487
    Thanked 190 Times in 184 Posts
    Blog Entries
    3
    Rep Power
    67
    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?

  7. #5

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    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.

  8. #6
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,305
    Thank Post
    487
    Thanked 190 Times in 184 Posts
    Blog Entries
    3
    Rep Power
    67
    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
    Attached Images Attached Images
    Last edited by Cache; 13th December 2013 at 08:38 PM.

  9. #7
    CAM
    CAM is offline

    CAM's Avatar
    Join Date
    Mar 2008
    Location
    Reigate Area, Surrey
    Posts
    4,423
    Thank Post
    884
    Thanked 416 Times in 316 Posts
    Blog Entries
    61
    Rep Power
    328
    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.

  10. Thanks to CAM from:

    Cache (14th December 2013)

  11. #8
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,305
    Thank Post
    487
    Thanked 190 Times in 184 Posts
    Blog Entries
    3
    Rep Power
    67
    I'll try marksheets then if I get chance, I just wish assessment was more flexible.

  12. #9

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    Isn't this an area that Discover can handle. Or at least some of it.

  13. #10
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,305
    Thank Post
    487
    Thanked 190 Times in 184 Posts
    Blog Entries
    3
    Rep Power
    67
    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.

  14. #11

    Join Date
    Mar 2012
    Posts
    68
    Thank Post
    24
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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?

  15. #12
    Cache's Avatar
    Join Date
    Apr 2008
    Location
    Cumbria
    Posts
    1,305
    Thank Post
    487
    Thanked 190 Times in 184 Posts
    Blog Entries
    3
    Rep Power
    67
    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.

  16. #13

    Join Date
    Sep 2006
    Location
    Reading
    Posts
    177
    Thank Post
    14
    Thanked 41 Times in 39 Posts
    Rep Power
    23
    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.

  17. #14
    DSapseid's Avatar
    Join Date
    Feb 2007
    Location
    West Sussex
    Posts
    1,155
    Thank Post
    131
    Thanked 54 Times in 47 Posts
    Rep Power
    38
    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!

  18. #15

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,956
    Thank Post
    775
    Thanked 1,487 Times in 1,234 Posts
    Rep Power
    367
    Quote Originally Posted by DSapseid View Post
    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.



SHARE:
+ Post New Thread
Page 1 of 2 12 LastLast

Similar Threads

  1. [SIMS] Sims Report in Word/Excel changes date format
    By Chunks_ in forum MIS Systems
    Replies: 10
    Last Post: 25th August 2012, 01:39 PM
  2. [SIMS] SIMs reports and Macros in Excel
    By GillNash in forum MIS Systems
    Replies: 12
    Last Post: 6th December 2011, 01:00 PM
  3. need to extract text from a string in excel
    By projector1 in forum How do you do....it?
    Replies: 7
    Last Post: 14th February 2007, 02:41 PM
  4. Replies: 3
    Last Post: 10th March 2006, 02:54 PM
  5. Web query in Excel
    By pinemarten in forum How do you do....it?
    Replies: 2
    Last Post: 20th January 2006, 10:22 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •