Advanced Reporting - Custom Reports and Scripts

Advanced Reporting - Custom Reports and Scripts

Overview

This article gives you an overview of advanced reporting in iVendNext, highlighting tools like custom templates, SQL queries, and Python scripts for deeper, tailored 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 > Build > Add Report > 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. Navigate to Home > Build > Add Report > 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 Home > Build > Add Report > Script Report.

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

  3. Schedule to run daily and email to managers.




Comparison of Advanced Reporting Tools

Tool

Best For

Coding Needed?

Report Builder

Simple custom columns/filters

No

SQL Reports

Complex joins/aggregations

Yes (SQL)

Python Scripts

Dynamic logic, external APIs

Yes (Python)



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.



    • Related Articles

    • Creating and Managing Custom Reports

      Overview This article will guide you through the process of creating and managing custom reports in iVendNext. Why Create Custom Reports? Custom reports in iVendNext allow businesses to extract specific data points and analyze them in a way that ...

    • Operational Reports

      This article provides an overview of the key reports available in iVendNext. It also shows how to create custom reports and explains how to add, remove, and rearrange columns, apply both predefined and custom filters, and sort the data. Introduction ...

    • Advanced Features in iVendNext Selling

      Overview This article explores these advanced features, providing a step-by-step guide to leveraging them for maximum efficiency and effectiveness. 1. Multi-Currency Transactions In today’s global marketplace, businesses often deal with customers and ...

    • Reporting and Analytics

      Overview This article will guide you through the key components of reporting and analytics in iVendNext, including Purchase and Sales Reports, Inventory Reports, Financial Reports, and Custom Analytics. 1. Key Components of Reporting and Analytics ...

    • Reporting and Analytics

      Overview This article will guide you through the key features and best practices for leveraging reporting and analytics in iVendNext. 1. Financial Reports 1.1 General Ledger The General Ledger provides a complete record of all financial transactions. ...