Overview

Reports in iVendNext support smart decision-making, but technical issues can slow users down. This article outlines common problems, why they happen, and how to fix them—across Report Builder, Query Reports, and 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

The table below outlines frequent issues and their corresponding solutions.

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  

Pro Tips

Some of the Pro Tips are:’