Using the Analysis Database to Create User Configurable Reports

This chapter provides an overview of the analysis database and discusses how to:

Click to jump to parent topicUnderstanding the Analysis Database

The analysis database integrates the power of the academic advisement transcript with a large number of student data result tables.

On the Request Header page, one of the field values for Report Format is Analysis Database. The analysis database report format is a special report format that is computer-friendly rather than people-friendly. The report is written in computer-readable format, which enables you to write application programs against the analysis database tables. When the value in the Report Format is Analysis Database, the report data is written to and stored in the analysis database tables. Using this database, you can then prepare user-configurable reports using your reporting tool of choice.

More information is stored in the analysis database tables than appears in the printed report formats. The database tables contain keys for each object in the report that an application program can use to reference other objects in the student's record that are not contained in the printed report. The analysis database contains data on courses that were considered for satisfying a requirement, but rejected. It also contains information on courses captured by global limits.

You can reuse the data stored in these academic advisement analysis tables. You can query the tables for data. To maximize performance, avoid overly complex joins. In most cases, performance severely degrades when more than five tables are joined in one SQL statement. These tables do not automatically purge and must be updated to remain current. Your database administrator must write a script to purge the files. The table data is only accurate for the date on which you run the report. If multiple processes of the same transcript type for the same student occur on a single day, the system uses the analysis database sequence number to keep the data separate for each report.

There are several things you can do to maximize performance when writing reports against the analysis database. When using Crystal reports, create a temporary table where the data to be reported is denormalized into one table. Then use one SQL statement to retrieve the data in a sorted order, using breaks on the data to control the output. Do this to overcome the performance degradation that occurs when using multiple Crystal subreports in one report. You can improve performance by creating indices on the analysis database tables, so joins are on the indices. Setting the initial size of the tables correctly also improves performance. Your database administrator can perform this task. If the tables are not sized correctly, performance could quickly degrade as the tables grow.

Click to jump to parent topicPopulating the Analysis Database Tables

You can populate the analysis database tables by running the transcript request process for a single student or small group of students (through the Transcript Request component), or for a large population of students (through the Batch Transcript Request component).

Here's how to populate the analysis database tables for a small group of students:

  1. Access the Request Header page.

  2. Specify an advising transcript type in the Transcript Type field.

  3. Specify a report type of Analysis Database in the Report Type field.

    Running the transcript process using this option sends the results of the advising report to the analysis database tables, instead of to a flat, printable, advising report.

  4. Access the Request Detail page and enter the student IDs for the processing group.

  5. (Optional) Enter what-if parameters.

  6. If you have a small number of IDs to process, click the Process Request button to process the request and populate the analysis database tables.

    If you have a large number of IDs to process, save the request, note the request number, and then run the process through the Transcript Generation component.

Here's how to populate the analysis database tables for a large group of students (batch mode):

  1. Access the Batch Transcript Request page.

  2. Specify an advising transcript type in the Transcript Type field.

  3. Select the Database Report check box.

  4. Specify the parameters for the processing group.

  5. Click the Run button to process the request and populate the analysis database tables.

See Also

Producing Academic Advisement Transcript Reports

Processing Batch Transcripts (Application Engine)

Click to jump to parent topicPrinting the Contents of the Analysis Database

Use the Database Tables Report component to retrieve all of the data in each academic advisement table for a specific student or group of students on a specific date. Before creating a user configurable report, you can run this report to view the data that is available for the student. On any specific day, you can run one standard and one special report for a student.

Click to jump to top of pageClick to jump to parent topicPage Used to Print the Contents of the Analysis Database

Page Name

Definition Name

Navigation

Usage

Database Tables Report

RUNCTL_SRDBTBLS

Academic Advisement, Advising Reports, Database Tables

Enter the parameters to capture the student population on which you want to report, and process the report.

Click to jump to top of pageClick to jump to parent topicProcessing the Database Tables Report

Access the Database Tables Report page (Academic Advisement, Advising Reports, Database Tables).

