SQL Queries

Topics: User Forum
Dec 20, 2006 at 12:11 AM
Greetings all,

I'm trying to find someone who can help me write a few SQL queries so I can deploy and test the reporting services functionality with the SLK.

All I really need to find is a query for:
Completion Status by name and date
Correct and Incorrect respnses by name and course

Can someone assist?
Dec 21, 2006 at 7:53 AM
Sounds interesting!

Some info to get started,

> Completion Status by name and date
The main tables to look at are 'LearnerAssignmentItem' and 'AttemptItem'. Joining these two will result in a list of every learner's assignment with Completion Status information. You could join these with the 'User' table and the 'Assignment' table to get the learner's name information and assignment information like date, respectively. Remember to perform an outer join on LearnerAssignmentItem as the AttemptItem may not exist until the assignment is attempted

> Correct and Incorrect responses
Join of the following four tables, 'LearnerAssignmentItem', 'AttemptItem', 'ActivityAttemptItem' and 'InteractionItem' will result in a list of all user's assignments and all corresponding responses.

You may want to checkout the documentation that comes with the SDK release. All the tables and the information they hold are documented in the SDK (under 'SLK+MLC Schema'). You might discover other interesting items to report on.
Dec 21, 2006 at 4:33 PM
Thank you very much!

One final question: do you have any suggestions for other reports? Is there anything in the basic SharePoint databases which would be helpful to pull in alongside the course reporting?
Dec 21, 2006 at 8:54 PM

If ou feel so inclined, could you post the queries you used for these two reports? I'm having trouble getting it to function correctly...
Jan 12, 2007 at 10:13 PM
One more question:

I can't seem to find the actual question text anywhere in the data. I can see the question id. Is it not captured?
Jan 15, 2007 at 11:26 PM

The question text is captured in the 'Description' field of the InteractionItem table. However, it is upto the content to set this information (in the case of SCORM content). This is also the case with some of the other SCORM data model elements and the reporting systems should in general be wary of what data is set and what is not by the instructional material used in the application.