This case study demonstrates how to add cascading input controls to reports in Lambda Analytics.
TOPICS
- Introduction to Cascading Input Controls
- Preparing the Report and Gathering Information
- Creating the Course Categories Input Control
- Creating the Cascading Courses Input Control
- Adding the Input Controls to the Report
Introduction to Cascading Input Controls
While standard input controls simply prompt a user to select filter values before running a report, cascading input controls prompt the user with a connected series of input controls, in which the value selected in one control will determine the available options in another.
For the purposes of this case study, we will create cascading input controls that first prompt the user to select course categories, then display the courses available within those categories for further filtering.
NOTE
This case study assumes that you have created a table view report from the course completions domain, using the course, course start date, course category, name, date enrolled, and status columns.
Preparing the Report and Gathering Information
NOTE
The course study is created based on the following specifications:
Data Source: Course Completions
View Type: Table View
Columns: Course, Course Category, Name, Date Enrolled, Date Completed, and Status
Filters: Course ID (Is One Of), Course Category (Is One Of)
However, for the SQL and parameters used to function, your report must only meet the following criteria:
Columns: Course, Course Category
Filters: Course ID, Course Category ID
Configuring the Report Filters
The queries used in this case study require that your report contain course category ID and course category ID filters.
To include and configure these filters:
1. From the Repository, open the Ad Hoc View from which your report was created.
2. In the left-hand column of the Ad Hoc Editor, select the Course Category ID measure from the Measures panel.
3. Right-click the Course Category ID measure and select Create Filter. The Course Category ID filter appears in the right-hand Filters column.
4. Repeat steps 2 and 3 for the Course ID measure.
5. From the Filter Title bar, click the Equals select to expand the Filter Type Options list.
6. From the Filter Type Options list, select Is One Of. Do this for both filters.
7. For each filter, click Deselect All, then select a few values for each filter. These selections will not matter for the final version of this report, as users will be required to select input control values before running the report.
NOTE
This step is important, because if all filter values are select then the queries implemented will not function as required.
8. Save your Ad Hoc View and either Create a New Report or Update an Existing Report.
Gathering Filter Parameter IDs
To properly map Custom Input Controls to existing filters, you will need to know the specific Parameter IDs of each filter. Both steps 2 and 3 of this case study will require you to enter the Parameter IDs of the Course ID and Course Category ID filters, respectively.
If you are following this case study to the letter, your Parameter IDs should be:
Course ID: course_id_1
Course Category ID: course_category_id_1
However, Parameter IDs for filters are generated by combining the table filed ID with a number representing the filter (if multiple filters are created from the same field, the suffixes will grow from 1 to 2, and onwards).
To find out the exact Parameter ID of a filter in your report:
1. Create a new dashboard by navigating to Create > Dashboard.
2. Add the Ad Hoc View of your report to this dashboard by selecting it from the Available Content panel. Once you have added the Ad Hoc View to your Dashboard Canvas, the Filters panel appears, showing the Course ID and Course Category ID filters.
3. Right-click the Course Category ID filter and select Properties.
4. Note the Parameter Name from the Properties window.
5. Repeat steps 3 and 4 for the Course ID filter.
Creating the Course Categories Input Control
The next step is to create the input controls that will be used to provide this cascading behaviour. The first input control we will create is Course Categories.
1. After logging into Lambda Analytics, follow this link to be taken to the Create Input Control workflow.
2. In the Type field, select Multi-Select Query.
3. In the Prompt Text field, enter "Course Categories."
4. In the Parameter Name field, enter the Parameter ID for the Course Categories ID filter in your report: "course_category_id_1"
5. After the optional Description field, ensure that the Visible option is checked.
6. Click Next. The Locate Query page appears.
7. Select Define a Query in the Next Step, as you will be entering a new unique query instead of selecting an existing one.
8. Click Next. The Name the Query page appears.
9. Enter "Course Categories" in the Name field. The Resource ID field will auto-populate.
10. Click Next. The Link a Data Source to the Query page appears.
11. Select the Select Data Source from Repository option.
12. Click Browse and identify the Totara data source (Public > Moodle/Totara > Data Connections > Totara), then click Select.
13. Click Next. The Define the Query page appears.
14. From the Query Language list, select SQL.
15. In the Query String field, enter the following query:
select id, name, concat(repeat(concat('&nsbp', char(59 using utf8)), (depth-1)*2), name) as hierarchy_name
from mdl_course_categories
order by sortorder
16. Click Save. The Save Parameters page appears.
17. In the Value Column field, enter "id." This enables the Input Control window to present values based on the ID fields from the mdl_course_categories table.
18. In the Visible Columns field, enter "name" and click Add. This enables the Input Control window to present the ID fields by their associated name. In this case, this will display the Course Categories by their proper names instead of their IDs.
20. Click Submit.
Creating the Cascading Courses Input Control
In this step, we will create an input control for selecting courses that is dependent on the selected course categories from the previous input control. Once a value or values are select, the Cascading Courses Input Control will update, and only allow the user to choose from courses within the selected course categories.
The process of creating this Cascading Input Control is similar to the process of creating a standard input control, except for the parameter referenced in the query. Here, the query will reference the parameter name of the Course Category Input Control.
1. After logging into Lambda Analytics, follow this link to be taken to the Create Input Control workflow.
2. In the Type field, select Multi-Select Query.
3. In the Parameter Name field, enter the Parameter ID for the Course Categories ID filter in your report: "course_id_1"
4. After the optional Description field, ensure that the Visible option is checked.
5. Click Next. The Locate Query page appears.
6. Select Define a Query in the Next Step, as you will be entering a new unique query instead of selecting an existing one.
7. Click Next. The Name the Query page appears.
8. Enter "Cascading Courses" in the Name field. The Resource ID field will auto-populate.
9. Click Next. The Link a Data Source to the Query page appears.
10. Select the Select Data Source from Repository option.
11. Click Browse and identify the Totara data source (Public > Moodle/Totara > Data Connections > Totara), then click Select.
12. Click Next. The Define the Query page appears.
13. From the Query Language list, select SQL.
14. In the Query String field, enter the following query:
select id, shortname, fullname
from mdl_course
where $X{IN, category, course_category_id_1}
order by sortorder
The "where clause $X{...} means that you want to select values where mdl_course.category match course_category_id_1
15. Click Save. The Save Parameters page appears.
In the Value Column field, enter "id." This enables the input control window to present values based on the ID fields from the mdl_course_categories table.
In the Visible Columns field, enter "fullname" and click Add. This enables the input control window to present the ID fields by their associated name. In this case, this will display the course categories by their proper names instead of their IDs.
16. Click Submit.
Adding the Input Controls to the Report
To add Cascading Input Controls to a report:
1. In the Repository, identify the report to which you intend to add the cascading input controls.
2. Right-click the Report and select Edit. The Set Up the Report screen appears.
3. From the left-hand panel, select Control & Resources to open the Control & Resources screen.
4. Underneath the Input Controls heading, click Add Input Control. The Locate Input Control screen appears.
5. Ensure that Select an Input Control from the Repository is selected.
6. Click Browse, and use the File Explorer to identify the Custom Input Controls folder.
7. Select the Course Categories input control.
8. Click Next. You will return to the Controls & Resources screen.
9. Repeat steps 4-8 to select the Cascading Course input control. When you return to the Controls & Resources screen, you should see the Course Categories input control and the Cascading Course input control stacked in order underneath the input controls heading.
10. From the Display Mode list, select Pop-up Window.
11. Select Always Prompt. When selected, this instructs the system to force the user to enter values into the input controls before running the report.
12. Click Submit. Now when you run the report, the Cascading Input Controls will appear as a pop-up window prompt.
In the Cascading Input Controls shown above, any selections made in the Course Categories input control will constrain the available values in the Cascading Course input control.
NOTE
Selecting 'fullname' as the visible variable allows the input control to show the course fullname values in the Cascading Courses input control.
The specified Prompt Text appears in the input control as a title.