Case Study: Adding Custom Seminar Fields to Data Sources

This case study focuses on using derived tables to add custom seminar fields to a data source on Lambda Analytics.

In a similar way that users and courses have custom fields, Totara has now included the option to include the option to add custom fields to the different screens, resources, and assets associated with the Seminar activity. These fields are added by visiting Site Administration > Seminars > Custom Fields within your Totara LMS site. You can then create new custom fields with a variety of different types, across the different areas associated with the Seminar activity.

Once you have created the custom fields to use as part of your Seminar sessions, you can start building the Lambda Analytics (Zoola) report that will include this information. For the purposes of this case study, we will add a derived table to the Seminar Expanded Data Source, which already includes many of the necessary tables associated with Seminars, sessions, and attendance. 

To begin, you will need to copy the Seminar Expanded Data Source from the Public folder into your private organizational folder tree. To copy the Seminar Expanded Data Source:

1.  From the Lambda Analytics homepage, click View Existing under the Data Sources link on the top navigation bar. 

2.  Locate the Seminar Expanded Data Source, right-click and select Duplicate. This will create a new data source directly under the original Seminar Expanded Data Source, with a (1) appended to the end.

Now that you have an editable copy of the Seminar Expanded Data Source, you can edit it to add the necessary Custom Fields through a derived table.

3.  Right-click the new Seminar Expanded Data Source and select Edit to access the Edit Data Source page. 

4.  Underneath the Data Source Design heading, click Edit with Data Source Designer to open the Display tab of the Data Source Designer. 

5.  Navigate to the Derived Tables tab.

On the Derived Tables tab, you will need to add in all the custom fields that you would like to use as part of your report. Totara features six different custom field options for the Seminar activity. You will need to create a derived table for each of the custom field types that you would like to add to your report. 

6.  In the Query ID field, enter the table name according to the derived table queries, listed below in the Custom Field Derived Table section of this document.

7.  In the Query field, copy and paste in the appropriate SQL statement defined in the Custom Field Derived Table section list below this document.

8.  Once you have added the SQL, click the Run Query button to validate the query.

9.  If your query runs successfully, click Save Table to create the new derived table. You will need to do this for each custom field type that you are looking to add you to the report.

Custom Field Derived Table

Copy and paste the following information into the derived table area of the Data Source creator.

Please note these SQL queries will need to be edited according to the custom fields that you are looking to add to your report. To do this, locate your Seminar custom fields in Totara. You will now need to map the custom field information into the SQL queries listed below.

For example, the Room custom fields currently have the following fields: Building, location, and city.

To do this, you will need to copy the Room Query (query id: room_info_data) and add in the name and field ID information. 

SELECT mfr.id AS 'room_id',
mfrid1.data AS '##ROOM FIELD 1##',
mfrid2.data AS '##ROOM FIELD N##'
FROM mdl_facetoface_room mfr
LEFT OUTER JOIN mdl_facetoface_room_info_data mfrid1 ON
(mfr.id = mfrid1.facetofaceroomid AND mfrid1.fieldid = 1)
LEFT OUTER JOIN mdl_facetoface_room_info_data mfrid2 ON
(mfr.id = mfrid2.facetofaceroomid AND mfrid2.fieldid = 2)

To map your custom profile field information into this document, you will need to make the following adjustments, show in bold and underlined.

SELECT mfri.id AS 'room_id',
mfrid1.data AS 'Building',
mfrid2.data AS 'Location',
FROM mdl_facetoface_room mfr
LEFT OUTER JOIN mdl_facetoface_room_info_data mfrid1 ON
(mfr.id = mfrid1.facetofaceroomid AND mfrid1.fieldid = 1)
LEFT OUTER JOIN mdl_facetoface_room_info_data mfrid2 ON
(mfr.id = mfrid2.facetofaceroomid AND mfrid2.fieldid = 2)
LEFT OUT JOIN mdl_facetoface_room_info_data_mfrid3 ON (mfr.id = mfrid3.facetofaceroomid AND mfrid3.fieldid = 3).

This process can be applied to any of the other custom fields, making adjustments to the SQL query as needed to accommodate the different fields. Please note that this process assumes that the fields are in the order that they were originally created within the Seminar custom profile area. If you find that the names and values are not aligning correctly, try adjusting the fieldid=# value to another number. This number refers to the ID number associated with the custom fields.

Derived Table Query ID: event_fields

SELECT
mfs.id AS 'session_id',
mfsid1.data AS '##EVENT FIELD 1##',
mfsid2.data AS '##EVENT FIELD N##'
FROM mdl_facetoface_sessions mfs
LEFT OUTER JOIN mdl_facetoface_session_info_data mfsid1 ON (mfs.id=mfsid1.facetofacesessionid and mfsid1.fieldid=1)
LEFT OUTER JOIN mdl_facetoface_session_info_data mfsid2 ON (mfs.id=mfsid2.facetofacesessionid and mfsid2.fieldid=2)

