How to Build Custom Reports in Moodle LMS: SQL, Configurable Reports, and Report Builder

Moodle LMS offers three powerful tools for creating custom reports, each designed for different use cases and skill levels. Whether you're a site administrator needing quick insights or a developer building complex relational queries, this comprehensive guide will help you choose and implement the right reporting solution.

Quick Comparison: Choosing Your Report Builder

Prerequisites and Access Requirements

Before diving into report creation, ensure you have the appropriate permissions:

• Report Builder: Site Administrator or Manager role
• Configurable Reports: Site Administrator or Manager role
• Ad-hoc Database Queries: Site Administrator role only

Important: Always test your reports in a development environment before deploying to production, especially when working with sensitive user data.

Report Builder: The User-Friendly Option

Moodle LMS' built-in Report Builder provides a drag-and-drop interface perfect for non-technical users who need quick, effective reports.

Getting Started

Navigate to: Site Administration > Reports > Report Builder > Custom Reports

Key Features

• No SQL required: Visual interface for building reports
• Flexible scheduling: Daily, weekdays only, weekly, monthly, or annual email delivery
• Advanced filtering: Multiple condition types and sorting options
• Duplicate removal: Automatically eliminates redundant rows
• Granular access control: Control exactly who can view each report
• Multiple export formats: CSV, XLSX, HTML, JSON, ODS, and PDF output options

Common Use Cases

• Student enrollment summaries
• Course completion tracking
• Basic user activity reports
• Grade distribution analysis

Performance Considerations

By default, Report Builder has no record limit, which can slow the interface with large datasets. Site administrators should set appropriate limits in the plugin settings based on system resources.

Configurable Reports: The Flexible Powerhouse

When you need complex SQL queries with dynamic filtering and chart capabilities, Configurable Reports is the ideal choice.

Installation and Access

Add the Configurable Reports block to your Dashboard or Front Page. Alternatively, access directly via: https://yourdomain.com/blocks/configurable_reports/managereport.php?courseid=1

Essential Variables

Configurable Reports supports dynamic variables that make your queries adaptable:

-- Common Variables
%%CATEGORYID%%     -- Current category ID
%%COURSEID%%       -- Current course ID  
%%CURRENTUSER%%    -- Current user ID
%%USERID%%         -- Specific user ID
%%WWWROOT%%        -- Site root URL
%%STARTTIME%%      -- Report start time
%%ENDTIME%%        -- Report end time

Filter Implementation

Filters allow users to customize report output dynamically. Always include at least one static WHERE condition:

SELECT u.firstname, u.lastname, c.fullname, cc.timecompleted
FROM prefix_course_completions cc
JOIN prefix_user u ON u.id = cc.userid
JOIN prefix_course c ON c.id = cc.course
WHERE 1
%%FILTER_COURSES:c.id%%
%%FILTER_USERS:u.id%%
%%FILTER_STARTTIME:cc.timecompleted:>%%
%%FILTER_ENDTIME:cc.timecompleted:<%%

Available Filters

• %%FILTER_COURSES: - Filter by specific courses
• %%FILTER_USERS: - Filter by user selection
• %%FILTER_CATEGORIES: - Filter by course categories
• %%FILTER_STARTTIME: / %%FILTER_ENDTIME: - Date range filtering
• %%FILTER_ROLES: - Filter by user roles
• %%FILTER_SEARCHTEXT: - Text search functionality

Sample Complete Query

Here's a practical example showing student progress across courses:

SELECT 
    u.firstname AS "First Name,
    u.lastname AS "Last Name", 
    c.fullname AS "Course Name",
    CASE 
        WHEN cc.timecompleted > 0 THEN 'Completed'
        ELSE 'In Progress'
    END AS "Status",
    FROM_UNIXTIME(ue.timecreated) AS "Enrolled Date"
