Role-Based Data Filtering in Query Reports (Frappe Cloud v16) - Best Practice?

1

Hi Frappe Community! :waving_hand:

I’m working on a Leave Overview Report in Frappe Cloud (v16.18.3, ERPNext 16.19.1, HRMS 16.7.1) and need role-based data filtering:

  • HR Manager/HR User → See all employees’ leave records

  • Regular Employee → See only their own leave records

:cross_mark: What Didn’t Work:

1. Query Report with %(user)s parameter:

sql

WHERE employee = (SELECT name FROM `tabEmployee` WHERE user_id = %(user)s)

Error: KeyError: b'user' - parameter not auto-injected in Query Reports

2. Script Report with database queries:

python

def execute(filters=None):
    data = frappe.db.sql(...)  # Returns null

Issue: Custom Script Reports (Is Standard = No) can’t access database in Frappe Cloud’s safe_exec environment

3. User Permissions + “Apply User Permissions”:

  • Set up User Permissions linking users to their Employee records

  • Clicked “Set User Permissions” in Role Permission Manager

  • Result: Query Reports don’t respect User Permissions by default

:white_check_mark: Working Solution (But Is This the Best Way?):

I created a Server Script (API) + JavaScript in Query Report:

Server Script (API Method: get_filtered_leave_data):

python

user = frappe.session.user

has_hr_role = frappe.db.exists("Has Role", {
    "parent": user,
    "role": ["in", ["HR Manager", "HR User", "System Manager"]]
})

if has_hr_role:
    filters = "docstatus < 2"
else:
    employee = frappe.db.get_value("Employee", {"user_id": user}, "name")
    if employee:
        filters = f"docstatus < 2 AND employee = '{employee}'"
    else:
        filters = "docstatus < 2 AND 1=0"

data = frappe.db.sql(f"""
    SELECT leave_type, custom_leave_subtype, from_date, to_date,
           total_leave_days, status, employee_name
    FROM `tabLeave Application`
    WHERE {filters}
    ORDER BY from_date DESC
    LIMIT 500
""", as_dict=True)

frappe.response['message'] = data

Query Report JavaScript:

javascript

frappe.query_reports["Leave Overview Report"] = {
    onload: function(report) {
        report.get_data = function() {
            frappe.call({
                method: 'get_filtered_leave_data',
                callback: function(r) {
                    if (r.message) {
                        report.data = r.message;
                        report.columns = [/* column definitions */];
                        report.raw_data = {result: r.message, columns: report.columns};
                        report.render_datatable();
                    }
                }
            });
        };
        report.get_data();
    }
};

Query Section:

sql

SELECT leave_type, custom_leave_subtype, from_date, to_date,
       total_leave_days, status, employee_name
FROM `tabLeave Application`
WHERE 1=0 LIMIT 1

(Returns nothing; JavaScript overrides it)

:white_check_mark: This Works Perfectly:

  • :white_check_mark: HR roles see all records

  • :white_check_mark: Employees see only their own records

  • :white_check_mark: No KeyError issues

  • :white_check_mark: Runs in Frappe Cloud

:red_question_mark: My Questions:

  1. Is there a simpler/standard way to implement role-based filtering in Query Reports for Frappe Cloud?

  2. Why doesn’t %(user)s auto-inject in Query Reports like it does in standard reports?

  3. Should User Permissions work with Query Reports? The documentation suggests they should, but “Apply User Permissions” doesn’t seem to affect Query Reports.

  4. Is the Server Script + JavaScript approach considered a best practice, or is there a more “Frappe-native” way?

:bullseye: Use Case:

This pattern would be useful for many HR reports:

  • Salary slips (employees see own, HR sees all)

  • Attendance records

  • Appraisal reviews

  • Expense claims

  • Timesheet

  • Leave Balance

Would love to hear from the community:

  • Has anyone solved this differently?

  • Is there official documentation I missed?

  • Are there plans to improve role-based filtering in Query Reports for Cloud?

Thanks in advance! :folded_hands: