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 dialog 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 dialog 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 tool bar.

    
    

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 parentheses. 

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.