FROM prefix_user_enrolments ue
JOIN prefix_user u ON u.id = ue.userid
JOIN prefix_enrol e ON e.id = ue.enrolid
JOIN prefix_course c ON c.id = e.courseid
LEFT JOIN prefix_course_completions cc ON cc.userid = u.id AND cc.course = c.id
WHERE 1
%%FILTER_COURSES:c.id%%
%%FILTER_USERS:u.id%%
ORDER BY u.lastname, c.fullname

Performance Optimization

• Alternative SQL Server: Configure an external replica database to reduce load on your main server
• Record Limits: Default 5,000 record limit (configurable by site administrators)
• Query Optimization: Use appropriate indexes and avoid complex JOINs when possible

Report Portability

One of Configurable Reports' most valuable features is its ability to export and import report configurations between Moodle LMS sites. This makes it ideal for:

• Multi-site deployments: Maintain consistent reporting across multiple Moodle LMS instances
• Report sharing: Share complex report configurations with the Moodle community
• Backup and migration: Preserve report configurations during site migrations
• Development workflow: Test reports in development environments before deploying to production

To export a report, use the export function within the report management interface. The exported file can then be imported into any other Moodle LMS site with Configurable Reports installed.

Export Formats

Configurable Reports supports multiple output formats for data analysis and sharing:

• CSV: Comma-separated values for spreadsheet applications
• XLSX: Excel format for advanced data manipulation
• JSON: Structured data format for API integration
• ODS: Open Document Spreadsheet format
• SYLK: Symbolic Link format for specialized applications

Post-Export Data Analysis

Once you've exported your report data, you can perform additional analysis and presentation enhancements using spreadsheet applications:

• Advanced Formatting: Apply professional styling, conditional formatting, and custom layouts in Excel or Google Sheets
• Data Manipulation: Create pivot tables, perform complex calculations, and merge data from multiple reports
• Chart Generation: Build sophisticated visualizations including graphs, charts, and dashboards
• Advanced Filtering: Use spreadsheet filtering and sorting capabilities beyond what's available in the reporting tool
• Statistical Analysis: Leverage built-in statistical functions for deeper data insights

Ad-hoc Database Queries: Advanced Automation

For complex reports requiring HTML output and automated email distribution, Ad-hoc Database Queries provides the most flexibility.

Access Path

Navigate to: Site Administration > Reports > Reports > Ad-hoc Database Queries

Unique Advantages

• HTML Support: The Only tool that preserves HTML formatting in output
• Email Distribution: Send reports to stakeholders without granting admin access
• Advanced Scheduling: Flexible timing options for automated delivery
• Complex Queries: Full SQL capabilities for sophisticated reporting needs
• Comprehensive Export Options: CSV, XLSX, HTML, JSON, ODS, and PDF formats available

Practical Example: User Profile Notes Report

SELECT 
    u.firstname,
    u.lastname,
    u.email,
    up.data as "Profile Notes"
FROM prefix_user u
JOIN prefix_user_info_data up ON up.userid = u.id  
JOIN prefix_user_info_field uif ON uif.id = up.fieldid
WHERE uif.shortname = 'notes'
AND up.data IS NOT NULL
AND TRIM(up.data) <> ''
ORDER BY u.lastname

This query extracts user profile notes with HTML formatting intact - something the other tools would strip out.

Performance Optimization

• Record Limits: Default 5,000 record limit (configurable by site administrators)
• Query Optimization: Use appropriate indexes and avoid complex JOINs when possible

Report Management and Portability

Unlike Configurable Reports, Ad-hoc Database Queries doesn't have the built-in ability to export/import report configurations. Report migration between sites requires:

• Manual copying: Copy SQL queries and settings between sites
• Documentation: Maintain external documentation of report configurations
• Rebuilding: Recreate reports manually on new installations

This limitation makes Ad-hoc Database Queries less suitable for multi-site deployments where consistent reporting is required across instances.

Enhanced Analysis with External Tools

All exported data formats can be imported into spreadsheet applications for enhanced analysis:

