SQL Building 101

SQL Building

SQL Building is the mode in the IntelliBoard Report Builder that allows users to write custom sql code that will be processed by Builder engine in order to retrieve data from database and populate each Data Element. Every report created with Report Builder allows the user to review the SQL code generated by the Builder by clicking the SQL Code tab while editing the report.

If the user wishes to edit this code, they must click the “Edit” button on the SQL Code tab. If the user alters code and clicks “Save” the report will be saved in “SQL Mode.

By enabling this mode some features, such as managing columns and filters through visual interface will be disabled. Edits can be discarded and the report can be returned to Visual Builder mode by clicking “Reset” in the SQL Code tab after making changes. This will return the report to the last version editable by the Visual Builder (if any). All edits to the SQL code will be lost.

The sql code supports some special constructions that will be parsed by Builder engine and transformed before processing as part of SQL code.

The sql code supports most features that are available in PostgreSQL.

The data model is provided in a panel in to the right of the SQL editor:

Single-click items from the list to add them to the code at the cursor.

Placeholders

The following placeholders can be added to any SQL report. Some are required for compatibility with the Report Builder as noted below.

:limit

Used to limit output amount of data. Required for Table charts. When the SQL code runs, this placeholder will be replaced with code supporting the “Per Page“ selector during viewing of Data Element. Add it to your code like this:

LIMIT :limit

:offset

Used to offset the starting row in the display to support pagination. Required for Table charts. When the SQL code runs, this placeholder will be replaced with a formula combining the :limit value and the current page number, that depends on current page: (<current_page> - 1) * <limit>. Used to skip particular amount of data, before retrieving it.

Add it to your code like this:

OFFSET :offset

:order

Used to support sorting during display of the report. Required for Table charts. When the code runs in SQL, this placeholder will be replaced with code for sorting data, if data must be sorted in some way (for example user clicked on header of the column in table). If there is no active sorting imposed by the user, this token will be removed from from the query before it is executed.

 

:learner_roles

This placeholder will be replaced with the list of enabled learner roles in connection settings. Can be used as a value in filters.

:teacher_roles

This placeholder will be replaced with the list of enabled teacher roles in connection settings. Can be used as a value in filters.

:is_main_account

This placeholder will be replaced with TRUE or FALSE values depending on whether the account viewing the report is the main subscriber or IB User.

:lms_user_email

This placeholder will be replaced with email of current user who is viewing the report.

Assignments

In order to support IB User limitations for SQL reports, user can incorporate special placeholders into filters. These will be replaced with IB User restrictions if this report is viewed by an IB User.

:filter_permission_X, where X - assignment type

 

available assignments types:

  • 1 - course
  • 2 - user
  • 3 - category
  • 4 - term
  • 5 - org units
  • 6 - cohorts
  • 7 - deprecated
  • 8 - MWP tenant (Moodle)
  • 9 - MWP program (Moodle)
  • 10 - Learning plan (Moodle)
  • 11 - deprecated
  • 12 - Parent (Moodle)

Filters

Runtime filters can be added to SQL queries by the use of the following constructions:

courses.id in (:course) 

This construction is used to create select filters in SQL reports. For example, the Report Builder engine will parse the column courses.id and create a select filter on

the report that will be populated with the names of courses based on the values of courses.id. The column that will be used as labels in select filters in SQL report is defined in the product code for each table. For the Courses table it will be Course Name. For select filters the name of the placeholder (:course in our example) has no visible effect but should be unique within the SQL report. The token can be entered as any non-reserved word with colon before it.

COALESCE(users.signup_at, 0) BETWEEN :signup_at_start and :signup_at_end 

Such constructions are used to create Date filters. In this construction it’s important to use the correct column name before the BETWEEN operator and the same text for the two tokens, with the postfixes _start and _end.

(users.user_name LIKE :filter_search_column__users_user_name OR <other columns>)

With this approach user can define search filters. The list of columns will be separated by OR statements and the whole filter should be wrapped in parentheses.

SQL Advanced Filters

In cases where the above constructions for select filters are not sufficient, there is a way to define a select filter by creating special JSON construction. It allows, for example, to create a filter with custom select options. To add a this filter use the next construction:

({FIELD_NAME}) IN (:{"id": {FILTER_ID}, "sql": "{FILTER_SQL}", "label": "FILTER_LABEL"})

FIELD_NAME - a field by which the report data should be filtered

FILTER_ID - unique identifier in the report’s query. you can use the same identifier for several filters in one query, but on the page it will be one filter

FILTER_SQL - SQL, which will be used to select data for the filter. The SQL must return two columns: id and title

FILTER_LABEL - Label of the filter on the page

WHERE (t.status) IN (:{"id": 1, "sql": "SELECT 'Completed' AS id, 'Completed' AS title UNION SELECT 'In Progress' AS id, 'In Progress' AS title UNION SELECT 'Not Started' AS id, 'Not Started' AS title", "label": "Status Filter"})

 

Some SQL

...

WHERE (t.status) IN (:{"id": 1, "sql": "SELECT 'Completed' AS id, 'Completed' AS title UNION SELECT 'In Progress' AS id, 'In Progress' AS title UNION SELECT 'Not Started' AS id, 'Not Started' AS title", "label": "Status Filter"})

 

Was this article helpful?
0 out of 0 found this helpful