Pivot Expert - Quick Guide

Introduction #

ioi Pivot Expert is a dynamic pivot table that transforms your raw data into interactive visual analyses.

Main features:

• Groups and filters data by customer, agent, product, period…
• Automatic calculations: sum, average, min/max, count…
• Interactive tables modifiable in real-time

Access: View list → Pivot Expert

---

Interface Structure #

Refresh Button #

Refreshes data to reflect the latest changes.

Query Profiles #

Analysis management:

• Select an existing analysis
• Create/modify/delete profiles
• Import profiles

3 ways to create a profile:

A. Based on a DocType #

• Select the DocType
• Double-click on fields to add
• Automatic configuration

B. Based on ioi List Expert #

• Import the structure of an existing Expert view
• Modifications are independent

C. Based on an SQL query #

• Write or paste an SQL query
• Test the query before saving
• Use system variables: {user}, {company}, {site}

Important options:

• Is public: Make the analysis accessible to other users
• Is standard: Mark as standard analysis (non-modifiable)
• Limit to roles: Restrict access to certain roles
• Parameters: Dynamic filters (dates, items, etc.)

Display Profiles #

Multiple presentations for the same analysis

Configuration:

• Manage rows/columns display
• Visual options (alternating lines, auto width, etc.)
• Freeze columns/rows
• Display grand totals
• Export format (CSV, Excel)

Available options:

• Alternating row background: Alternating gray/white rows
• Columns auto width: Automatic column width adjustment
• Collapse columns: Summarize columns
• Collapse rows: Summarize rows
• Freeze columns: Freeze left columns
• Show column total: Grand total column
• Show row total: Grand total row
• Read only tree: Block configuration modification
• Vertical columns header text: Vertical column titles

---

Table Structure #

Values Zone #

Defines aggregation methods:

• Sum: Sum
• Average: Average
• Min/Max: Minimum/Maximum
• Count: Total count (with duplicates)
• CountUnique: Number of unique values
• Median: Median
• StDev/Var: Standard deviation and variance

Columns Zone #

Fields displayed as table columns

Rows Zone #

Fields displayed as table rows

Available operations:

• Add/remove fields (+/x button)
• Reorganize by drag-and-drop
• Filter data
• Sort by clicking on header

---

Filters #

Available filter types:

Text: equal, different, contains, starts with, ends with…

Numeric: >, <, ≥, ≤, =, ≠

Date: >, <, ≥, ≤, =, ≠, between

---

Dynamic Parameters #

Filter in SQL query (more performant than table filters)

Parameter types:

• Data: Text
• Date: Date
• Datetime: Date and time
• Float: Decimal numeric
• Link: Link to a DocType

Usage in SQL:

WHERE document_date BETWEEN '{param_date_debut}' AND '{param_date_fin}'
WHERE item_id = '{param_item}'

Available system variables:

• {user}: Current user
• {company}: Current company
• {site}: Current site

---

Additional Tools #

System Console #

Integrated module to test SQL queries before using them in Pivot Expert.

Access: Menu → System Console

Adminer #

External database management tool to create and test complex queries.

Access: https://db.your_environment.ioi.online/adminer.php

Useful functions:

• Table and structure exploration
• SQL query testing
• Result export

---

Useful SQL Functions #

String manipulation:

• CONCAT(a, b): Concatenate
• SUBSTRING(text, start, length): Extract
• UPPER(text) / LOWER(text): Upper/lowercase
• TRIM(text): Remove spaces

Dates:

• NOW(): Current date/time
• CURDATE(): Current date
• YEAR(date) / MONTH(date): Extract year/month
• DATE_FORMAT(date, '%Y-%m'): Format date

Numeric:

• ROUND(number, decimals): Round
• CEILING(number) / FLOOR(number): Round up/down

Conditional:

• IF(condition, value_if_true, value_if_false)
• CASE WHEN ... THEN ... ELSE ... END

---

Main Tables #

Sales (ioi Sales) #

• tabioi Sales Quote / tabioi Sales Quote Detail
• tabioi Sales Order / tabioi Sales Order Detail
• tabioi Sales Delivery / tabioi Sales Delivery Detail
• tabioi Sales Invoice / tabioi Sales Invoice Detail
• tabioi Customer / tabioi Customer Contact
• tabioi Sales Journal

Purchases (ioi Purchases) #

• tabioi Purchases Order / tabioi Purchases Order Detail
• tabioi Purchases Receipt / tabioi Purchases Receipt Detail
• tabioi Purchases Invoice / tabioi Purchases Invoice Detail
• tabioi Supplier / tabioi Supplier Contact
• tabioi Purchases Journal