• Excel/Google Sheets Integration: Import CSV, XLSX, or ODS files for advanced data manipulation
• Custom Visualizations: Create charts, graphs, and dashboards beyond Moodle LMS' built-in capabilities
• Data Combination: Merge multiple report outputs for comprehensive analysis
• Professional Formatting: Apply corporate branding and presentation standards
• Advanced Analytics: Use spreadsheet functions for statistical analysis, trend identification, and forecasting

Database Schema and Resources

Essential References

• Examulator: Entity Relationship (ER) Diagrams of table and relationship documentation. The site includes an archive of schemas for previous versions of Moodle LMS.
• Moodle LMS Database Schema: moodleschema.zoola.io - Complete table relationships and field definitions
• Contributed Reports: Moodle Ad-hoc Reports - Community-contributed SQL examples
• Official Documentation: Configurable Reports Guide

Commonly Used Moodle LMS Tables and Fields

Understanding Moodle LMS' database structure is crucial for creating effective custom reports. Here are the most frequently used tables and their key fields:

Common Table Relationships

-- Users enrolled in courses
FROM prefix_user u
JOIN prefix_user_enrolments ue ON ue.userid = u.id
JOIN prefix_enrol e ON e.id = ue.enrolid  
JOIN prefix_course c ON c.id = e.courseid

-- Course completion data
FROM prefix_course_completions cc
JOIN prefix_user u ON u.id = cc.userid
JOIN prefix_course c ON c.id = cc.course

-- Activity completion
FROM prefix_course_modules_completion cmc
JOIN prefix_course_modules cm ON cm.id = cmc.coursemoduleid
JOIN prefix_user u ON u.id = cmc.userid
JOIN prefix_course c ON c.id = cm.course

-- Grade information
FROM prefix_grade_grades gg
JOIN prefix_grade_items gi ON gi.id = gg.itemid
JOIN prefix_user u ON u.id = gg.userid
JOIN prefix_course c ON c.id = gi.courseid

MySQL to PostgreSQL Conversion

Many contributed reports are written for MySQL/MariaDB. Common conversions needed for PostgreSQL:

Date and Time Functions:

-- MySQL
FROM_UNIXTIME(timestamp)
-- PostgreSQL  
TO_TIMESTAMP(timestamp)

-- MySQL
UNIX_TIMESTAMP()
-- PostgreSQL
EXTRACT(EPOCH FROM NOW())

-- MySQL
DATE_FORMAT(date, '%Y-%m')
-- PostgreSQL
TO_CHAR(date, 'YYYY-MM')

String Concatenation:

-- MySQL
CONCAT(field1, ' ', field2)
-- PostgreSQL
field1 || ' ' || field2

-- MySQL
SUBSTRING(field, 1, 10)
-- PostgreSQL
SUBSTR(field, 1, 10)

Conditional Logic:

-- MySQL
IF(condition, true_value, false_value)
-- PostgreSQL
CASE WHEN condition THEN true_value ELSE false_value END

Boolean Values:

-- MySQL
WHERE deleted = 0
-- PostgreSQL
WHERE deleted = false

-- MySQL  
WHERE visible = 1
-- PostgreSQL
WHERE visible = true

Quotation Marks

-- MySQL (more flexible with quotes)
SELECT "field_name" FROM `table_name`
-- PostgreSQL (strict about quote types)
SELECT "field_name" FROM table_name

-- MySQL accepts both
WHERE field = 'value' OR field = "value"  
-- PostgreSQL requires single quotes for strings
WHERE field = 'value'

Key Quotation Rules for PostgreSQL:

