Creating a Data Source

The following example walks through the process of creating a Data Source based on Course data.

To create your sample Data Source, complete the following steps:

1. From the Home page, select either Create > Data Source or click the Create link in the Data Source block. The Add New Data Source page opens.
2. In the Name field, enter "Course Categories."
3. In the Resource ID field, accept the automatically generated ID.
4. In the Description field, you option of entering a description.
5. Below the Save Location field, click the Browse button to open the Choose the destination folder dialogue box.
6. If you wish to save your example Data Source, select your Data Sources folder, then click the Select button.
7. Below the Data Source field, click the Browse button to open the Select Data Source dialogue box.
8. Navigate to Public > Totara > Data Sources > Totara and click the Select button.
9. Below the Data Source Design heading, click the Create with Data Source Designer link to launch the Data Source Designer.
10. From the Data Source panel, select the following tables and drag/enter them into the Selected Tables panel:
    • "mdl_course"
    • "mdl_course_categories"
11. Click the Derived Tables tab, where you can enter SQL queries to define custom tables based on specified data from the data source.
12. To create a derived table, complete the following steps:
    1. In the Query ID field, enter a name for the query (using no spaces)—"CourseFullname" will suffice for this sample.
    2. In the Query field, begin you query by entering "select."
    3. In the Available objects panel, select the fullname column and enter it into your query by double-clicking it.
    4. In the Query field, continue your query by entering "from."
    5. In the Available objects panel, select the "mdl_course" table and enter it into your query by double-clicking it. Your query should now appear as: select fullname from mdl_course; 
        
    6. Click the Run Query button.

    7. Click the Save Table button; "CourseFullName" now appears in the Available objects panel. 

       You may not wish to save this custom table - to delete it, select the table and click the Delete button from the Designer toolbar.
13. Click the Joins tab, where you can join tables to be presented together in your report.
14. To create a join, complete the following steps:
    1. In the Left Table panel, expand the "mdl_course" table and select the category column.
    2. In the Right Table panel, expand the "mdl_course_categories" table and select the Id column.
    3. Select the Inner Join button icon so that join results contain only rows where values in the chosen columns are equal.
15. Click the Calculated Fields tab, where you can create fields with information dependent on variable data.
16. To create a calculated field, complete the following steps:
    1. In the Field Name field, enter a name for the calculated field—"CourseNameID" will suffice for this example.
    2. From the Type list, select "String" as the datatype for the calculated field.
    3. In the Expression field, begin your expression by entering "concat("
    4. In the Available Fields panel, locate the "fullname" column of the "mdl_course" table and double-click it to enter it into your expression.
    5. In the Expression field, continue your expression by adding a comma and a space: ", "
    6. In the Available Fields panel, locate the "Id" column from the "mdl_course" table and double-click it to enter it into your expression.
    7. In the Expression field, finish your expression by adding a closing parenthesis.
17. Click the Pre-Filters tab, where you can create and apply filters to reduce the data displayed in the Data Source.
18. To create a pre-filter, complete the following steps:
    1. In the Fields panel, select the "name" column from the "mdl_course_categories" table.
    2. Enter the name column into the Filter panel by double-clicking it.
    3. In the Filters panel, select "equals" from the "name" list as the conditional operator.
    4. In the adjacent field, enter "corporate" as the filter value.
    5. Click the OK button to save the filter.
19. Click the Display tab, where you can specify which columns and calculated fields to display to users through the Data Source.
20. To modify display properties, complete the following steps:
    1. In the Resources panel, select "Jointree_1"
    2. Add "Jointree_1" to the [Sets and Items] panel by clicking the [Add to Sets] button.
    3. In the Sets and Items panel, expand the "mdl_course" set and select the "Id" item.
    4. In the Properties panel, click the Edit button.
    5. In the Label field, modify the "Id" name to "CourseID".
    6. To save your modification, click the Save button.
21. Click the OK button to save the Data Source Design.