Report Builder
On the Home / Reports page, there is a second tab at the top for the Report Builder, where you can create your own custom reports.
Building reports requires a solid understanding of data structure and reporting logic, as it can be quite complex. While the Report Builder allows for a high degree of customization, there are some limitations when it comes to applying advanced mathematical operations to generate specific results.
If you’re unfamiliar with building reports or need a more complex report, ampEducator staff can create one for you. Contact [email protected] for assistance.
This section provides an overview of the Report Builder and its key features to help you understand how to create and configure reports.
To create a new report, click New Report Builder Report. A modal will appear with the following fields:
- Report Name – Enter a name for the report.
- Description – (Optional) Add details about the report’s purpose.
- Primary Table – Select the main data source for the report.
- The dropdown menu includes a Commonly Used Tables section at the top, listing frequently selected options like Courses, Programs, Staff, and Students. Below this, more specific tables are available for advanced reporting needs.
- Read-Only Access – Select which roles can view the report
- Read / Update Access – Select which roles can view and modify the report.
- Location Access – Choose which locations the report should be accessible from
Once you've filled out the required fields, click Add. This will take you directly to the Edit Report Builder Report page, where you can continue customizing your report.
When creating a new report, you are automatically directed to the Edit Report Builder Report page. However, you can also edit or update an existing report at any time by clicking the Edit Report icon next to a custom report on the Report Builder Reports list.
When editing a custom report, you’ll see a menu on the right-hand side listing all available sections, including Details, Parameters, Joins, Conditions, Group By, Order, Columns, and Preview. You can use this menu to quickly navigate between sections or scroll down to access them in order.
The Details section contains the same information entered when the report was created. You can update the Report Name, Description, Read, Update, and Location Accesses, but the Primary Table cannot be changed once the report has been created.
The Parameters section allows you to create input fields that users can customize when generating a report. This helps refine results based on specific criteria, such as selecting a date range or filtering by a category.
To add a parameter, click New Parameter, then fill out the required fields:
- Parameter Name – A unique identifier for the parameter.
- Parameter Type – Defines the type of input required. Options include:
- Boolean – A true/false selection.
- Date – A specific date in YYYY-MM-DD format.
- Time – A specific time in HH:mm:ss format.
- Integer – A whole number.
- Number – Any numerical value, including decimals.
- String – Text input.
- List – A dropdown selection of predefined options.
- Default Value – (Optional) A preset value for the parameter.
These parameters allow users to enter specific data when generating the report, making it more flexible and interactive.
The Joins section allows you to connect additional tables to the Primary Table, bringing in more data to enhance the report. To add a join, click New Join, then fill out the required fields:
- Join Type – Defines how data is combined between tables. Options include:
- Inner Join – Returns only records that have matching values in both tables.
- Left Join – Returns all records from the left table and only matching records from the right table.
- Right Join – Returns all records from the right table and only matching records from the left table.
- Cross Join – Returns all records from both tables, creating every possible combination.
- Left Table – The table selected as the Primary Table when the report was created.
- Left Column – A dropdown where you select the specific column from the Left Table to match with the right table.
- Right Table – The additional table you want to join with the Left Table.
- Right Column – A dropdown where you select the specific column from the Right Table to establish the join.
- Join Right Table As – A unique name assigned to the Right Table in this report (must be alphanumeric with no spaces).
Joins allow the report to pull in relevant data from multiple tables, making it more dynamic and comprehensive.
The Conditions section allows you to filter the report data based on specific criteria. You can add one or more conditions to refine the report results.
Click Add Condition Group to create a new group of conditions. You can choose between AND (all conditions must be met) or OR (at least one condition must be met). Within the group, clicking Add Condition will allow you to define a filter.
- Table Dropdown – Displays the Primary Table selected for the report and cannot be changed.
- Column Dropdown – Select a specific column from the table to filter by.
- Operator Dropdown – Choose a comparison operator, such as equals, greater than, less than, or other available options.
- Value Field – Enter or select the value for filtering.
- Parameter Checkbox – If checked, the condition is linked to a Parameter, allowing users to input values dynamically when generating the report.
You can add multiple conditions within a group to refine the data further. Additional Condition Groups can be created to apply different sets of criteria. Use the Remove Condition button to delete a condition or Remove Group to delete an entire condition group.
The Group By section allows you to group report data based on a specific column. Click Add Group By to open a modal where you can configure the grouping options.
- Group By Table – Pre-selected based on the report’s primary table and cannot be changed.
- Group By Column – Choose from a list of available columns to group the data by.
- Group By Header – A required field where you define a label for the grouped data in the report output.
- Column Function – (Optional) Apply a function to modify the selected column, such as handling null values, extracting characters, or rounding numerical data.
If a function is applied, it will adjust how the data is grouped in the report.
The Order section allows you to define how the report data should be sorted. Click Add Order to open a modal where you can configure the sorting options.
- Order Table – Pre-selected based on the report’s data source and cannot be changed.
- Order Column Name – Choose from a list of available columns to determine the sorting order.
- Order Column Function – (Optional) Apply a function to modify the selected column, such as handling null values, extracting characters, or rounding numerical data.
- Order Direction – Select whether the report should be sorted in Ascending or Descending order.
The Columns section allows you to select which fields will appear in the report and arrange their order.
Once selected, the columns appear in the Selected Columns section. You can drag and drop columns to rearrange their order in the report.
Editing a Column
Clicking the Edit icon next to a selected column opens a modal where you can customize the column settings.
- Column Header – The name that will appear as the column title in the report output.
- Column Function – Apply a function to modify the column’s data, such as handling null values, extracting characters, or rounding numbers.
- Group Aggregate – (For grouped reports) Choose how the column should be aggregated within a grouped report.
Once you have entered all the necessary details, click Preview Report.
This will generate a query displaying the selected data and provide a preview of the report output. The preview allows you to review the formatting, filters, and included columns before finalizing the report.
If you are satisfied with the results, click Create Report. This will save the report, and it will appear in the Custom Reports section for future use.