Overview

Dynamic filters empower users to refine report data on the fly, making them indispensable for real-time analytics in iVendNext. Unlike static filters, dynamic filters allow end-users to adjust parameters (e.g., date ranges, statuses, or item groups) without modifying the report itself. This article covers:
✔ Where dynamic filters work (Query Reports and Script Reports).
✔ How to implement them in SQL/Python.
✔ Best practices for performance and usability.

1. Dynamic Filters vs. Static Filters

Note: Report Builder does not support dynamic filters.

2. Implementing Dynamic Filters

A. In Query Reports (SQL)

Use %(...)s placeholders for filter values:

SELECT * FROM `tabSales Invoice`  

WHERE  

  posting_date BETWEEN %(from_date)s AND %(to_date)s  

  AND customer = %(customer)s  

Step-by-Step Setup:

1. Define Filters in UI:

• Navigate to Report > Edit.

• Add filter fields (e.g., Date, Customer).

2. Map Placeholders:

• Ensure SQL placeholders match filter names (e.g., from_date).

3. User Interaction:

• Users select values when running the report.

B. In Script Reports (Python)

Define filters in the execute() function:

def execute(filters=None):  

    conditions = "1=1"  

    if filters.get("from_date"):  

        conditions += f" AND posting_date >= '{filters['from_date']}'"  

    if filters.get("customer"):  

        conditions += f" AND customer = '{filters['customer']}'"  

    data = frappe.db.sql(f"""  

        SELECT name, customer, grand_total  

        FROM `tabSales Invoice`  

        WHERE {conditions}  

    """, as_dict=True)  

    return data  

Filter Configuration:

Add a filters dictionary to specify UI fields:

filters = [  

    {  

        "fieldname": "from_date",  

        "label": "From Date",  

        "fieldtype": "Date"  

    },  

    {  

        "fieldname": "customer",  

        "label": "Customer",  

        "fieldtype": "Link",  

        "options": "Customer"  

    }  

]  

3. Best Practices

Some of the best practices are:

Performance Optimization

• Indexed Fields: Apply filters to indexed columns (e.g., posting_date).

• Default Values: Pre-set common ranges (e.g., current fiscal year):

"default": frappe.utils.nowdate()  

User Experience

• Dropdowns for High-Cardinality Fields: Use Link fields instead of text inputs.

• Mandatory Filters: Force critical filters (e.g., company):

"reqd": 1  

Security

• Sanitize Inputs: Always use placeholders (%(...)s) to prevent SQL injection.

• Restrict Access: Limit filterable fields to sensitive data (e.g., cost_center).

4. Common Use Cases

A. Date-Range Analytics

WHERE posting_date BETWEEN %(start_date)s AND %(end_date)s  

Ideal for: Financial period comparisons, sales trends.

B. Status-Based Filtering

if filters.get("status"):  

    conditions += f" AND status = '{filters['status']}'"  

Ideal for: Tracking workflows (e.g., "Draft" vs. "Submitted" invoices).

C. Multi-Select Filters

WHERE item_group IN (%(item_groups)s)  /* Requires CSV input */  

Ideal for: Category-level reports.

5. Troubleshooting

Here’s a quick look at some common issues you might run into.

Issue: Filters Not Applying

Cause: Placeholder name mismatch (e.g., from_date vs. start_date).
Fix: Audit SQL/Python for consistency.

Issue: Slow Performance

Cause: Non-indexed filters or fetching excessive data.
Fix:

• Add LIMIT 1000.

• Use EXPLAIN to optimize queries.

Issue: Blank Results

Cause: Overlapping conditions (e.g., status="Paid" AND status="Unpaid").
Fix: Simplify logic with OR clauses.