Overview

Query Reports in iVendNext let advanced users pull data using SQL for detailed, custom reporting. Unlike the no-code Report Builder, they offer full control over data queries—ideal for complex needs. However, cloud users can't write SQL directly due to security limits. This article covers how Query Reports work, when to use them, and tips for safe, effective setup.

1. What Are Query Reports?

Query Reports are SQL-based reports that:
✔ Pull raw data from database tables.
✔ Support dynamic filters (unlike Report Builder).
✔ Are used for advanced analytics and cross-module data fetching.

Example: The "Purchase Order Items to Be Received" report in the Stock module is a Query Report.

Note: Only on-premise users or administrators can create/modify SQL queries in iVendNext. Cloud users must rely on pre-built Query Reports.

2. Key Components of a Query Report

A. SQL Query Structure

A basic Query Report includes:

• SELECT (columns to fetch).

• FROM (database tables).

• WHERE (filters).

• JOIN (linking related tables).

Example:

SELECT item_code, qty, rate  

FROM `tabPurchase Order Item`  

WHERE received_qty < qty  

B. Dynamic Filters

Users can apply filters at runtime (e.g., date ranges, statuses). Filters are added as %(...)s placeholders in SQL:

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

3. How to Use Pre-Built Query Reports

For cloud users (or those without SQL access):

1. Navigate to the module containing the report (e.g., Stock).

2. Open the Reports section.

3. Select a Query Report (e.g., "Purchase Order Items to Be Received").

4. Apply filters (e.g., date range, supplier) and click Run.

Pro Tip: Use the Export feature to download data in Excel/PDF for further analysis.

4. Creating a Query Report (For Admin/On-Premise Users)

Step 1: Access the Query Report Builder

1. Go to Customize > Report > New Report.

2. Select Report Type: Query Report.

Step 2: Write the SQL Query

• Reference table names with `tabTableName` (e.g., `tabPurchase Order`).

• Use %(...)s for dynamic filters.

Example:

SELECT  

  po.name AS purchase_order,  

  poi.item_code,  

  poi.qty - poi.received_qty AS pending_qty  

FROM `tabPurchase Order` po  

JOIN `tabPurchase Order Item` poi ON po.name = poi.parent  

WHERE po.status = "Submitted"  

  AND poi.qty > poi.received_qty  

  AND po.transaction_date >= %(from_date)s  

Step 3: Define Filters (Optional)

Add filter parameters in the "Filter" section:

filters = [  

  {  

    "fieldname": "from_date",  

    "label": "From Date",  

    "fieldtype": "Date"  

  }  

]  

Step 4: Save and Test

1. Click Save.

2. Run the report to verify data accuracy.

5. Troubleshooting Query Reports

Issue: "Permission Denied" Error

Cause: The user lacks database access rights.

Fix: Grant read permissions for the referenced tables.

Issue: Incorrect Data

Causes:

• Typos in table/field names (e.g., `tabPurchaseOrder` vs. `tabPurchase Order`).

• Missing JOIN conditions.

Fix: Validate SQL syntax and test with simple queries first.

Issue: Slow Performance

Cause: Unoptimized queries (e.g., fetching all columns).

Fix:

• Use SELECT only for necessary columns.

• Add INDEXES to frequently filtered fields.

6. Security Best Practices

1. Restrict SQL Access: Only admins should modify Query Reports.

2. Sanitize Inputs: Use %(...)s placeholders to prevent SQL injection.

3. Avoid SELECT *: Fetch only required columns to reduce load.