As of Date

The Database Tables report accurately reflects the contents of each academic advisement table for the designated student as of this date. The current date is the default field value, but you can modify it. You must enter a value in this field.

ID

Enter the student's ID or leave blank to return all of the values.

Report Type

Select the report type. Values include:

SPC: Indicates a report with a special requirement usage.

STD: Indicates a standard advising report.

Note. The Database Tables report is not as robust as the analysis database tables themselves (where multiple reports for a student in a single day are organized by an analysis database sequence number). If you process multiple reports for a student with the same report type on a single day, all of the data for the day appears on the Database Tables report.

Click to jump to parent topicCreating a User-Defined Advisement Report Using the Analysis Database

You may want to query the analysis database for many reasons. For example, you may want to know how many undergraduate students have not satisfied their math requirement. This data may even help you decide how many sections of a class to schedule in a subsequent term. Or, you may want to create a report that displays the career, program, and plan of all students who have met all their degree requirements.

You can query the analysis database using PeopleSoft Query or your database's query tool such as Microsoft's Query Analyzer, Oracle's SQL Plus, or IBM's Command Center.

Note. Pay close attention to the automatic join created by PeopleSoft Query. When joining certain tables, the query tool creates the join using the ENTRY_SEQ field. Leaving this field in the join may result in no data being returned.

After retrieving data, use your reporting tool of choice to create a report that meets your needs. To do this, you can use most tools that coordinate with SQL.

Click to jump to parent topicReviewing the Academic Advisement Analysis Database Tables

At a minimum, tables from the analysis database always contain these column headings: ID, RPT_DATE, RPT_TYPE, and ANALYSIS_DB_SEQ.

The following describes the table R_REPORT:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

WHAT_IF_FL

  

Char

1

What-If Flag

REPORT_REQUEST_NBR

  

Char

9

Report Request Nbr

INSTITUTION

  

Char

5

Academic Institution

TSCRPT_TYPE

  

Char

5

Transcript Type

The following describes the table, R_ORDER:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

ENTRY_R_TYPE

  

Char

6

Valid Types: Career ('KEYCAR'), Program ('KEYPRG'), Plan ('KEYPLN'), Sub-Plan ('KEYSUB'), Requirement Group ('KEYRQG'), Requirement ('KEYREQ'), and Requirement Line ('KEYRQL').

The following describes the table, R_RQRMNT_GROUP:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

RQRMNT_GROUP

Y

Char

6

  

RQ_DATE

  

PSDATE

  

Effective Date of Requirement Group

ENTRY_R_STATUS

  

Char

4

  

PARTITION_FAIL

  

Char

1

Partition Fail Flag

The following describes the table, R_RG_LINK:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

The following describes the table, R_TRANSCRIPT:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

CRSE_TAG

Y

Char

4

  

INSTITUTION

  

Char

5

  

SRC_INSTITUTION

  

Char

5

  

ACAD_CAREER

  

Char

4

  

SRC_CAREER

  

Char

4

  

CRSE_CAREER

  

Char

4

  

STRM

  

Char

4

Term

APPLIED_TERM

  

Char

4

  

TERM_TAKEN

  

Char

4

  

CLASS_NBR

  

Char

5

  

STDNT_ENRL_STATUS

  

Char

2

  

UNT_TAKEN

  

Decimal

3.2

  

UNT_PRGRSS

  

Decimal

3.2

  

GRADING_BASIS_ENRL

  

Char

3

  

CRSE_GRADE_OFF

  

Char

3

  

REPEAT_CODE

  

Char

4

  

ASSOCIATED_CLASS

  

Smallint

4

  

AUDIT_GRADE_BASIS

  

Char

1

  

EARN_CREDIT

  

Char

1

  

INCLUDE_IN_GPA

  

Char

1

  

UNITS_ATTEMPTED

  

Char

1

Valid Choices: In Progress ('I') and Attempted ('Y').

GRADE_POINTS

  

Decimal

6.3

  

CRSE_ID

  