• Use single quotes (') for string literals and values.
• Use double quotes (") only for identifiers (table/column names) when they contain special characters or need case preservation.
• Backticks (`) used in MySQL should be replaced by double quotes (") in PostgreSQL.
• Most of the time, you can omit quotes around standard table and column names in PostgreSQL.

Tips for Conversion:

• Use AI tools like ChatGPT to help with complex conversions - just specify you're working with Moodle LMS and PostgreSQL.
• Test converted queries thoroughly in a development environment.
• Pay special attention to date/time calculations, as these often cause the most issues.
• Boolean fields in Moodle's PostgreSQL implementation use true/false rather than 1/0.
• Quote compatibility: Remove MySQL backticks and ensure single quotes are used for string values, double quotes only for identifiers when necessary.

Security and Best Practices

Excluding Deleted and/or Suspended Users

Add u.deleted = 0 and/or u.suspended = 0 if you want to exclude deleted/suspended users from user-focused reports.

Data Protection

• Sensitive Information: Be cautious when including personal data in reports
• Email Security: Use secure email delivery for confidential reports
• Access Auditing: Regularly review who has access to sensitive reports

Query Safety

• Testing Environment: Always test queries on development data first
• Data Validation: Validate user inputs in filtered reports
• Performance Impact: Monitor system resources during report execution

Development Workflow

1. Design: Plan your report structure and required data sources
2. Develop: Build and test queries in a safe environment
3. Validate: Verify data accuracy with known datasets
4. Deploy: Implement with appropriate access controls
5. Monitor: Track performance and user feedback

Common Use Cases and Solutions

Student Progress Tracking

Track completion rates, engagement metrics, and learning pathway progress across multiple courses and time periods.

Course Analytics

Analyze course effectiveness, identify content gaps, and monitor instructor performance through detailed activity reporting.

Compliance Reporting

Generate automated reports for regulatory compliance, certification tracking, and audit requirements.

Resource Utilization

Monitor system usage, identify peak times, and optimize resource allocation based on detailed usage patterns.

Troubleshooting Guide

Common SQL Issues

• Table Prefix: Always use prefix_ in table names. Example: prefix_user.
• No Semicolons: SQL statements must not end with semicolons
• No LIMIT Clauses: The tools add it automatically

Error Resolution

Cryptic error messages are common. When troubleshooting:

1. Simplify: Start with basic SELECT statements
2. Validate: Check table and field names against the schema
3. AI Assistance: Use ChatGPT or similar tools, specifying Moodle LMS and PostgreSQL context
4. Database Differences: Remember that PostgreSQL syntax differs from MySQL - refer to the conversion guide above

Performance Problems

• Large Datasets: Implement appropriate WHERE clauses to limit scope
• Complex JOINs: Consider breaking complex queries into simpler components
• Peak Hours: Schedule intensive reports during low-usage periods

Migration and Integration Strategies

When to Upgrade Your Reporting

• Start with Report Builder for basic needs and non-technical users
• Move to Configurable Reports when you need custom SQL with filtering and cross-site portability
• Use Ad-hoc Queries for automated HTML reports and email distribution

Portability Considerations

When planning multi-site deployments or report sharing:

• Configurable Reports: Seamless export/import between sites makes it ideal for standardized reporting across multiple Moodle LMS instances
• Ad-hoc Database Queries: Requires manual recreation of reports, making it less suitable for multi-site environments
• Report Builder: No export/import functionality; reports must be recreated manually on each site

External Integration

Consider how your Moodle LMS reports integrate with:

• Business Intelligence Tools: Export data for advanced analytics platforms
• Student Information Systems: Maintain data consistency across platforms
• Learning Analytics Platforms: Feed data into specialized analysis tools
• Spreadsheet Applications: Import exported data into Excel or Google Sheets for advanced formatting, pivot tables, custom charts, and statistical analysis beyond Moodle LMS' native capabilities

Conclusion

Each of Moodle LMS’ reporting tools has its own strengths, and over the years, I’ve seen how choosing the right one can make a big difference. Report Builder is great when you need something quick and intuitive. When you're ready to dive deeper, Configurable Reports gives you the flexibility of SQL with handy filters. And for those who love automation and don't mind getting into the weeds, Ad-hoc Database Queries can be a real powerhouse.

There’s no one-size-fits-all answer—it really depends on your comfort level and what you’re trying to accomplish. My advice? Start with what feels manageable, experiment, and build up from there. You’ll learn a lot just by trying things out and seeing what works.

Most importantly, keep your goals and your users in mind. A well-crafted report can save time, reveal insights, and even change how people work. And don’t forget: always follow good security practices - your data deserves it.

Michael Milette