Report Builder
The Report Builder allows you to create custom reports using Data Warehouse tables.

Think of the Data Warehouse as a simplified, organized set of data designed specifically for reporting. Instead of working with hundreds of system tables, the Data Warehouse provides a smaller set of structured tables that contain the most relevant information.
Using the Report Builder, you can:
- Choose what data you want to report on
- Filter results to show only what you need
- Group and organize your data
- Customize which columns appear in your report
- Create calculated columns using conditions or mathematical operations
Before creating a report, it’s important to understand how the Primary Tables work, since this is what everything in your report is built from.
Understanding Primary Tables
When you create a report, the Primary Table is your starting point. It determines what data you’re working with, how your report is structured, and how detailed your results will be.

The Data Warehouse organizes tables into clear, purpose-built datasets, so you don’t need to work with raw or complex database tables.
Start by asking: what am I trying to report on?
If you want a complete snapshot of each student, use:
- DW Student Overview – combines academic, financial, attendance, and program data into a single view
If you’re working with students or admissions data, use:
- DW Students – core student and prospect information
- DW Prospects – admissions pipeline data like stages and sources
- DW Prospect Stage History – tracks how prospects move through stages over time
If your report is about courses, instructors, or scheduling, use:
- DW Courses – course details like schedule and status
- DW Course Instructors – instructor assignments
- DW Instructors – instructor information
- DW Sessions – academic terms and date ranges
If you’re analyzing academic performance, use:
- DW Student Academic Summary – GPA, credits, and academic standing
- DW Student Enrollments – course-level data including grades and attendance
- DW Student Grades – detailed grade records
If you want to track student progress over time, use:
- DW Student Programs – program-level progress and completion
- DW Student Sessions – performance by term or session
If your focus is attendance, use:
- DW Student Attendance Summary – totals and high-level metrics
- DW Student Attendance – detailed attendance records
If you need financial data, use:
- DW Student Financial Summary – balances and aging
- DW Student Account Activity – transaction-level detail
Creating a New Report
Go to Reports → Report Builder and click New Report Builder Report to open the setup window.

- Report Name (required): The name of the report as it will appear in the list. Use something clear and easy to recognize.
- Description: Optional note to explain what the report is used for.
- Data Source: Where the report pulls data from. Select Data Warehouse for all new reports. The Production Tables (Legacy*) option is being phased out.
- Primary Table: The main dataset your report is built on. This determines what data is available and how it is structured. Choose based on what you want to report on.
- Read Only Access: Roles that can view the report but cannot make changes.
- Read / Update Access: Roles that can edit the report.
- Location Access: Locations that are allowed to access the report.
Click Add to create the report.
You will then be taken to the Edit Report Builder Report page, where you build and customize your report by adding fields, filters, grouping, and sorting.
Legacy tables may still appear as a Data Source option temporarily, but they are being phased out. Existing reports that use legacy tables will continue to work, but new reports should be built using Data Warehouse tables.
Details
The Details section contains the basic information for your report. This is the same information entered when the report was first created.

- Report Name: The name of the report as it appears in the Report Builder list. You can update this at any time.
- Description: Optional note explaining what the report is used for. This is helpful for other users who may access the report.
- Primary Table: The main dataset the report is built on. This cannot be changed after the report is created, so if you need a different table, a new report will need to be created.
- Read Only Access: Roles that can view the report but cannot make changes.
- Read / Update Access: Roles that can edit the report.
- Location Access: Locations that are allowed to access the report.
Click Save Report Details after making any changes.
Parameters
Parameters allow you to add user input to a report. This lets users customize the results when running the report (for example, selecting a date range or filtering by a specific value).

- Parameter Name (required): The name of the parameter used in the report.
- Parameter Label: The label users will see when running the report. This should be clear and user-friendly.
- Parameter Type (required): The type of input the user will provide. Options include:
- Boolean – true/false values
- Date – must be in YYYY-MM-DD format
- Integer / Number – numeric values
- String – text input
- List – predefined list of values
- Time – must be in HH:mm:ss format
- Default Value: An optional value that will be pre-filled when the report is run.
Click Add to create the parameter.
Once added, parameters can be used in the report’s conditions to dynamically filter results.
Joins
Joins allow you to bring in additional data from another table into your report. Joins are typically used for more complex reports. Most standard reports can be built using a single Data Warehouse table.
Use a join when:
- the data you need is not available in your primary table
- you need to combine information from two different datasets