Char

6

  

CRSE_OFFER_NBR

  

Char

2

  

SESSION_CODE

  

Char

3

  

CLASS_SECTION

  

Char

4

  

ACAD_GROUP

  

Char

5

  

SUBJECT

  

Char

8

  

CATALOG_NBR

  

Char

10

  

DESCR

  

Char

30

  

COMPONENT

  

Char

3

  

CRS_TOPIC_ID

  

Decimal

3

  

EQUIV_CRSE_ID

  

Char

5

  

OVRD_CRSE_EQUIV_ID

  

Char

1

  

START_DT

  

PSDATE

    

END_DT

  

PSDATE

    

CRSE_COUNT

  

Decimal

2.2

  

CLASS_ENRL_TYPE

  

Char

1

  

RQ_SCHOOL_TYPE

  

Char

4

  

UNT_EARNED

  

Decimal

3.2

  

The following describes the table, R_SUBSTITUTION:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

CRSE_TAG

Y

Char

4

  

ACAD_GROUP

  

Char

5

  

CRSE_ID

  

Char

6

  

SUBJECT

  

Char

8

  

CATALOG_NBR

  

Char

10

  

DESCR

  

Char

30

  

OPRID

  

Char

8

  

DESCR254A

  

Char

254

  

The following describes the table, R_REQUIREMENT:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

REQUIREMENT

Y

Char

9

  

RQ_DATE

  

PSDATE

  

Effective Date of Student's Requirement

ITEM_R_STATUS

  

Char

4

  

PARTITION_FAIL

  

Char

1

Partition Fail Flag

The following describes the table, R_RQRMNT_LINE:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

REQUIREMENT

Y

Char

9

  

RQ_LINE_NBR

Y

Char

4

  

ITEM_R_STATUS

  

Char

4

  

DISP_SELECT_LINE

  

Char

1

  

REQ_LINE_TYPE

  

Char

3

  

The following describes the table, R_CAREER:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

ACAD_CAREER

Y

Char

4

  

CAR_DATE

  

PSDATE

    

ITEM_R_STATUS

  

Char

4

  

The following describes the table, R_ACAD_PROG:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

ACAD_PROG

Y

Char

5

  

ACAD_CAREER

  

Char

4

  

ITEM_R_STATUS

  

Char

4

  

The following describes the table, R_ACAD_PLAN:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

ACAD_PLAN

Y

Char

10

  

ACAD_CAREER

  

Char

4

  

ACAD_PROG

  

Char

5

  

ITEM_R_STATUS

  

Char

4

  

The following describes the table, R_ACAD_SUBPLAN:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

ACAD_SUB_PLAN

Y

Char

10

  

ACAD_CAREER

  

Char

4

  

ACAD_PROG

  

Char

5

  

ACAD_PLAN

  

Char

10

  

ITEM_R_STATUS

  

Char

4

  

The following describes the table, R_COURSE_USEAGE:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

REQUIREMENT

Y

Char

9

  

RQ_LINE_NBR

Y

Char

4

  

CRSE_TAG

Y

Char

4

  

SEL_PROCESS_TYPE

  

Char

3

Valid Types: Verify ('VER'), Sequence ('SEQ'), Limit ('LIM'), and Standard ('STD').

SEL_MODE

  

Char

1

Valid Modes: Selected ('S'), Not Selected ('N'), and Excluded ('X').

IN_PROGRESS_GRD

  

Char

1

In Progress Flag: In Progress ('Y').

The following describes the table, R_COND_USEAGE:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

REQUIREMENT

Y

Char

9

  

RQ_LINE_NBR

Y

Char

4

  

CONDITION_CODE

Y

Char

3

  

CONDITION_OPERATOR

Y

Char

2

  

CONDITION_DATA

Y

Char

10

  

The following describes the table, R_UNIT_CRSE_GPA:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

UNITS_REQUIRED

  

Decimal

4.2

  

UNITS_NEEDED

  

Decimal

4.2

  

