+ Post New Thread
Results 1 to 12 of 12
MIS Systems Thread, Excel Report Template in SIMS in Technical; Afternoon First of all I am quite new to reporting in SIMS. I know how to create reports and can ...
  1. #1

    Join Date
    Apr 2009
    Location
    Derby
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    Question Excel Report Template in SIMS

    Afternoon

    First of all I am quite new to reporting in SIMS. I know how to create reports and can generally find the information I need and export it out but this has me stumped.

    I am trying to run a report that exports information into an Excel spreadsheet. The values that get exported need to be manipulated with formulas to generate the results we need. At the moment all I can do is select Standard Portrait or Standard Landscape in the Excel Settings area, but this generates a new speadsheet.

    Is there a way that I can create an Excel template that includes all these formulas, which SIMS uses to export the data to? That way I can include all the formulas and styling I need so the resulting spreadsheet is ready to be mail merged.

    I have noticed that there is a Report Template option, however there seems to be no documentation for this and I can't find any support for it anywhere.

    I might not have explained this very well so please let me know if you need any more info.

    Thanks for your help,
    James

  2. #2


    Join Date
    Sep 2007
    Location
    UK
    Posts
    5,426
    Thank Post
    1,437
    Thanked 880 Times in 565 Posts
    Rep Power
    645
    I don't think SIMS uses excel to do the calculations, it just loads the completed report into a worksheet. IIRC, old versions of SIMS could only create a csv file when running reports. I've just created a report to import into ReportAssist and I had to physically change the Year field from text to numeric in Excel.

  3. #3

    Join Date
    Apr 2009
    Location
    Derby
    Posts
    2
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by laserblazer View Post
    I don't think SIMS uses excel to do the calculations, it just loads the completed report into a worksheet.
    I understand that

    What I mean is that I would like to be able to export the report data into an existing spreadsheet (or a template) that contains formulas that I have previously setup. That way when the new data is imported the calculations automatically update.

    Does that make sense?

    Cheers,
    James

  4. #4


    Join Date
    Sep 2007
    Location
    UK
    Posts
    5,426
    Thank Post
    1,437
    Thanked 880 Times in 565 Posts
    Rep Power
    645
    Sill me I've not tried this but in 2007 if you go to Data and From Other Sources - From Microsoft Sources, there's an Excel option there that might do it.

  5. #5

    TechMonkey's Avatar
    Join Date
    Dec 2005
    Location
    South East
    Posts
    3,287
    Thank Post
    225
    Thanked 405 Times in 302 Posts
    Rep Power
    162
    Not tried it but I am pretty sure you can use your own template as a basis for a report so try that. The only problem I have ever found is that Calculations seem to be turned off on exported SIMs spreadsheets. Although this could be set on just the SIMs blank spreadsheet template

  6. #6
    Dancer's Avatar
    Join Date
    Mar 2009
    Location
    Norfolk
    Posts
    26
    Thank Post
    2
    Thanked 6 Times in 5 Posts
    Rep Power
    13
    The only way I have sucessfully managed to create my own excel templates in SIMS is to copy the version sims use, rename it and ammend it for my own ends.
    on my machine in
    C:\Program Files\SIMS\SIMS .net\ReportData.xls
    and rename it say - ReportDataMyTemplate.xls

    As I understand it takes the result of the query in sims and using a macro opens a new excel spreadsheet into which it copies the data and applies the appropriate formatting.

    I usually add my own own function after the fixaddresscolumn in the Auto_Open sub. May need to move it around depending on what want to do.

    You could either get the macro to do all the relevent calcualtions and add the data to an exsting spreadsheet.

  7. #7

    Join Date
    Feb 2006
    Location
    South Cumbria
    Posts
    199
    Thank Post
    26
    Thanked 29 Times in 24 Posts
    Rep Power
    22
    I've used this method for creating templates which use pivot tables so I guess it could be used for other calculations.

    Create and run a SIMS excel report which contains your base data
    In the excel report created, record a macro which performs the data manipulation
    Make a copy of the reportdata.xls file, give it a suitable name and edit the macro code to include the code you recorded
    Go back to your SIMS report designer, in output select report template and point it at your file

    There was a KB article which explained it in detail but I can't find it now

  8. #8

    Join Date
    Feb 2008
    Posts
    23
    Thank Post
    3
    Thanked 4 Times in 4 Posts
    Rep Power
    14
    In my version I don't have this file, I only have a file called ExcelStandardList.xls which doesn't contain any macros at all. Can anyone link a copy of ReportData.xls please?

  9. #9
    ChrisH's Avatar
    Join Date
    Jun 2005
    Location
    East Lancs
    Posts
    5,009
    Thank Post
    120
    Thanked 282 Times in 260 Posts
    Rep Power
    108
    That file for me only appears as a temp file in my home dir then disappears when the information is merged into the new spreadsheet.

  10. #10

    Join Date
    Jan 2007
    Posts
    100
    Thank Post
    0
    Thanked 34 Times in 23 Posts
    Rep Power
    21
    Download the advanced reporting course book from Capita site, takes you through this step by step

  11. #11

    Join Date
    May 2007
    Posts
    13
    Thank Post
    4
    Thanked 1 Time in 1 Post
    Rep Power
    0
    Posts here:
    http://www.edugeek.net/forums/mis-sy...tml#post359947

    discuss an example, offer a 'how to' doc and give some code.

  12. #12

    Join Date
    Nov 2009
    Location
    Leeds
    Posts
    5
    Thank Post
    0
    Thanked 0 Times in 0 Posts
    Rep Power
    0

    SIMS Advanced Reporting

    Quote Originally Posted by Greg View Post
    Download the advanced reporting course book from Capita site, takes you through this step by step
    Hi Greg

    I have looked on the CAPITA web site for the Advanced Reoprting course book but I am unable to find it . Do you have a copy which you could amil to me?

    Regards
    Steve

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] Sims Lesson / Attendance Report
    By IrritableTech in forum MIS Systems
    Replies: 10
    Last Post: 4th May 2012, 01:38 PM
  2. SIMS user permissions report
    By User3204 in forum MIS Systems
    Replies: 14
    Last Post: 13th November 2009, 12:52 PM
  3. Sims report help
    By Jamie_a in forum MIS Systems
    Replies: 1
    Last Post: 4th October 2008, 04:34 PM
  4. Sims report Template
    By imiddleton25 in forum MIS Systems
    Replies: 1
    Last Post: 27th June 2008, 05:08 PM
  5. Another Sims Report
    By Jamie_a in forum MIS Systems
    Replies: 2
    Last Post: 29th January 2008, 04:58 PM

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Posting Permissions

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