Derived Table Query ID: asset_fields

SELECT
mfa.id AS 'asset_id',
mfaid1.data AS '##ASSET FIELD 1##',
mfaid2.data AS '##ASSET FIELD N##'
FROM mdl_facetoface_asset mfa
LEFT OUTER JOIN mdl_facetoface_asset_info_data mfaid1 ON (mfa.id=mfaid1.facetofaceassetid and mfaid1.fieldid=1)
LEFT OUTER JOIN mdl_facetoface_asset_info_data mfaid2 ON (mfa.id=mfaid2.facetofaceassetid and mfaid2.fieldid=2)

Derived Table Query ID: room_fields

SELECT
mfr.id AS 'room_id',
mfrid1.data AS '##ROOM FIELD 1##'
mfrid2.data AS '##ROOM FIELD N##'
FROM mdl_facetoface_room mfr
LEFT OUTER JOIN mdl_facetoface_room_info_data mfrid1 ON (mfr.id=mfrid1.facetofaceroomid AND mfrid1.fieldid=1)
LEFT OUTER JOIN mdl_facetoface_room_info_data mfrid2 ON (mfr.id=mfrid2.facetofaceroomid AND mfrid2.fieldid=2)

Derived Table Query ID: signups_fields

SELECT
mfs.id AS 'Signup_id',
mfsid1.data AS '##SIGN-UP FIELD 1##',
mfsid2.data AS '##SIGN-UP FIELD N##'
FROM mdl_facetoface_signups mfs
LEFT OUTER JOIN mdl_facetoface_signup_info_data mfsid1 ON (mfs.id=mfsid1.facetofacesignupid AND mfsid1.fieldid=1)
LEFT OUTER JOIN mdl_facetoface_signup_info_data mfsid2 ON (mfs.id=mfsid2.facetofacesignupid AND mfsid2.fieldid=2)

Derived Table Query ID: user_cancellation_fields

SELECT
mfs.id AS 'signup_id',
mfcid1.data AS '##USER CANCELLATION FIELD 1##',
mfcid2.data AS '##USER CANCELLATION FIELD N##'
FROM mdl_facetoface_signups mfs
LEFT OUTER JOIN mdl_facetoface_cancellation_info_data mfcid1 ON (mfs.id=mfcid1.facetofacecancellationid AND mfcid1.fieldid=1)
LEFT OUTER JOIN mdl_facetoface_cancellation_info_data mfcid2 ON (mfs.id=mfcid2.facetofacecancellationid AND mfcid2.fieldid=2)

Derived Table Query ID: event_cancellation_fields

SELECT
mfs.id AS 'session_id',
mfscid1.data AS '##EVENT CANCELLATION FIELD 1##',
mfscid2.data AS '##EVENT CANCELLATION FIELD N##'
FROM mdl_facetoface_sessions mfs
LEFT OUTER JOIN mdl_facetoface_sessioncancel_info_data mfscid1 ON (mfs.id=mfscid1.facetofacesessioncancelid AND mfscid1.fieldid=1)
LEFT OUTER JOIN mdl_facetoface_sessioncancel_info_data mfscid2 ON (mfs.id=mfscid2.facetofacesessioncancelid AND mfscid2.fieldid=2)

Now that the tables have been created, you will need to join them into the data source so they can be used by reports.

10.  Navigate to the Joins tab and add the following joins according to the derived tables and custom fields that you would like to add.

Left Table Field Join Type Right Table Field
mdl_facetoface_sessions id LEFT OUTER event_fields session_id
mdl_facetoface_asset id LEFT OUTER asset_fields asset_id
mdl_facetoface_room id LEFT OUTER room_fields room_id
mdl_facetoface_signups id LEFT OUTER signup_fields signup_id
mdl_facetoface_signups id LEFT OUTER user_cancellation_fields signup_id
mdl_facetoface_sessions id LEFT OUTER event_cancellation_fields session_id

Finally, we need to add the custom fields to the data source so they are available with the Ad Hoc view builder.

11.  Navigate to the Display tab.

12.  In the Resources panel, select to View as: Join Tree.

13.  Expand JoinTree_1, then expand the events_fields, asset_fields, room_fields, signup_fields, user_cancellation_fields, or event_cancellation_fields table. 

14.  Select the relevant field(s) and drag them into the appropriate set in the Sets and Items panel. 

15.  Click OK to save your changes in the Data Source Designer and return to the Edit Data Source page. 

16.  Under the Required Information heading, rename the Course Enrollments Data Source in the Name field, to avoid confusing it with the Public Course Enrollments Data Source. We will call this data source the "[Company Name] Enrollments Data Source."

17.  Once you are complete, click Submit.