Aide · silicon ioi

Pivot Expert - Reference Guide

Fiche

Introduction to ioi Pivot Expert #

ioi Pivot Expert is a pivot table tool integrated into Silicon ioi. It allows you to explore and analyze data in a flexible, interactive, and visual manner. Users can build their own dynamic reports by grouping, filtering, and aggregating data according to customized axes.

It enables you to summarize and analyze data by grouping information along different axes (rows, columns) and performing calculations (sums, averages, counts, etc.).

It transforms raw data tables into synthetic and readable views, with powerful sorting, filtering, and grouping capabilities.

This tool is particularly useful for quickly obtaining summary results by customer, agent, product, period, etc.

Key Features:

  • Transformation of raw data into synthetic and readable views.
  • Grouping of information by customer, agent, product, period, etc.
  • Advanced calculations (sum, average, count, etc.).
  • Interactivity: dynamic modification of axes and filters.

Advantages:

  • Time savings: fast analysis without complex formulas.
  • Flexibility: test multiple analysis angles in real-time.
  • Accessibility: available directly from the list of views in a module.

Ioi Pivot Expert is accessible from the list of available views in a module:

General Interface Structure #

The ioi Pivot Expert interface is divided into several functional areas, which we will review together to understand their properties.

Refresh #

Button dedicated to refreshing the data source, ensuring that the analysis reflects the latest modifications.

Query profiles #

This section allows you to:

  • Select, modify, or create a new analysis.
  • Manage profiles (creation, deletion, import).

List of available analyses:

To create a new query profile/delete the profile/modify the profile or import old profiles:

Query Profile Settings #

Creating a profile based on DocType structure. #

View of the new profile configuration window:

Profile description:

Field search:

View of the label displayed in the DocType and field name in the table.

To add a field to the analysis, double-click on the field or press the “Add field” button.

Creating a profile based on ioi List Expert. #

\nNote that the analysis profile will keep the field list from the Expert view at the time it was created.

It can be modified independently of the Expert view profile and vice versa, the Expert view can be modified without altering an ioi Pivot Expert profile.

Creating a profile based on a query. #

Selecting the DocType, selecting fields, and generating the SQL query.

Attention, this action of generating an SQL query will delete the current query.

Option to filter by default to the user’s site.

This highlights the use of system variables in the SQL script.

These variables are the same as those encountered in reporting.

The test button to ensure the query is valid.

Option to display the query result and export this result in XLSX format.

You can paste an SQL query from another SQL creation tool like Adminer, an SQL data source from a report, or an SQL query from a BI tool analysis like Insights.

Syntax testing is done automatically on save with an error message if there is a problem.

“Limit to roles” allows you to restrict access to analyses for certain roles.

The memo field allows you to feed the analysis with comments on its design and use.

The “Is public” checkbox must be checked to make the analysis available to other users; without this checkbox checked, the analysis will only be available to its creator.

The “Is standard” checkbox indicates whether the analysis is part of the standard analysis pack or if it is a specific analysis.

Standard pack analyses are not modifiable; if you want to make modifications, it is necessary to duplicate the analysis and modify this copy.

The list of analyses can be consulted in the “ioi Pivot Query Definition” module.

Use the “Duplicate” action on a row if you want to copy and adapt an analysis.

\nDisplay profiles #

Allows you to define multiple presentations for the same analysis.

List of available profiles:

To modify an existing profile, by pressing the “Save display profile” button, you will save the presentation of the elements displayed in the currently selected profile.

To add and delete a profile:

To edit profile options:

You can edit the identification and description of the profile.

Available options:

To allow the display of rows with alternating gray or white cell backgrounds.

To adjust column width according to content.

This option will remove duplicates from the display of values.

To collapse or expand column headers.

To collapse or expand rows.

Allows you to freeze left columns

To display a grand total column for rows and to freeze this column on the right side of the table.

Display of a row for the grand total of columns.

Allows you to remove access to the table formatting configuration (values, columns, and rows).

