Aide · silicon ioi

Pivot Expert - Quick Guide

Fiche

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

Astuce

Filtered fields are marked with a specific icon.

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 #

Astuce

  • Performance: Use SQL parameters rather than table filters for large data volumes
  • Security: Limit access with Limit to roles and Is public
  • Organization: Create multiple Display Profiles for different needs
  • Testing: Validate your queries in System Console before integration
  • Documentation: Use Memo area to document your analyses

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