Build custom reports from Adobe Connect database

Adobe Connect uses a database to store information about users, content, courses, and meetings. User activity populates the database. You can use tools such as Adobe® ColdFusion® Studio and Business Objects Crystal Reports to query star schema views and view the data. You can also use SQL-based tools such as SQL Query Analyzer.

The following Adobe Connect applications can output data to reports:

Meeting attendance, meeting duration, and meeting content.

Content views, slide views, and presentation views.

Course management information such as course attendee statistics, content viewing statistics, and quiz results.

SCO fact

SCO details dimension

Activity fact

Activity details dimension

Curriculum test outs dimension

Curriculum prerequisites dimension

Curriculum completion requirements dimension

Slide views fact

Slide views details dimension

Answers fact

Answer details dimension

Question fact

Question details dimension

Question responses dimension

Groups dimension

User groups dimension

User dimension

Custom field names dimension

Cost centers dimension

Building custom reports from legacy database views

Adobe Connect uses a database to store information about users, content, courses, and meetings. User activity populates the database. You can use tools such as Business Objects Crystal Reports to query the database and view the data. You can also use SQL-based tools such as SQL Query Analyzer.

The following Adobe Connect applications can output data to reports:

Meeting attendance, meeting duration, and meeting content.

Content views, slide views, and presentation views.

Course management information such as course attendee statistics, content viewing statistics, and quiz results.

View relationships between database views

To see the relationships between the database views, use a diagramming tool that connects to your database.

The following are not supported:

• views that are not identified in this document
• altering the views that are identified in this document
• direct access to the underlying database schema

EXT_TRANSACTIONS

A unique transaction ID is generated each time a user interacts with an object. The EXT_TRANSACTIONS view returns the data listed in the following table:

The following query returns the data in the following table:

The EXT_TRANSACTIONS view returns all existing transactions for a given user and training session. To view the latest transaction, check the maximum DATE_VISITED value.

You can filter on the STATUS and URL fields to get a list of passing users for a specific training session, for example:

User actions that generate data in this view:

• Attending a meeting

• Viewing a piece of content

• Taking a training session (a course or curriculum)

• Certificate number, which does not exist in the database

• Maximum score, which is often unavailable

EXT_TRANSACTIONS_VIEWS

The EXT_TRANSACTIONS_VIEWS view retrieves data about the slides or pages that users view.

The following query returns the data in the following table:

Data is generated in this view whenever a user views content or a training session.

EXT_USERS

The EXT_USERS view lists users and associated profile attributes:

The following query returns the data in the following table:

Data is updated in this view whenever a guest or user is created, updated, or deleted.

• Password, which is not stored in plain text.

• Time zone and language, which are not available in human readable form. For example, PST is 323.

• Last login, as it is resource intensive to calculate. Instead, use a max(date_visited) query from the EXT_TRANSACTIONS view to retrieve this data.

• Active session, which is data from the EXT_TRANSACTION view. Instead, use a STATUS='IN-PROGRESS' query to retrieve this data.

• Deleted users do not appear in the EXT_USERS view. Deleted users continue to appear in the EXT_TRANSACTION view.

• Data on groups is not included in this view.

• Data on new and pre-defined user custom fields. This information is available for each user in the EXT_USER_FIELDS view.

EXT_USER_FIELDS

The EXT_USER_FIELDS view lists new and predefined custom fields for a specific user. It also lists custom fields for users who are converted to guests.

The following query returns the data in the following table:

Actions that generate data in this view: adding, creating, or updating new or predefined custom fields for one or more users.

EXT_USER_GROUPS

The EXT_USER_GROUPS view lists data about groups and associated group members. The EXT_USER_GROUPS view uses the data listed in the following table:

The following query returns the data in the following table:

Nesting of multiple groups is supported in version 5.1 and later. For example, if group A contains group B, and you are in group B, you are listed as a member of A.

Built-in groups, like the Administrators group, use code names in the schema, as in the following SQL query: SELECT * FROM EXT_USER_GROUPS where group='{admins}. The code name distinguishes built-in groups from user-defined groups.

User actions that generate data in this view:

• Creating, updating, or deleting a group

• Changing group membership

EXT_OBJECTS

The EXT_OBJECTS view lists all system objects (such as meetings, content, courses) and their attributes.

The following SQL query returns the data in the following table:

You can get all objects of a specific type by filtering on the TYPE field. For example, the following SQL query filters for courses and curriculums:

Use the following SQL query to return a list of available system types:

User actions that generate data in this view:

• Creating or updating a meeting, course, or curriculum

• Uploading or updating content

• Duration, which you can use date_end- date_begin to calculate.

• Size on disk, which exposes business rules regarding copies versus originals

• Folder ID

• Deleted objects do not appear in the EXT_OBJECTS view. Deleted objects do exist in the EXT_TRANSACTION view.