To display column header text vertically.

The different search, format, and export tools for a table:

Searching for a value in the table:

Value formatting:

Table export:

The shortcut to show/hide table configuration tools:

Using parameters to filter data in the SQL query (optimization).

Add parameters from ioi Pivot Query Definition.

Enabled: to activate the parameter.

Param ID: parameter identification to use in the SQL query.

Prompt: text that will be displayed above the parameter.

FieldType:

  • Data: text type
  • Date: date type
  • Datetime: datetime type
  • Float: numeric type
  • Link: link type to a doctype

Default value: default value to display in the parameter.

Last value: last value used for the analysis.

Remark: information about the parameter.

Example for using start date and end date parameters:

Example for using an item parameter:

Using “System”, “Company”, and “User” variables in the SQL query.

The “Evaluate Query” button allows you to execute the query and display the result.

The “Clear” button to delete the values from the last execution.

Table Structure #

The user interface consists of two main elements: the configuration panel and the data table.

Configuration Panel

The Configuration panel allows you to add columns and rows to the table, as well as value fields defining data aggregation methods. You can add each element via the following areas of the panel:

Values: you can add values that define how data is aggregated (such as sum, minimum and maximum values).

Columns: you can configure table columns (define which fields will be used as columns).

Rows: you can configure which fields should be applied as table rows.

Values #

In this area, you can define aggregation methods (such as min, max, count) that will be applied to pivot table cells. You can then perform the following operations:

  • add and remove fields from the values area
  • modify the order and priority of values in the table
  • filter data
  • define operations that will be applied to table fields

Columns #

In this area, you can perform the following operations:

  • add and remove columns (i.e., add/remove fields applied as columns)
  • modify the order and priority of table columns
  • filter data

Rows #

In the Configuration panel rows area, you can perform the following operations:

  • add and remove rows (i.e., add/remove fields applied as rows)
  • modify the order and priority of table rows
  • filter data

Operations in the areas

In all three sections of the Configuration panel, you can add or remove fields from the table. To apply a field as a row or column, select it in the appropriate section (columns or rows).

To add a new field, in the required area, click the “+” button and select the name from the dropdown list.

To remove an item, click the Delete button (“x”).

To change the order of values/rows/columns in the table, drag an element to the desired position.

To define operations that will be applied to all data in the table column, in the Values area, click on the value operation corresponding to the required field in the dropdown list, then select the desired option from the list.

Sum : Calculates the sum of all values.

Min : Returns the minimum value.

Max : Returns the maximum value.

Count : Counts the total number of values (including duplicates and null values).

CountA : Counts the number of unique values (without duplicates).

CountUnique : Counts the number of non-null values in the data.

Average : Calculates the arithmetic mean of values.

Median : Returns the median value (the middle value when data is sorted).

Product : Calculates the product of all values (successive multiplication).

StDev : Estimates the standard deviation of a sample (measure of data dispersion).

StDevP : Calculates the standard deviation of an entire population (more accurate if all data is available).

Var : Estimates the variance of a sample (square of the standard deviation).

VarP : Calculates the variance of an entire population.

When to Use Each Aggregate?

  • Sum/Count/CountUnique: For quantitative analyses (totals, frequencies).
  • Min/Max/Average/Median: For descriptive analyses (central tendencies, extremes).
  • StDev/Var: To evaluate data dispersion or stability.
  • Product: Specific cases (e.g., financial or scientific calculations).

CountA and CountUnique are useful to avoid distorting results with null values or duplicates.

Filters

Filters appear as dropdown lists for each field in all areas. The pivot table offers the following types of filtering conditions:

  • for text values: equals, not equal, contains, does not contain, begins with, does not begin with, ends with, does not end with
  • For numeric values: greater than, less than, greater than or equal, less than or equal, equal, not equal, contains, does not contain, begins with, does not begin with, ends with, does not end with
  • For date types: greater than, less than, greater than or equal, less than or equal, equal, not equal, between, not between

