ClearCompany Learning: Custom Reports

Custom Reports allows organizations to run custom reports on additional data they would like to analyze. In this article, we dive into custom reports and data available.

In this article:
Custom Reports Overview
Custom Reports Types
Custom Reports Data Available
Create a Custom Report

Custom Reports Overview

Custom Reports can be accessed by SuperAdmin users and the rights associated with Custom Reports. Custom Reports allow users to export all the reports into a .CSV file and further customize as needed. 

• Custom Reports can only be seen by the author who has created them.
• Custom Reports can be cloned to send to additional users, data that will be displayed will depend on the user's Rights.
• Custom Reports can be set up to export on a schedule; daily, monthly, weekly, quarterly, semiannually, and annually. 

Custom Reports Types

Activity Report The Activity Report allows a report to be tailored based on training activities in the system. Activities for any of the Users/groups in the system can be generated. Filters include the list of objects that have been chosen for specific tags, User status, or object status.	Assignment Report The Assignment Report allows a report to be tailored based on activity for any Users/groups and objects that are associated with assignment batches. The Assignment Report will be generated and will include applicable Users, groups, groupings, and columns that have been selected. The Assignment Report allows the “not started” and “overdue” status for assignments to be included. Dates selected only pertain to assignment creation dates. The report includes an overdue column which will display “Y” if the training was not completed by the due date.	User Type Report The User Type Report is an export of the Users within the system. Data displayed is from the User profile, which can include user auxiliary fields.
Object Report The Object Report allows specific objects to be selected and run in a report. Dates selected only pertain to object creation dates.	Quiz Report The Quiz Report runs an export of data for a specific quiz. The Quiz Report will display the answers for each question in the quiz for the Users/groups selected, in addition to any columns selected.	Survey Report The Survey Report runs an export of data for a specific survey. The Survey Report will display all the answers for each question in the survey for the Users/groups selected in addition to any columns selected.
Object Status Matrix The Object Status Matrix report creates a table where each object selected is a column and each user selected is a row. The report displays each users' current status with each object (if the object is completed it displays a timestamp). Users can select up to 20 objects for the report.	Batch Report The Batch Report will display users and their status with certain batches. Users will see batch names and status as well as user batch status.	Assessment Report An assessment report will show every person that has taken the assessment. The Assessment Report will display every question within the assessment and the score that goes along with that question. The report also includes columns that display section scores added up and total points for the entire assessment. The report will also display if/who assessed each person.

Custom Report Data Available

User Fields

Field	Action	Description
added_on	users	The date the user was added to the system
badges_earned	users	Displays the number of badges the user has earned
barcode_format	users	Displays the barcode format for the user (add-on)
barcode_value	users	Displays the numerical barcode values for the user (add-on)
email	users	User email address
expires_on	users	The date the user's access to the LMS will expire
first_name	users	First name of user
gmt_offset	users	Displays the user timezone offset (timezone adjustment from UTC/GMT)
job_title	users	Job title of user
language_id	users	The (numerical) ID for the language associated with the user's profile
last_name	users	The last name of the user
lms_user_id	users	The unique (numerical) ID for the user in the database table
middle_name	users	The middle name of the user
phone	users	The phone number for the user
points_earned	users	Displays the number of points a user has earned
rank_name	users	Displays the rank name the user has achieved
user_status	users	Displays account as active or inactive
username	users	Displays the username
vendor_user_id	users	User-unique numerical value sent by a vendor

Items Field

Field	Action	Description
allow_purchase_with_credits	items	Displays Y or N for objects that can be purchased with credits (add-on)
allow_repurchase	items	Displays Y or N for objects that can be repurchased (add-on)
days_until_purchase_expiration	items	Number of days until the purchase of the object will expire (add-on)
does_purchase_expire	items	Displays Y or N if the purchase of the object will expire (add-on)
item_price_cad	items	Displays the price in CAD of the learning object (add-on)
item_price_usd	items	Displays the price in USA of the learning object (add-on)
num_credits_to_purchase	items	Displays the number of credits needed to purchase the object (add-on)

Assessed Users Fields

Field	Action	Description
assessed_added_on	assessed_users	The date the assessed user was added to the system (add-on)
assessed_email	assessed_users	The assessed user email (add-on)
assessed_first_name	assessed_users	The assessed user's first name (add-on)
assessed_last_name	assessed_users	The assessed user's last name (add-on)
assessed_lms_user_id	assessed_users	The assessed user's unique (numerical) ID in the database table (add-on)
assessed_middle_name	assessed_users	The assessed user's middle name (add-on)
assessed_primary_group_code	assessed_users	The primary group code of the assessed user's primary group
assessed_primary_group_name	assessed_users	The primary group name of the assessed user's primary group
assessed_primary_group_role	assessed_users	The primary group role name of the assessed user's primary group
assessed_primary_group_type	assessed_users	The primary group type name of the assessed user's primary group
assessed_user_status	assessed_users	Displays account as active or inactive of the assessed user
assessed_username	assessed_users	Displays the username of the assessed user

