[SIMS] Individual Reports, Teacher Names & Result History?

[LosOjos]

Hi all,

 

as I'm sure some of you have noticed, SIMS Individual Reports do not play nice with teacher names if more than one teacher is assigned to a class, it seems to pick one of their names to display with no apprent way of defining which this is.

 

Because of this, I've resorted to creating a "report author" aspect, then after each set of reports is written in AM7, I work my way through the marksheets checking the result history to see which teacher actually wrote the report, then writing their name in to the author column ready to publish on the reports.

 

It's quite a long winded process!

 

My question to you all is, does anybody know of a way to pull through that information (result history operator name) in to an individual report? Or alternatively, how to create a report that contains this information so I can import it back in to the relevant aspects?

 

I'm open to any other suggestions you may have too...

 

Any help, as always, will be much appreciated.

Edited May 28, 2010 by LosOjos

[vikpaw]

We have this issue too!

 

We ask either the teacher writing the report to fill in their name, or ask the Head of Dept. to do this. I would advise that is the best way forward, as you doing it, or finding long winded workarounds is just not practical.

 

Otherwise, put the onus on the timetabler to try and sort it out, by splitting groups, and making linked classes, and whatever other tricks are available.

 

Having said that, i've used some sql to extract information out of the database before which may be of use to you. You'll need to change some parts to fit in with what you want. I used it mostly to see the history of a particular aspect to see who changed what and when. I've added in the link to show resultset which i imagine would be a better way for you to use the code rather than on aspect name. You then wouldn't need the result_date part.

 

I know the whole lot really should be done by lots of table joins, but i just botched it together quickly at the time and can't easily get my head around multiple table joins. For this reason it wont extract the user's forename / surname, just their login ID which may be enough for you (?).

 

You may want to run a separate query to extract names of user agaist loginname, and ptu it in a lookup table.

 

Oh yeah, i do this on my VM, using a backup of live data, naturally ;)

 

Hope it may be of some use

 

[size=2][size=2][color=#0000ff][size=2][color=#0000ff]use[/color][/size][/color][/size][size=2] sims2005[/size][size=2][color=#808080][size=2][color=#808080];[/color][/size]
[/color][/size][size=2][color=#0000ff][size=2][color=#0000ff]select[/color][/size][/color][/size]
[size=2]result_date[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size][/color][/size][size=2] result[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size]
[/color][/size][size=2]resultset_name[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size]
[/color][/size][size=2]aspect_name[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size]
[/color][/size][size=2]forename[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size][/color][/size][size=2] surname[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size]
[/color][/size][size=2]login_name[/size]
[size=2][color=#0000ff][size=2][color=#0000ff]from[/color][/size][/color][/size][size=2] sims[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]asm_result [/size][size=2][color=#0000ff][size=2][color=#0000ff]AS[/color][/size][/color][/size][size=2] r1[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size][/color][/size]
[size=2]sims[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]db_user [/size][size=2][color=#0000ff][size=2][color=#0000ff]AS[/color][/size][/color][/size][size=2] u1[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size][/color][/size]
[size=2]sims[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]asm_aspect [/size][size=2][color=#0000ff][size=2][color=#0000ff]AS[/color][/size][/color][/size][size=2] a1[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size][/color][/size]
[size=2]sims[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]asm_resultset [/size][size=2][color=#0000ff][size=2][color=#0000ff]AS[/color][/size][/color][/size][size=2] rs1[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size]
[/color][/size][size=2]sims[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]sims_person [/size][size=2][color=#0000ff][size=2][color=#0000ff]AS[/color][/size][/color][/size][size=2] s1[/size]
[size=2][color=#0000ff][size=2][color=#0000ff]where[/color][/size][/color][/size][size=2] r1[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]student_id [/size][size=2][color=#808080][size=2][color=#808080]=[/color][/size][/color][/size][size=2] s1[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]person_id [/size]
[size=2][color=#808080][size=2][color=#808080]AND[/color][/size][/color][/size][size=2] r1[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]aspect_id [/size][size=2][color=#808080][size=2][color=#808080]=[/color][/size][/color][/size][size=2] a1[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]aspect_id [/size]
[size=2][color=#808080][size=2][color=#808080]AND[/color][/size][/color][/size][size=2] r1[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]resultset_id [/size][size=2][color=#808080][size=2][color=#808080]=[/color][/size][/color][/size][size=2] rs1[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]resultset_id[/size]
[size=2][color=#808080][size=2][color=#808080]AND[/color][/size][/color][/size][size=2] aspect_name [/size][size=2][color=#808080][size=2][color=#808080]LIKE[/color][/size][/color][/size][size=2][color=#ff0000][size=2][color=#ff0000]'_temp%'[/color][/size][/color][/size]
[size=2][color=#808080][size=2][color=#808080]AND[/color][/size][/color][/size][size=2] operator_id [/size][size=2][color=#808080][size=2][color=#808080]=[/color][/size][/color][/size][size=2] u1[/size][size=2][color=#808080][size=2][color=#808080].[/color][/size][/color][/size][size=2]person_id [/size]
[size=2][color=#808080][size=2][color=#808080]AND[/color][/size][/color][/size][size=2] result_date [/size][size=2][color=#808080][size=2][color=#808080]>[/color][/size][/color][/size][size=2][color=#ff00ff][size=2][color=#ff00ff]DATEADD[/color][/size][/color][/size][size=2][color=#808080][size=2][color=#808080]([/color][/size][/color][/size][size=2][color=#ff00ff][size=2][color=#ff00ff]day[/color][/size][/color][/size][size=2][color=#808080][size=2][color=#808080],-[/color][/size][/color][/size][size=2]3[/size][size=2][color=#808080][size=2][color=#808080],[/color][/size][/color][/size][size=2][color=#ff00ff][size=2][color=#ff00ff]GETDATE[/color][/size][/color][/size][size=2][color=#808080][size=2][color=#808080]())[/color][/size]
[/color][/size][size=2][color=#0000ff][size=2][color=#0000ff]order[/color][/size][/color][/size][size=2][color=#0000ff][size=2][color=#0000ff]by[/color][/size][/color][/size][size=2] result_date [/size][size=2][color=#0000ff][size=2][color=#0000ff]desc[/color][/size][/color][/size][size=2][color=#808080][size=2][color=#808080],[/color][/size][/color][/size][size=2] surname [/size][size=2][color=#0000ff][size=2][color=#0000ff]asc[/color][/size]
[/color][/size][/size]

[Bev]

I just added teacher name to the marksheet - not clever but it does the trick.

[LosOjos]

Thanks both, at the minute I'm just going to stick with entering the names manually in an aspect I think, but your SQL query is interesting vikpaw, I'll have a bit more of a play around with it when I have some spare time