To filter table data, click on the filter icon of one of the elements in the desired area, then select the operator and define the filter value, and finally click Apply. Fields to which the filter is applied will be marked with a specific filter symbol.

Table

Table data displays according to the configuration defined in the configuration panel. Column sorting is enabled by clicking on the column header.

Ability to click on a document reference to be redirected to the document.

Opening a new tab and positioning on the document.

To retrieve the original layout of the analysis, position on “_Default”.

The list of display profiles can be consulted in the “ioi Pivot Expert Display Profile” module.

Component Used #

The ioi Pivot Expert interface is based on the DHTMLX Pivot JavaScript library.

https://docs.dhtmlx.com/pivot/

Adminer #

Adminer is a lightweight and powerful database management tool that allows you to interact with Silicon ioi SQL databases.

https://www.adminer.org

https://github.com/vrana/adminer

Adminer allows you to connect to your database and execute SQL queries directly. This is particularly useful for preparing specific data that you want to use in ioi Pivot Expert.

Silicon ioi databases use the Mariadb database management system.

https://mariadb.org

Access to Adminer:

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

Username and password.

List of databases hosted on the server.

Selecting a database:

List of database tables:

Display table data:

To modify the query:

Query result:

The SQL functions you can use in MariaDB to extract, manipulate, and format data are very similar to those of other SQL databases.

They allow you to extract information such as year, month, create periods as character strings, manipulate strings (for example, take the first or last characters), calculate date differences, round numeric values, and condition results based on certain rules.

These functions are particularly useful for preparing your data before analyzing it in a tool like IOI Pivot Expert.

Example of a query prepared for use in ioi Pivot Expert:

Useful SQL functions:

CONCAT()

Concatenate two or more character strings

SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM users;

SUBSTRING()

Extract a substring from a specific position

SELECT SUBSTRING(id_number, 1, 5) AS short_id FROM orders;

LENGTH()

Return the length of a character string

SELECT LENGTH(id_number) FROM orders;

REPLACE()

Replace part of a character string with another

SELECT REPLACE(prefix_id, 'ABC', 'XYZ') FROM orders;

TRIM()

Remove whitespace at the beginning and end of a string

SELECT TRIM(id_number) FROM orders;

UPPER()

Convert a character string to uppercase

SELECT UPPER(customer_name) FROM orders;

LOWER()

Convert a character string to lowercase

SELECT LOWER(customer_name) FROM orders;

LPAD() Add spaces or characters to the left of a string

SELECT LPAD(id_number, 10, '0') FROM orders;

RPAD()

Add spaces or characters to the right of a string

SELECT RPAD(id_number, 10, '0') FROM orders;

INSTR() Find the position of a character in a string

SELECT INSTR(id_number, '123') FROM orders;

SOUNDEX()

Return the phonetic value of a string (for comparison)

SELECT SOUNDEX(customer_name) FROM orders;

NOW()

Return the current date and time

SELECT NOW();

CURDATE()

Return the current date without time

SELECT CURDATE();

CURTIME()

Return the current time without date

SELECT CURTIME();

DATE()

Extract the date (without time) from a DATETIME or TIMESTAMP value

SELECT DATE(document_date) FROM orders;

YEAR()

Extract the year from a date

SELECT YEAR(document_date) FROM orders;

MONTH()

Extract the month from a date

SELECT MONTH(document_date) FROM orders;

DAY()

Extract the day from a date

SELECT DAY(document_date) FROM orders;

WEEK()

Extract the week number of the year

SELECT WEEK(document_date) FROM orders;

DAYNAME()

Extract the day name of the week

SELECT DAYNAME(document_date) FROM orders;

DAYOFWEEK()

Extract the day number of the week (1 = Sunday, 7 = Saturday)

SELECT DAYOFWEEK(document_date) FROM orders;

DATE_ADD()

Add a time interval to a date

SELECT DATE_ADD(document_date, INTERVAL 1 MONTH) FROM orders;

DATE_SUB()

Subtract a time interval from a date