Assignment Batches

Field	Action	Description
assign_date	assignment_batches	The date on which the assignment is scheduled to be sent
batch_creation_date	assignment_batches	The date the batch was created on
batch_name	assignment_batches	The batch name
batch_status	assignment_batches	The batch status
created_by	assignment_batches	The unique (numerical) ID of the user that created the assignment batch
due_date	assignment_batches	The date/time the assignment is due
next_assigned_ts	assignment_batches	If a recurring assignment, the next date it will be assigned
overdue	assignment_batches	Displays Y or N for Yes overdue or No not overdue
recur_day_no	assignment_batches	Number of days set to recur
recur_type	assignment_batches	Displays "Recurring" or "Once "

Assigned Groups Users

Field	Action	Description
assigned_ts	assigned_groups_users	The date on which the user was assigned to the batch

Object Authors

Field	Action	Description
auth_first_name	object_authors	First name of the author of the object
auth_last_name	object_authors	Last name of the author of the object
author	object_authors	List all the authors associated with the learning object
author_no	object_authors	The numerical ID for the author in the database table
display_title	object_authors	Displays the author of the object
faculty_id	object_authors	The unique (numerical) ID for the faculty in the database table

User Object Records

Field	Action	Description
attempt_number	user_object_records	Number of times the user attempted a quiz or survey object
batch_id	user_object_records	The unique (numerical) ID for the assignment batch in the database table
comment_text	user_object_records	Information from the Log Completion Wizard - Confirm completion step: Comments box
completed_ts	user_object_records	Date/time of when the object was completed with time stamp
equivalency_name	user_object_records	The name of the equivalency that was added to the object
file_path	user_object_records	Information from the Log Completion Wizard - Confirm completion step: Upload file option
grandfather_id	user_object_records	The record id associated with a completion that was assigned and satisfied with a previous completion.
lms_object_status	user_object_records	The object status for a user in the LMS
logged_by	user_object_records	This pulls the LMS user ID of the account logging the individual's training complete
logged_ts	user_object_records	Date/time stamp of when the user training was logged complete
parent_record_id	user_object_records	The RID of the parent object if this is a step in a complex object
record_id	user_object_records	The unique numerical ID for the LMS started/completed record
score	user_object_records	The score associated with the object
start_ts	user_object_records	Date/time of when the object was started, ts is timestamp
total_secs_tracked	user_object_records	Displays the value of total seconds tracked
user_hidden_yn	user_object_records	Displays Y or N for Yes/No if a user has hidden a started object in the frontend

LMS Objects

Field	Action	Description
certificate_template	lms_objects	Displays what certificate template is set in the learning object
created_by	lms_objects	The unique (numerical) ID of the user that created the learning object
creation_ts	lms_objects	Date/time the object was created in the LMS with time stamp
keywords	lms_objects	Shows all the keywords entered in the learning object keyword field
marketplace_imported	lms_objects	Displays Y or N if the object was purchased/added through ClearCompany Learning Marketplace
object_exp_date	lms_objects	Date/time of the object's expiration
object_rel_date	lms_objects	Date/time of the object's release
owner_group	lms_objects	Displays the owner group name of the learning object

All Objects

Field	Action	Description
duration	all_objects	General duration description
duration_secs	all_objects	Duration in seconds
object_descr	all_objects	Description of the object
object_id	all_objects	The unique (numerical) ID for the object in the database table
object_name	all_objects	Name/title of the object in the LMS
object_status	all_objects	The status of the object in the LMS
object_type_name	all_objects	The name of the object type

Course Schedule

Field	Action	Description
entry_descr	course_schedule	Specific schedule description of a class object
entry_no	course_schedule	The ID of the specific schedule of a class object
max_seats	course_schedule	Number of allowed seats for a specific schedule of a class object
registration_end_ts	course_schedule	Date/time when a class schedule will be closed for user registration
registration_start_ts	course_schedule	Date/time when a class schedule will be open for user registration
schedule_active	course_schedule	Class schedule status
schedule_end_ts	course_schedule	Date/time when a class schedule will end
schedule_facility	course_schedule	Class object schedule information about where (location, facility, resource) it will take place
schedule_start_ts	course_schedule	Date/time when a class schedule will start
virtual_meeting	course_schedule	Displays what virtual meeting tool is used for a class schedule

Action Log

Field	Action	Description
last_user_login	action_log	Last date the user's account logged into the LMS
last_user_login_ip	action_log	Displays the IP address the user logged in from the last time

