Introduction

Reports are essential for data-driven decision-making in iVendNext, but users often encounter technical challenges that disrupt their workflow. This guide addresses the most common report issues, their root causes, and step-by-step solutions—whether you’re using Report Builder, Query Reports, or Script Reports.

1. Duplicate Entries in Reports

Symptoms

• The same document (e.g., Purchase Invoice) appears multiple times.

Cause

• Child table fields (e.g., Items, Taxes) are included in the report. Each row in a child table creates a separate line.

Solution

1. For Report Builder:

• Open the report in edit mode.

• Navigate to Pick Columns and remove unnecessary child table fields.

• Use Grouping to consolidate entries (e.g., "Group by Invoice Number").

2. For Query/Script Reports:

• Modify the SQL/Python query to DISTINCT or aggregate data:

SELECT DISTINCT parent FROM `tabPurchase Invoice Item`  

Pro Tip: Scroll to the far-right columns to identify which child table is causing duplicates.

2. Missing or Incomplete Data

Symptoms

• Expected records do not appear.

• Columns show blank values.

Causes & Fixes

For Query Reports: Validate JOIN conditions in SQL:

-- Incorrect (may miss data):  

SELECT * FROM `tabSales Invoice` WHERE customer = "X"  

-- Correct (includes linked items):  

SELECT si.name, sii.item_code FROM `tabSales Invoice` si  

JOIN `tabSales Invoice Item` sii ON si.name = sii.parent  

3. Slow Report Generation

Symptoms

• Reports take minutes to load.

• The system becomes unresponsive.

Optimization Strategies

• For Report Builder:

• Reduce the number of columns.

• Add indexed filters (e.g., date fields).

• For Query Reports:

• Avoid SELECT *—fetch only essential columns.

• Add INDEXES to frequently filtered fields:

CREATE INDEX idx_date ON `tabSales Invoice` (posting_date);  

• For Script Reports:

• Use pagination (LIMIT 100 OFFSET 0).

• Cache repetitive calculations.

4. Dynamic Filters Not Working

Symptoms

• Filters (e.g., date pickers) don’t apply.

• "Invalid Filter" errors appear.

Solutions

• Report Builder: Dynamic filters are not supported. Use predefined filters instead.

• Query/Script Reports: Ensure placeholders match the filter names:

# Script Report Example:  

filters = {"from_date": "2023-01-01"}  # Must match SQL’s %(from_date)s  

5. Formatting Issues in Exports

Symptoms

• Exported PDF/Excel files have misaligned columns or broken layouts.

Fixes

• For HTML/PDF: Use Jinja templates for print formats

• For Excel:

• Avoid merged cells in Script Reports.

• Specify column widths in Python:

return {  

    "columns": columns,  

    "data": data,  

    "widths": {"A": 20, "B": 15}  # Column widths  

}  

6. "Permission Denied" Errors

Causes

• Your role lacks access to:

• The report itself.

• Underlying DocTypes (e.g., tabSales Invoice).

Resolution

1. Go to Settings > Permissions.

2. Assign the read permission for the DocType to your role.

3. For Script Reports, ensure frappe.whitelist decorators are used:

@frappe.whitelist()  

def execute(filters):  

    ...  

7. Data Discrepancies

Symptoms

• Report totals don’t match source documents.

Debugging Steps

1. Verify Filters: Ensure no accidental exclusions (e.g., "Draft" status ignored).

2. Check Aggregations: For grouped data, confirm SUM, COUNT, etc., are applied correctly.

3. Audit Joins: In Query Reports, test LEFT JOIN vs. INNER JOIN.

Example Fix (SQL):

-- Incorrect (excludes invoices with zero items):  

SELECT si.name, SUM(sii.amount) FROM `tabSales Invoice` si  

INNER JOIN `tabSales Invoice Item` sii ON si.name = sii.parent  

-- Correct (includes all invoices):  

SELECT si.name, IFNULL(SUM(sii.amount), 0) FROM `tabSales Invoice` si  

LEFT JOIN `tabSales Invoice Item` sii ON si.name = sii.parent  

Conclusion

Most report issues stem from incorrect configurations, permissions, or query logic. By methodically checking filters, joins, and access rights, you can resolve 90% of problems.

Quick Action Guide