SELECT DATE_SUB(document_date, INTERVAL 1 WEEK) FROM orders;

DATEDIFF()

Return the number of days between two dates

SELECT DATEDIFF(CURDATE(), document_date) FROM orders;

ADDDATE()

Add a date to another

SELECT ADDDATE(document_date, INTERVAL 2 DAY) FROM orders;

LAST_DAY()

Return the last day of the month of a given date

SELECT LAST_DAY(document_date) FROM orders;

ABS()

Return the absolute value of a number

SELECT ABS(total_htva) FROM orders;

CEIL()

Return the smallest integer greater than or equal to a number

SELECT CEIL(total_htva) FROM orders;

FLOOR()

Return the largest integer less than or equal to a number

SELECT FLOOR(total_htva) FROM orders;

ROUND()

Round a number to a certain number of decimal places

SELECT ROUND(total_htva, 2) FROM orders;

POWER()

Calculate a number raised to a power

SELECT POWER(total_htva, 2) FROM orders;

RAND()

Return a random number between 0 and 1

SELECT RAND() FROM orders;

MOD()

Calculate the remainder of the division between two numbers

SELECT MOD(total_htva, 100) FROM orders;

SIGN()

Return the sign of a number (1 for positive, -1 for negative, 0 for zero)

SELECT SIGN(total_htva) FROM orders;

COUNT()

Count the number of rows in a group

SELECT COUNT(*) FROM orders;

SUM()

Calculate the sum of values in a column

SELECT SUM(total_htva) FROM orders;

AVG()

Calculate the average of values in a column

SELECT AVG(total_htva) FROM orders;

MIN()

Find the minimum value in a column

SELECT MIN(total_htva) FROM orders;

MAX()

Find the maximum value in a column

SELECT MAX(total_htva) FROM orders;

GROUP_CONCAT()

Concatenate values from a group

SELECT GROUP_CONCAT(customer_name) FROM orders GROUP BY customer_family_1_id;

IF()

Perform a condition in SELECT (similar to CASE)

SELECT IF(total_htva > 1000, 'High', 'Low') FROM orders;

CASE

Multiple condition in a single column

SELECT CASE WHEN total_htva > 1000 THEN 'High' ELSE 'Low' END FROM orders;

NULLIF()

Return NULL if two values are equal, otherwise the first

SELECT NULLIF(total_htva, 0) FROM orders;

COALESCE()

Return the first non-NULL argument

SELECT COALESCE(payment_term_id, 'No Term') FROM orders;

System Console #

The System Console module in Silicon ioi is an integrated tool allowing users to execute SQL queries. This can be very useful for testing queries for use in ioi Pivot Expert.

List of Tables #

Non-exhaustive list of the most commonly used tables:

Ioi Accounting:

**\`tabioi Account Balance\`**

**\`tabioi Account Transaction\`**

**\`tabioi Accounting Journal\`**

**\`tabioi Customer\`**

**\`tabioi Customer Balance\`**

**\`tabioi Customer Transaction\`**

**\`tabioi Financial Calculation\`**

**\`tabioi Financial Calculation Line\`**

**\`tabioi General Account\`**

**\`tabioi Payment Reminder Campaign\`**

**\`tabioi Supplier\`**

**\`tabioi Supplier Balance\`**

**\`tabioi Supplier Transaction\`**

**\`tabioi Period\`**

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 Price\`**

**\`tabioi Sales Journal\`**

Ioi Purchases:

**\`tabioi Purchases Price Request\`**

**\`tabioi Purchases Price Request Detail\`**

**\`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 Price\`**

**\`tabioi Purchases Journal\`**

Ioi Items:

**\`tabioi Item\`**

**\`tabioi Stock Entry\`**

**\`tabioi Stock Entry Detail\`**

**\`tabioi Stock Output\`**

**\`tabioi Stock Output Detail\`**

**\`tabioi Warehouse\`**

**\`tabioi Warehouse Stock\`**

**\`tabioi Warehouse Stock Detail\`**