...
5. Navigate to the Derived Tables tab.
On the Derived Tables tab you will need to add in all the custom fields that you want to use as part of your Zoola 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 in 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 the Save Table button to create the new derived table. You will need to do this for each custom field type e.g. Event, Asset, Room, etc, that you are looking to add you to the report.
Custom Field Derived Table
...
| Code Block | ||||
|---|---|---|---|---|
| ||||
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 add them into the data source so they are available when using the Ad Hoc View builder.
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_signupsroom id LEFT OUTER userroom_cancellation_fields signuproom_id mdl_facetoface_signups id LEFT OUTER signup_fields Signup_id mdl_facetoface_sessionssignups id LEFT OUTER eventuser_cancellation_fields sessionsignup_id mdl_facetoface_sessions id LEFT OUTER event_cancellation_fields session_id mdl_ From the Left Table, select the id field from the mdl_user table, then from the Right Table select the userid field from the der_custom_user_fields Derived Table. Click the Left Outer Join button.
Or, from the Left Table, select the id field from the mdl_course table, then from the Right Table select the courseid field from the der_custom_course_fields Derived Table. Click the Left Outer Join button.
one of the following joins, depending on your query:
Navigate to the Display tab.
In the Resources panel, select to View as: Join Tree.
Expand JoinTree_1, then expand the der_custom_user_fields / der_custom_course_fields table:
Select the relevant field(s) and drag it into the appropriate set in the Sets and Items panel.
Click the OK button to save your changes in the Data Source Designer and return to the Edit Data Source page.
Under the Required Information heading, rename the Course Enrollments Data Source in the Name field, in order to avoid confusing it with the Public Course Enrollments Data Source. We will call this Data Source the "CompanyABC Course Enrollments Data Source".
To save your changes to the Data Source, click the Submit button
...