- Join Type: Controls how data from the two tables is combined
- Inner: Only shows records that exist in both tables
- Left: Shows all records from your main (primary) table, plus matching data from the second table
- Right / Cross: Less commonly used and typically not needed for standard reports
- Left Table: The primary table your report is built on
- This is automatically set and cannot be changed
- Left Column: The field from the primary table used to match data
- This is usually an ID or key field (for example, student_id)
- Right Table: The additional table you want to pull data from
- You can select from any available Data Warehouse table
- Right Column: The field in the second table that matches the Left Column
- This must correspond to the same type of data (e.g., student ID to student ID)
- Join Right Table As: A name for the joined table
- Used internally in the report
- Must be unique and contain no spaces
Conditions
Conditions allow you to filter your report results so only specific records are shown.
For example, you may want to:
- Show only active students
- Show students from a specific program
- Show records within a certain date range
To add a condition, click Add Condition Group. A condition area will appear with an AND / OR toggle and an option to add conditions.
- AND: All conditions in the group must be true
- OR: Any condition in the group can be true

Then, click Add Condition to add a filter.

Each condition is made up of the following:
- Table: The table the field belongs to
- This is automatically set based on your report and cannot be changed
- Column: The field you want to filter on
- Example: student_status, current_program_name, balance
- Operator: How the value is compared
- Examples:
- = equals
- != not equal
- > greater than
- < less than
- LIKE contains text
- Value: The value you are filtering by
- Enter the value you want to match
- Parameter (optional):
- Allows users to enter a value when running the report
- Requires a parameter to be created in the Parameters section first
- If no parameter exists, selecting this will show an error
You can add multiple conditions within a group to further refine your report results. Use the AND / OR toggle to control how those conditions are applied.
Group By
The Group By section allows you to organize report results by a specific column. This is useful when you want to summarize information by categories, such as program, session, or month, rather than displaying individual records.
Click Add Group By to open the New Group By window.

Configure the following fields:
- Group By Table: The table associated with the grouping.
- Group By Header (required): Enter a name to identify the grouping.
- Group By Type: Choose how you want to group your results:
- By Table / Column: Group results directly using a field from a Data Warehouse table.
- Select Existing Column: Group results using a column already added to your report, including transformed or calculated columns.
- Group By Table (column source): When using By Table / Column, select the table containing the field you want to group by.
- Group By Column: Select the column you want to group by. The available options depend on your selected Group By Type.
- Column Function: Optionally apply a function to the selected column:
- COALESCE(): Provide a replacement value for null values.
- LEFT(): Extract a specified number of characters from the beginning of a value.
- RIGHT(): Extract a specified number of characters from the end of a value.
- ROUND(): Round a numeric value to a specified number of decimal places.
For example, if you've created a report column that extracts the month or year from a date, you can use Select Existing Column to group results by that value instead of the original date.
Click Add to save your grouping.
Note: To use Select Existing Column, the column must first be added to your report.
Having
The Having section allows you to filter grouped or aggregated report results based on specific conditions. Unlike the regular Conditions section, which filters individual records, Having filters results after they have been grouped or calculated.
This is useful when you want to filter results based on totals, counts, averages, or other calculated values.
Click Add Having to open the New Report Having window.

Configure the following fields:
- Having Type: Choose how you want to apply the condition:
- By Table / Column: Apply a condition using a field directly from a Data Warehouse table.
- Select Existing Column: Apply a condition using a column already added to your report, including calculated or aggregated columns.
- Table: When using By Table / Column, select the table containing the field you want to filter.
- Column Name / Column: Select the column to apply the condition to. The field label changes depending on the selected Having Type.
- Aggregate Function: Select an aggregate function, such as COUNT, SUM, AVG, MIN, or MAX, when needed to evaluate the results.
- Condition Type: Choose the comparison operator, such as equals (=), greater than (>) or less than (<).
- Value From Param: Check this option to use an existing report parameter instead of a fixed value.
- Value (required): Enter the value to compare against when using a fixed value.
For example, you could create a report showing each student's most recent payment date, then use Having to display only students whose last payment was more than 30 days ago.
Click Add to save the Having condition.
Note: To use Select Existing Column, the column must first be added to your report. Having is generally used with grouped or aggregated data, and the appropriate aggregate function may be required depending on your report configuration.
Order
The Order section controls how your report results are sorted. You can organize results alphabetically, numerically, or by date, in either ascending or descending order.
Click Add Order to open the New Report Order window.

