Lotus LearningSpace R5.01 Deployment Guide

256 Lotus LearningSpace R5.01 Deployment Guide

Figure 18-8 Reports permissions

The status of the Selected Reports permissions has been changed successfully.

18.3 Reporting technical aspects

If you are generating custom reports using Seagate Crystal Reports, it is a good

idea to familiarize yourself with the LearningSpace database structures. You

should pay particular attention to relationships between tables. In this section, we

list the tables and the relationship between them.

18.3.1 LearningSpace 5 reports and the database structure

Each report registered in the system is represented by a record in the REPORTS

table. Each report also has a corresponding record in the PERMS table, because

access to reports is restricted by permission. The report title is stored in the

PERMS table. LearningSpace makes no distinction between built-in reports

(those that are installed with LearningSpace) and custom reports (those that are

created by a LearningSpace user with the correct permissions).

Table 18-5 on page 253 lists the LearningSpace database tables.

Note: Even though Figure 18-8 shows that the status of the access to Reports

for the Administrators Profile is No, this Reports permission status is

overridden by the fact that the Run All Reports permission in the Reporter

permissions has been set to Yes for the Administrators profile.

Chapter 18. Reporting 257

Table 18-5 The LearningSpace database tables

The Crystal Reports Print Engine is the LearningSpace component that

generates most built-in reports. The REPORTOPS table is an adjunct to this

subsystem. REPORTOPS is a temporary repository of records that identify the

data in the USERS or COURSES table on which a given report is based (see

Table 18-6).

Table 18-6 The REPORTOPS table information

18.3.2 The LearningSpace 5.01 database tables

When creating reports, it is important to understand the relationships between

the tables in LearningSpace database. For progress reports, the relationships

between the users, progress, nodes, and courses tables are used to retrieve that

type of data. If a progress report is needed, listing the course and all activities

under that course, and the relationship between the NODES and NODEBR table,

is important. If a detail of a particular user profile is needed, then the relationship

between the UPINT, UPCATS, and the UPROFS tables is critical.

The LearningSpace database tables are as follows:

򐂰 The NODES table is where all node records are stored. A node record is an

element in the course tree (such as an activity in a course).

Table name Primary key(s) Comments

REPORTS PE_ID: A foreign key from the

PERMS table in which the

corresponding record identifies

the report to be run.

Each record specifies the data that the system

should gather when a person runs the report

identified by the PERMS record designated by

PE_ID.

PERMS PE_ID: LearningSpace generates

a unique integer identifier for this

field when you add the record, and

stores it in the GLOBALS table.

Each record defines a permission within

LearningSpace. When you define a user profile, the

Yes or No value that you assign to each of the

permissions defined in this table determines the

privileges for a user with that profile.

Table name Primary key(s) Comments

REPORTOPS 򐂰 RO_ID: Corresponds to S_SESSION in the

SEMAPHORES table.

򐂰 RO_VALUE: Corresponds to U_ID in the USERS

table or C_ID in the COURSES table, depending

on the report.

Note: This table occurs in LS 5.01, but not 5.0.

Each record identifies a record

in the USERS or COURSES

table participating in a Crystal

Reports report. All records are

deleted from this table when

the report is complete.

258 Lotus LearningSpace R5.01 Deployment Guide

򐂰 The NODEBR table stores the positions of nodes in the course. NODEBR

stands for Node Branch.

򐂰 The USERS table is where all LearningSpace users are stored.

򐂰 UPROFS stands for User Profiles. Each user profile is stored as a record in

this table. Each user profile also references a user profile category (in the

UPCATS table).

򐂰 The UPINT table stores the relationship between users and user profiles.

Figure 18-9 shows the relationship between these tables.

Figure 18-9 The relationship between the tables

An example of a select statement to retrieve the first name, last name, course

number, status, and score for a student with user ID of 859 for the course with

c_number of M100, is as follows:

SELECT u_f_name, u_l_name, c_number, p_status, p_score

FROM users, progress, nodes, courses

WHERE users.u_id = 859

AND users.u_id = progress.u_id

AND progress.p_node = nodes.n_id

AND courses.c_number = “M100”

The LearningSpace Administration Guide provides a full listing of the tables and

the relationship between them.

Users

u_id

u_f_name

u_l_name

u_number

.

.

.

Progress

p_node

p_status

p_score

u_id

.

.

.

Nodes

n_id

n_desc

n_type

.

.

.

Nodebr

n_id

nb_id

nb_order

nb_parent

.

.

.

Courses

c_id

c_number

.

.

.