Stock (ioi Items) #

• tabioi Item
• tabioi Stock Entry / tabioi Stock Entry Detail
• tabioi Warehouse / tabioi Warehouse Stock

Accounting (ioi Accounting) #

• tabioi Account Balance / tabioi Account Transaction
• tabioi Customer Balance / tabioi Customer Transaction
• tabioi Supplier Balance / tabioi Supplier Transaction
• tabioi General Account
• tabioi Period

---

Best Practices #

---

Common Use Cases #

Sales analysis by period and customer #

SELECT 
    YEAR(a.document_date) AS year,
    MONTH(a.document_date) AS month,
    a.invoice_customer_id,
    c.name as customer,
    SUM(b.value_line_doc_currency) AS total_sales
FROM `tabioi Sales Invoice` a
JOIN `tabioi Sales Invoice Detail` b ON b.parent = a.name
JOIN `tabioi Customer` c ON c.name = a.invoice_customer_id
WHERE a.document_date BETWEEN '{param_date_start}' AND '{param_date_end}'
    AND a.ioistatus >= 1
GROUP BY YEAR(a.document_date), MONTH(a.document_date), a.invoice_customer_id

Globalized invoices analysis (POS) #

SELECT 
    a.document_date,
    a.name AS invoice,
    a.globalized_document,
    COUNT(DISTINCT b.sales_delivery_id) AS nb_grouped_sales,
    SUM(a.total_incl_vat) AS total_invoice
FROM `tabioi Sales Invoice` a
LEFT JOIN `tabioi Sales Invoice Globalized Link` b ON b.sales_invoice_id = a.name
WHERE a.globalized_document = 1
    AND a.document_date BETWEEN '{param_date_start}' AND '{param_date_end}'
GROUP BY a.name

Stock analysis by warehouse #

SELECT 
    w.name AS warehouse,
    i.name AS item,
    i.description,
    ws.available_stock AS available_stock,
    ws.reserved_stock AS reserved_stock,
    (ws.available_stock - ws.reserved_stock) AS free_stock
FROM `tabioi Warehouse Stock` ws
JOIN `tabioi Warehouse` w ON w.name = ws.parent
JOIN `tabioi Item` i ON i.name = ws.item_id
WHERE ws.available_stock > 0

Customer payments analysis (balance) #

SELECT 
    c.name AS customer,
    c.vat_number AS vat,
    cb.total_debit AS total_invoices,
    cb.total_credit AS total_payments,
    cb.balance AS open_balance,
    DATEDIFF(CURDATE(), cb.oldest_open_date) AS days_overdue
FROM `tabioi Customer Balance` cb
JOIN `tabioi Customer` c ON c.name = cb.customer_id
WHERE cb.balance > 0
ORDER BY cb.balance DESC

---

Common Pitfalls #

1. Permissions and security

• ioi System Manager: Some functions require this specific role (bulk update, etc.)
• Role “System Manager” ≠ Role “ioi System Manager” (these are two different roles!)
• Check DocType permissions before sharing an analysis

2. Performance

• Avoid: SELECT * FROM on large tables
• Prefer: Filter with WHERE and limit columns
• Use: Indexes on join fields (document_date, customer_id, etc.)
• Caution: Multiple joins on detail tables can slow down

3. Calculated fields and statuses

• ioistatus: 0=Draft, 1=Pending, 2=Confirmed, 4=Partially Delivered, etc.
• Always filter on ioistatus to exclude drafts
• approval_status: Check this field for approval workflows

4. Table relationships

• Header ↔ Detail: Always join via b.parent = a.name and b.parenttype = 'DocType Name'
• Customer/Supplier: Can have multiple IDs depending on context (delivery_customer, invoice_customer, order_customer)
• Dates: document_date (document date) vs modified (last modification)

5. Specific globalized invoices

• A globalized invoice (globalized_document = 1) groups multiple POS sales
• Link table: tabioi Sales Invoice Globalized Link
• Linked sales have globalized_invoice = 1
• Don’t mix with normal invoices in line analyses

6. Variables and parameters

• System variables: {user}, {company}, {site} - always in curly braces
• Parameters: {param_xxx} - defined in Query Profile Settings
• Caution: Variables are case-sensitive

7. Data format

• VAT: Stored as decimal (0.21 for 21%)
• Amounts: value_line_doc_currency for line amount in document currency
• References: name fields are unique IDs (e.g., “SINV-2024-00123”)

---

Resources #

Component used: DHTMLX PivotDocumentation: https://docs.dhtmlx.com/pivot/

Database: MariaDBOfficial site: https://mariadb.org

Adminer: https://www.adminer.org