CRSES_REQUIRED

  

Decimal

4.2

  

CRSES_NEEDED

  

Decimal

4.2

  

GPA_REQUIRED

  

Decimal

3.3

  

GPA_ACTUAL

  

Decimal

3.3

  

The following describes the table, R_DESCRIPTION:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

DESCR_LN_NBR

Y

Smallint

    

DESCR254

  

Char

254

  

The following describes the table, R_OVERRIDE_RG:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

RQ_RG_OVRD_ACTION

  

Char

4

  

OLD_UNITS

  

Decimal

4.2

  

NEW_UNITS

  

Decimal

4.2

  

OLD_CRSES

  

Decimal

4.2

  

NEW_CRSES

  

Decimal

4.2

  

REF_RQMT_GROUP

  

Char

6

  

OPRID

  

Char

30

  

DESCR254A

  

Char

254

  

The following describes the table, R_OVERRIDE_RQ:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

REQUIREMENT

  

Char

9

  

RQ_OVRD_ACTION

  

Char

4

  

OLD_UNITS

  

Decimal

4.2

  

NEW_UNITS

  

Decimal

4.2

  

OLD_CRSES

  

Decimal

4.2

  

NEW_CRSES

  

Decimal

4.2

  

REF_REQUIREMENT

  

Char

9

  

OPRID

  

Char

30

  

DESCR254A

  

Char

254

  

The following describes the table, R_OVERRIDE_LN:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

REQUIREMENT

Y

Char

9

  

RQ_LINE_NBR

Y

Char

4

  

RQ_LN_OVRD_ACTION

  

Char

4

  

OLD_MIN_UNITS

  

Decimal

4.2

  

NEW_MIN_UNITS

  

Decimal

4.2

  

OLD_MAX_UNITS

  

Decimal

4.2

  

NEW_MAX_UNITS

  

Decimal

4.2

  

OLD_MIN_CRSES

  

Decimal

4.2

  

NEW_MIN_CRSES

  

Decimal

4.2

  

OLD_MAX_CRSES

  

Decimal

4.2

  

NEW_MAX_CRSES

  

Decimal

4.2

  

OPRID

  

Char

30

  

DESCR254A

  

Char

254

  

The following describes the table, R_COURSE_DIREC:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

REQUIREMENT

Y

Char

9

  

RQ_LINE_NBR

Y

Char

4

  

CRSE_TAG

Y

Char

4

  

OPRID

  

Char

30

  

DESCR254A

  

Char

254

  

The following describes the table, R_RQDPLN_HEADER:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

PLANS_NEEDED

  

Smallint

    

The following describes the table, R_RQDPLN_DETAIL:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ENTRY_SEQ

Y

Smallint

    

RQRMNT_GROUP

Y

Char

6

  

ACAD_CAREER

Y

Char

4

  

ACAD_PROG

Y

Char

5

  

ACAD_PLAN

Y

Char

10

  

ACAD_SUB_PLAN

Y

Char

10

  

The following describes the table, R_WHAT_IF:

Column Name

Key

Data Type

Length

Comments

ID

Y

Char

11

Student's ID

RPT_DATE

Y

PSDATE

  

Report Date

RPT_TYPE

Y

Char

4

Report Type

ANALYSIS_DB_SEQ

Y

Smallint

  

Report Type Sequence

ACAD_CAREER

  

Char

4

  

ACAD_CAREER_OVRD

  

Char

4

  

CAR_REQ_TERM

  

Char

4

  

ACAD_PROG

  

Char

5

  

ACAD_PROG_OVRD

  

Char

5

  

ACAD_PROG_TERM

  

Char

4

  

ACAD_PLAN

  

Char

10

  

ACAD_PLAN_OVRD

  

Char

10

  

ACAD_PLAN_TERM

  

Char

4

  

ACAD_SUB_PLAN

  

Char

10

  

ACAD_SUB_PLAN_OVRD

  

Char

10

  

ACAD_SUB_PLAN_TERM

  

Char

4