Links

Field	Action	Description
link_url	links	Link added to the URL field of a Link object

Object Auxiliary Fields

Field	Action	Description
object_aux_field_	object_aux_fields	Displays information related to the specified object auxiliary fields

Groups

Field	Action	Description
opt_group_code1	groups	The code for the user's optional group 1
opt_group_code2	groups	The code for the user's optional group 2
opt_group_code3	groups	The code for the user's optional group 3
opt_group_code4	groups	The code for the user's optional group 4
opt_group_code5	groups	The code for the user's optional group 5
opt_group_name1	groups	The name of the user's optional group 1
opt_group_name2	groups	The name of the user's optional group 2
opt_group_name3	groups	The name of the user's optional group 3
opt_group_name4	groups	The name of the user's optional group 4
opt_group_name5	groups	The name of the user's optional group 5
opt_group_type1	groups	The group type name of the user's optional group 1
opt_group_type2	groups	The group type name of the user's optional group 2
opt_group_type3	groups	The group type name of the user's optional group 3
opt_group_type4	groups	The group type name of the user's optional group 4
opt_group_type5	groups	The group type name of the user's optional group 5
primary_group_code	groups	The code of the user's primary group
primary_group_name	groups	The name of the user's primary group
primary_group_type	groups	The group type name of the user's primary group

Roles

Field	Action	Description
opt_group_role1	roles	The role the user has in their optional group 1
opt_group_role2	roles	The role the user has in their optional group 2
opt_group_role3	roles	The role the user has in their optional group 3
opt_group_role4	roles	The role the user has in their optional group 4
opt_group_role5	roles	The role the user has in their optional group 5
primary_group_role	roles	The role the user has in their primary group

Succession Plans

Field	Action	Description
succession_plan_item_number	succession_plans	Numerical position of an object in a certification plan (add-on)
succession_plan_name	succession_plans	Name of the Certification Plan (add-on)

Object Tags

Field	Action	Description
tags	object_tags	Tags that are listed in the tag tab on the object

User Auxiliary Fields

Field	Action	Description
user_aux_field_	user_aux_fields	Displays information related to the specified user auxiliary fields

Create a Custom Report

Guide

1. From the administration dashboard, click Reports, then Click Custom Reports.
2. Click Create New Report.
3. Fill out the fields below:
   1. Report Type: The type of report you would like to create, from the options above.
   2. Name: The name of the report. The maximum character length is 100.
   3. Description: The description of the report, to help differentiate between the goals of each created report we recommend adding a description.
   4. Schedule: Users can schedule the report to send an email; daily, monthly, weekly, quarterly, semiannually, and annually. These emails trigger at 4 am Central Time.
   5. Include All Objects: Users have the option to include all the objects in the report.
   6. Status: Active or Inactive. 
4. Select Users: This allows users to add individual users to the report.
5. Select Groups: This allows users to add groups to the report.
6. Select Groupings: This allows users to add groupings to the report.
7. Select Objects: This allows users to select learning objects to include in the report.
8. Select Columns: This allows users to add additional data fields to the report.
   • The order of the column headers in this tab determines the order of the columns in the report.
   • Options vary based on the report type, but all will include auxiliary fields that have been added to ClearCompany Learning.
9. Generate: Before generating a report users have the following options. 
   1. Date Range: Filtering will be specific to the type of report chosen.  See below for how each report handles the date data.
      • Static Range: Select a start date and/or end date for the data you want to see. (e.g. progress from March 12 to March 19.)
      • Dynamic Range: Select the number of previous days you want information from. (e.g. progress from the 7 days before generating the report.)
   2. From Date: This allows users to specify the start date.
   3. To Date: This allows users to specify the end date.
   4. User Status: Users have the option to include active or inactive users.
   5. User Object Status: The status of the object. (Started, Completed, Skipped)
   6. Tags:
      • Match Any Selected Tag – The objects pulled will match any tag selected.
      • Match All Selected Tags – Pulls only the objects that have all tags selected.
   7. Format: Comma Separated Values (.csv) or Excel (.xlsx)
   8. Generate: When ready generate the report.

Custom Report Date Filter 

Activity Report	Records either started or completed within the time frame.
Assignment Report	Assigned time if the report includes not started records, otherwise same as activity report. For example, an assignment type Custom Report will pull information based on “assigned on dates” within the range selected.
User Report	User-created time.
Object Report	An object created time.
Quiz Report	Records started time.
Survey Report	Records started time.
Batch Report	Date Filter does not apply.
Object Status Matrix	Records started time.
Assessment Report	Records started time.

1. Activity and Assignment reports have additional optional filters
   • User status - The status of the accounts (Active or Inactive).
   • User Object Status - The status of an object based on User interaction (i.e. “Completed”).