Configure the following fields:
- Order Type: Choose how you want to sort your results:
- By Table / Column: Sort using a field directly from a Data Warehouse table.
- Select Existing Column: Sort using a column already added to your report, including calculated columns and aggregate results such as counts, totals, averages, minimums, or maximums.
- Order Table: When using By Table / Column, select the table containing the field you want to sort by.
- Order Column Name: Select the column you want to sort by.
- Order Column Function: Optionally apply a function to the selected column before sorting.
- Order Direction (required): Choose how the results should be sorted:
- Ascending: Sort from lowest to highest, oldest to newest, or A to Z.
- Descending: Sort from highest to lowest, newest to oldest, or Z to A.
For example, if your report shows the number of active students in each program, you can use Select Existing Column to sort by the student count, displaying programs with the highest enrollment first.
Click Add Order to save your sorting preferences.
Note: To use Select Existing Column, the column must first be added to your report.
Calculated Columns
The Calculated Columns section allows you to create new report columns using existing data, rather than displaying a database field directly.
Calculated columns can be used to apply conditions, perform mathematical operations, and create values that can be used for grouping, filtering, and sorting.
Click New Calculated Column to open the Add Report Calculated Column window.

There are two types of calculated columns available:
- Simple Case: Creates a value based on whether a specified condition is met.
- Math: Performs arithmetic using an existing column and either another column or a fixed value.
Simple Case
Simple Case allows you to create a conditional value based on an existing column. It checks whether a condition is true and returns one result if it is, or another result if it isn't.
For example, you could create a column that displays "Outstanding" when a student's balance is greater than $0, or "Paid" otherwise.
Select Simple Case from the Column Type dropdown.

Configure the following fields:
- Column Header (required): Enter a name for the calculated column as it will appear in your report.
- Column Table: Select the table containing the field you want to evaluate.
- Column Name: Select the field to apply the condition to.
- Operator (required): Choose how the selected field should be compared, such as equals (=) or greater than (>).
- Condition Value (required): Enter the value to compare against.
- Then Type: Choose whether the result should be a fixed value or another column.
- Then Value (required): Specify the value or column to return when the condition is true.
- Else Type: Choose whether the alternative result should be a fixed value or another column.
- Else Value (required): Specify the value or column to return when the condition is false.
- Column Function: Optionally apply a function to the calculated result.
- Group Aggregate: Optionally apply an aggregate function to the calculated result, such as finding the maximum or minimum value.
Click Add to save your calculated column.
Math
Math allows you to create calculated values by performing arithmetic using an existing column and either another column or a fixed value.
Supported operations include addition, subtraction, multiplication, division, and modulo.
For example, you could multiply course hours by 60 to calculate the total number of minutes.
Select Math from the Column Type dropdown.

Configure the following fields:
- Column Header (required): Enter a name for the calculated column as it will appear in your report.
- Column Table: Select the table containing the field you want to use in the calculation.
- Column Name: Select the field to use as the first value in the calculation.
- Operator (required): Select the mathematical operation to perform:
- Addition (+): Add two values.
- Subtraction (-): Subtract one value from another.
- Multiplication (*): Multiply two values.
- Division (/): Divide one value by another.
- Modulo (%): Return the remainder after division.
- Right Type: Choose whether the second value should be a fixed value or another column.
- Right Value (required): Enter the fixed value or select the column to use in the calculation.
- Column Function: Optionally apply a function to the calculated result.
- Group Aggregate: Optionally apply an aggregate function to the calculated result.
Click Add to save your calculated column.
Once created, calculated columns can be used alongside regular report columns and referenced when configuring Group By, Having, and Order.
Column Functions and Group Aggregates
Both Simple Case and Math calculated columns include two optional settings for further processing the calculated values.
- Column Function: Applies a function to the calculated result. Available functions include date functions (such as MONTH(), YEAR(), and DAY()), text functions (such as LEFT(), RIGHT(), and UPPER()), and numeric functions (such as ROUND(), ABS(), and CEILING()). These can be used to extract, format, or transform values.
- Group Aggregate: Applies an aggregate function to summarize values across records. Available options include SUM(), AVG(), COUNT(), COUNT(DISTINCT), MIN(), MAX(), GROUP_CONCAT(), STD(), STDDEV(), and VARIANCE().
For example, you could use ROUND() to round a calculated amount or SUM() to calculate a total across grouped records.
Leave either dropdown set to None if no additional function or aggregation is needed.
Columns
The Columns section allows you to choose which fields appear in your report and control the order in which they are displayed.
Available columns are organized by their Data Warehouse tables. Select the checkboxes beside the fields you want to include in your report.
As you select fields, they appear in the Selected Columns area below.

