Excel Report Template in SIMS

[landaufortecollege]

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

[laserblazer]

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.

[landaufortecollege]

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

[laserblazer]

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.

[TechMonkey]

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

[Dancer]

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.

[Hecate]

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

[Willzzz]

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?

[ChrisH]

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.

[Greg]

Download the advanced reporting course book from Capita site, takes you through this step by step

[simon37]

Posts here:

http://www.edugeek.net/forums/mis-systems/38519-edit-ecxel-output-2.html#post359947

 

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

[smiths75]

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

[Natashac15]

HI just wondering if someone is able to send me the ReportData.xls file as I can not seem to find this. I want to strat creating foprmulas/macros so when I run a report from SIMS all data will automatically work out percentages. If anyone also has any tips on this I would appreciate any help. Thank you.

My email is [email protected]