Working with Query Reports - Fetching Data Directly from the Database

Working with Query Reports - Fetching Data Directly from the Database

Introduction

Query Reports in iVendNext allow advanced users to extract data directly from the database using SQL (Structured Query Language). Unlike Report Builder, which offers a no-code interface, Query Reports provide granular control over data retrieval—making them ideal for complex reporting needs.


However, this power comes with restrictions: cloud users cannot write SQL directly due to security risks. This guide explains how Query Reports work, their use cases, and best practices for implementation.



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.


NotesNote: 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.


IdeaPro 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.



Conclusion

Query Reports are a powerful tool for users who need direct database access, but they require SQL knowledge and cautious implementation. While cloud users must use pre-built reports, on-premise users can leverage SQL to create tailored analytics.


For simpler reports, use Report Builder; for complex logic, explore Script Reports (Python-based).



Key Takeaways


✅ Query Reports use SQL for direct database access.
✅ Cloud users can only run pre-built Query Reports.
✅ Always use %(...)s placeholders for dynamic filters.
✅ Optimize queries to avoid performance issues.



    • Related Articles

    • Fetching Data from Child Tables Using Jinja Tags

      Introduction Child tables in iVendNext (e.g., Items, Taxes, or Additional Costs) store multi-row data linked to a parent document (e.g., Sales Invoice or Purchase Order). While Report Builder displays child table rows as duplicate lines, Jinja ...

    • 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 ...

    • Understanding the Three Types of Reports

      Introduction Reports are a critical component of iVendNext, enabling users to extract, analyze, and present data efficiently. This article explores the three primary types of reports available in iVendNext: Report Builder, Query Reports, and Script ...

    • Fetching Data from Linked Masters

      Introduction In iVendNext, linked masters (e.g., Customers, Items, Contracts) store centralized data that multiple documents reference. Manually re-entering this data wastes time and risks errors. This guide explains how to auto-fetch information ...

    • Script Reports: Leveraging Python for Complex Calculations

      Introduction Script Reports in iVendNext are the most advanced reporting option, allowing users to execute Python-based logic for data processing, complex calculations, and customized analytics. Unlike Report Builder or Query Reports, Script Reports ...