From here, you can:
- Reorder Columns: Drag and drop columns using the move icon to change their display order.
- Edit a Column: Click the pencil icon beside a selected column to modify its settings.
- Remove a Column: Uncheck a field to remove it from your report.
Editing a Column
Click the pencil icon beside a selected column to open the Edit Report Column window.

The following settings are available:
- Column Table: Displays the Data Warehouse table containing the selected field.
- Column Name: Displays the field being used in the report.
- Column Header: The name displayed at the top of the column in your report. You can change this to something more user-friendly, such as "Student Name" instead of student_name.
- Column Function (optional): Applies a function to transform the column's values, such as extracting part of a date, modifying text, or rounding numbers.
- Group Aggregate (optional): Summarizes values when working with grouped data. Available functions include COUNT(), SUM(), AVG(), MIN(), and MAX(), among others.
In most cases, Column Function and Group Aggregate can be left as None unless your report requires additional calculations or grouping.
Click Save to apply your changes.
Once you've selected and organized your columns, continue to Preview to review the results.
Preview
The Preview section allows you to review your report before saving it. You can check the generated query and see a sample of the results to confirm that the report is displaying the information you need.
Click Preview to generate or refresh the report results.

The Preview page includes the following sections:
- Report Parameters: Displays any parameters configured for the report. If parameters are required, enter the values you want to use for the preview. These values are only used for testing. Users will be prompted to enter their own values when running the saved report.
- Generated Query: Displays the SQL query automatically created from your report configuration. This is useful for reviewing how the selected columns, conditions, and other settings are being applied.
- Preview (First 10 Rows): Displays a sample of the report results, including the selected columns and their corresponding data.
Review the preview to ensure the correct information is being retrieved and displayed as expected.
If you need to make changes, return to the appropriate section of Report Builder to adjust your configuration, then preview the report again.
Once you're satisfied with the results, click Create Report to save your report.
Note: The preview displays only the first 10 rows of data. The saved report is not limited to these 10 rows.
Create Report
Once you've reviewed your report in the Preview section and confirmed that the results are correct, click Create Report at the bottom of the page.
This opens the Create Report window, where you can configure the report's details, output formats, and access permissions before saving it.

The following options are available:
- Report Name (required): Enter the name that will identify your report in ampEducator.
- Description: Add a brief explanation of the report's purpose or the information it contains.
- Page Orientation: Choose Portrait or Landscape for the report layout.
- Output PDF: Enable or disable PDF output.
- Output CSV: Enable or disable CSV output.
- Output XML: Enable or disable XML output.
- Output TXT: Enable or disable TXT output.
- Stretch With Overflow: Automatically adjusts row heights to accommodate longer content. Disable this option for reports intended for CSV-only output.
- Read Only Access: Select which users or groups can view and run the report without making changes.
- Read / Update Access: Select which users or groups can view and modify the report.
- Location Access: Specify which institution locations can access the report.
Once you've configured these settings, click Create Report to save your report.
The new report will be added to the Home → Reports → Institution Custom section, where it can be run and scheduled like other ampEducator reports.

Legacy Tables
The Report Builder is designed to work with Data Warehouse tables. While legacy tables may still appear as a Data Source option, they are being phased out in favour of Data Warehouse tables.
Existing reports that use legacy tables will continue to work. However, all new reports should be created using Data Warehouse tables.