Introduction

While iVendNext provides pre-built operational reports, businesses often need tailored analytics to address unique challenges. This guide explores advanced reporting tools—including custom Report Builder templates, SQL Query Reports, and Python Script Reports—to unlock deeper insights.

When to Use Advanced Reporting

Use these tools when you need:
✔ Data beyond standard reports (e.g., profit margins by region).
✔ Complex calculations (e.g., inventory turnover ratios).
✔ Automated workflows (e.g., low-stock alerts).

1. Custom Reports with Report Builder

Key Features

• No coding required – Drag-and-drop interface.

• Child table support – Pull data from linked tables (e.g., invoice items).

• Save & reuse – Create templates for recurring needs.

Step-by-Step: Build a Custom Sales Report

1. Navigate to Home > Customizations > Reports > Report Builder.

2. Click New, select "Sales Invoice" as the doctype.

3. Add fields:

• Parent table: Customer Name, Grand Total.

• Child table (Invoice Items): Item Code, Qty, Rate.

4. Apply filters (e.g., "Status = Paid").

5. Save as "High-Value Sales Report."

2. SQL Query Reports (For Technical Users)

Use Cases

• Multi-table joins (e.g., sales + inventory data).

• Custom aggregations (e.g., average basket size by store).

Example: Top-Selling Items by Revenue

SELECT 

  item.item_name, 

  SUM(invoice_item.amount) AS total_revenue

FROM `tabSales Invoice Item` AS invoice_item

JOIN `tabItem` AS item ON invoice_item.item_code = item.item_code

GROUP BY item.item_name

ORDER BY total_revenue DESC

LIMIT 10;

Steps to Implement:

1. Go to Home > Customizations > Reports > Query Report.

2. Paste the SQL query, test with "Run."

3. Save as "Top 10 Products by Revenue."

Note: Requires read access to database tables.

3. Python Script Reports (Maximum Flexibility)

When to Use

• Dynamic logic (e.g., apply discounts based on rules).

• External integrations (e.g., pull weather data for sales correlation).

Example: Low-Stock Alert Report

def execute(filters=None):

  columns = ["Item", "Warehouse", "Qty", "Reorder Level"]

  data = frappe.db.sql("""

    SELECT item.item_name, bin.warehouse, bin.actual_qty, item.reorder_level

    FROM `tabBin` AS bin

    JOIN `tabItem` AS item ON bin.item_code = item.item_code

    WHERE bin.actual_qty < item.reorder_level

  """)

  return columns, data

Steps to Implement:

1. Navigate to Script Report under Customizations.

2. Enter the Python code, set filters (e.g., warehouse).

3. Schedule to run daily and email to managers.

Comparison of Advanced Reporting Tools

Pro Tips for Advanced Reporting

🔹 Reuse Scripts – Save frequently used queries as templates.
🔹 Limit Data – Use filters to avoid slow performance.
🔹 Test Safely – Run scripts in development mode first.

Warning: Always back up data before running write-access scripts.

Conclusion

iVendNext’s advanced tools—Report Builder, SQL Queries, and Python Scripts—let you:
✔ Build reports tailored to your workflows.
✔ Automate data-driven decisions.
✔ Go beyond standard analytics.