Pivot Expert - Guide de référence

Introduction à ioi Pivot Expert #

ioi Pivot Expert est un outil de tableau croisé dynamique intégré à Silicon ioi. Il permet d’explorer et d’analyser des données de manière flexible, interactive et visuelle. L’utilisateur peut construire ses propres rapports dynamiques en regroupant, filtrant et agrégeant les données selon des axes personnalisés.

Il permet de résumer et analyser des données en regroupant les informations selon différents axes (lignes, colonnes) et en effectuant des calculs (sommes, moyennes, comptages, etc.).

Il transforme un tableau de données brut en vue synthétique et lisible, avec des possibilités de tri, filtrage et des regroupements très puissants.

Cet outil est particulièrement utile pour obtenir rapidement des résultats synthétiques par exemple par client, agent, produit, période, etc.

Fonctionnalités clés :

• Transformation de données brutes en vues synthétiques et lisibles.
• Regroupement des informations par client, agent, produit, période, etc.
• Calculs avancés (somme, moyenne, comptage, etc.).
• Interactivité : modification dynamique des axes et des filtres.

Avantages :

• Gain de temps : analyse rapide sans formules complexes.
• Flexibilité : test de multiples angles d’analyse en temps réel.
• Accessibilité : disponible directement depuis la liste des vues d’un module.

Ioi Pivot Expert est accessible dans la liste des vues disponibles dans un module :

Structure générale de l’interface #

L’interface d’ioi Pivot Expert est divisée en plusieurs zones fonctionnelles, que nous allons parcourir ensemble pour en comprendre les propriétés.

Refresh #

Bouton dédié au rafraîchissement de la source de données, garantissant que l’analyse reflète les dernières modifications.

Query profiles #

Cette section permet de :

• Sélectionner, modifier ou créer une nouvelle analyse.
• Gérer les profils (création, suppression, importation).

Liste des analyses disponibles :

Pour créer un nouveau query profile/supprimer le profil/modifier le profil ou importer les anciens profils :

Query Profile Settings #

Création d’un profil sur base de la structure du DocType. #

Vue de la fenêtre de configuration du nouveau profil :

Description du profil :

Recherche d’un champ :

Vue du label affiché dans le DocType et nom du champ dans la table.

Pour ajouter un champ à l’analyse, double clic sur le champ ou appuyer sur le bouton « Add field ».

Création d’un profil sur base de ioi List Expert. #

Attention, le profil de l’analyse gardera la liste de champs de la vue Expert au moment où il a été créé.

Il est modifiable indépendamment du profil de la vue Expert et inversement, la vue Expert peut être modifiée sans altérer un profil ioi Pivot Expert.

Création d’un profil sur base d’une requête. #

Sélection du DocType, sélection des champs et génération de la requête SQL.

Attention cette action de génération d’une requête SQL va supprimer la requête actuelle.

Possibilité de filtrer par défaut le site de l’utilisateur.

Ceci permet de mettre en évidence l’utilisation des variables système dans le script SQL.

Ces variables sont les mêmes que celles rencontrées dans le reporting.

Le bouton de test pour s’assurer que la requête est valide.

Possibilité d’afficher le résultat de la requête et d’exporter ce résultat au format XLSX.

Vous pouvez coller une requête SQL provenant d’un autre outil de création SQL comme Adminer, une source de données SQL d’un rapport ou une requête SQL d’une analyse d’un outil BI comme Insights.

Le test de la syntaxe se fait automatiquement à la sauvegarde avec un message d’erreur si il y a un problème.

« Limit to roles » permet de limiter l’accès aux analyses pour certains rôles.

La zone mémo permet d’alimenter l’analyse avec des commentaires sur sa conception et son utilisation.

La case « Is public » doit être cochée pour rendre l’analyse disponible des autres utilisateurs, sans cette case cochée l’analyse sera disponible uniquement pour son créateur.

La case « Is standard » informe si l’analyse fait partie du pack standard des analyses ou si c’est une analyse spécifique.

Les analyses du pack standard ne sont pas modifiables, si vous désirez apporter des modifications il est nécessaire de dupliquer l’analyse et de modifier cette copie.

La liste des analyses est consultable dans le module « ioi Pivot Query Definition ».

Utilisation de l’action « Duplicate » d’une ligne si on veut la copier et adapter une analyse.

Display profiles #

Permet de définir plusieurs présentations pour une même analyse.

Liste des profils disponibles :

Pour modifier un profil existant, en appuyant sur le bouton « Save display profile », vous sauverez la présentation des éléments affichés dans le profil actuellement sélectionné.

Pour ajouter et supprimer un profil :

Pour éditer les options du profil :

Vous pouvez éditer l’identification et la description du profil.

Les options disponibles :

Pour permettre l’affichage des lignes avec le fond des cellules en gris ou en blanc en alternance.

Pour ajuster la largeur des colonnes en fonction du contenu.

Cette option va supprimer les doublons à l’affichage des valeurs.

Pour résumer ou explorer les titres des colonnes.

Pour résumer ou explorer les lignes.

Permet de figer les colonnes de gauche

Pour afficher une colonne de total général des lignes et pour figer cette colonne à droite du tableau.

Affichage d’une ligne pour le total général des colonnes.

Permet de supprimer l’accès à la configuration de la mise en forme du tableau (les valeurs, les colonnes et les lignes).

Pour afficher le texte des titres des colonnes à la verticale.

Les différents outils de recherche, format et export d’un tableau :

La recherche d’une valeur dans le tableau :

Le format des valeurs :

L’exportation du tableau :

Le raccourci pour afficher/cacher les outils de configuration du tableau :

Utilisation des paramètres pour filtrer les données dans la requête SQL (optimisation).

Ajouter les paramètres à partir de ioi Pivot Query Definition.

Enabled : pour activer le paramètre.

Param ID : identification du paramètre à utiliser dans la requête SQL.

Prompt : texte qui sera affiché au-dessus du paramètre.

FieldType :

• Data : type texte
• Date : type date
• Datetime : type date-heure
• Float : type numérique
• Link : type lien vers un doctype

Default value : valeur par défaut à afficher dans le paramètre.

Last value : dernière valeur utilisée pour l’analyse.

Remark : information sur le paramètre.

Exemple pour utiliser des paramètres date de début et date de fin :

Exemple pour utiliser un paramètre sur l’item :

Utilisation des variables «System », « Company » et « User » dans la requête SQL.

Le bouton « Evaluate Query » permet d’exécuter la requête et d’afficher le résultat.

Le bouton « Clear » pour supprimer les valeurs de la dernière exécution.

Structure du tableau #

L’interface utilisateur se compose de deux éléments principaux : le panneau de configuration et le tableau de données.

Panneau de configuration

Le panneau Configuration permet d’ajouter des colonnes et des lignes au tableau, ainsi que des champs de valeurs définissant les méthodes d’agrégation des données. Vous pouvez ajouter chaque élément via les zones suivantes du panneau :

Values : vous pouvez ajouter des valeurs qui définissent la manière dont les données sont agrégées (telles que la somme, les valeurs minimales et maximales).

Columns : vous pouvez configurer les colonnes du tableau (définir les champs qui seront utilisés comme colonnes).

Rows : vous pouvez configurer les champs qui doivent être appliqués comme lignes du tableau.

Values #

Dans cette zone**,** vous pouvez définir les méthodes d’agrégation (telles que min, max, count) qui seront appliquées aux cellules du tableau croisé dynamique. Vous pouvez ensuite effectuer les opérations suivantes :

• ajouter et supprimer des champs de la zone de valeurs
• modifier l’ordre et la priorité des valeurs dans le tableau
• données filtrées
• définir les opérations qui seront appliquées aux champs de la table

Columns #

Dans cette zone, vous pouvez effectuer les opérations suivantes :

• ajouter et supprimer des colonnes (c.-à-d. ajouter/supprimer des champs appliqués comme colonnes)
• modifier l’ordre et la priorité des colonnes du tableau
• données filtrées

Rows #

Dans le panneau Configuration de la zone rows, vous pouvez effectuer les opérations suivantes :

• ajouter et supprimer des lignes (c.-à-d. ajouter/supprimer des champs appliqués en tant que lignes)
• modifier l’ordre et la priorité des lignes dans le tableau
• données filtrées

Opérations dans les zones

Dans les trois sections du panneau Configuration, vous pouvez ajouter ou supprimer des champs du tableau. Pour appliquer un champ en tant que ligne ou colonne, sélectionnez-le dans la section appropriée (colonnes ou lignes).

Pour ajouter un nouveau champ, dans la zone requise, cliquez sur le bouton « + » et sélectionnez le nom dans la liste déroulante.

Pour supprimer un élément, cliquez sur le bouton Supprimer (« x »).

Pour modifier l’ordre des valeurs/lignes/colonnes du tableau, faites glisser un élément jusqu’à la position souhaitée.

Pour définir les opérations qui seront appliquées à toutes les données de la colonne du tableau, dans la zone Values, cliquez sur l’opération de valeur correspondant au champ requis dans la liste déroulante, puis sélectionnez l’option souhaitée dans la liste.

Sum : Calcule la somme de toutes les valeurs.

Min : Retourne la valeur minimale.

Max : Retourne la valeur maximale.

Count : Compte le nombre total de valeurs (y compris les doublons et les valeurs nulles).

CountA : Compte le nombre de valeurs non nulles dans les données.

CountUnique : Compte le nombre de valeurs uniques (sans doublons).

Average : Calcule la moyenne arithmétique des valeurs.

Median : Retourne la valeur médiane (la valeur du milieu lorsque les données sont triées).

Product : Calcule le produit de toutes les valeurs (multiplication successive).

StDev : Estime l’écart-type d’un échantillon (mesure de la dispersion des données).

StDevP : Calcule l’écart-type d’une population entière (plus précis si toutes les données sont disponibles).

Var : Estime la variance d’un échantillon (carré de l’écart-type).

VarP : Calcule la variance d’une population entière.

Quand Utiliser Chaque Agrégat ?

• Sum/Count/CountUnique : Pour des analyses quantitatives (totaux, fréquences).
• Min/Max/Average/Median : Pour des analyses descriptives (tendances centrales, extrêmes).
• StDev/Var : Pour évaluer la dispersion ou la stabilité des données.
• Product : Cas spécifiques (ex : calculs financiers ou scientifiques).

CountA et CountUnique sont utiles pour éviter de fausser les résultats avec des valeurs nulles ou des doublons.

Filtres

Les filtres apparaissent sous forme de listes déroulantes pour chaque champ dans toutes les zones. Le tableau croisé dynamique propose les types de conditions de filtrage suivants :

• pour les valeurs textuelles : égal, différent, contient, ne contient pas, commence par, ne commence pas par, se termine par, ne se termine pas par
• Pour les valeurs numériques : supérieur : inférieur, supérieur ou égal, inférieur ou égal, égal, différent, contient, ne contient pas, commence par, ne commence pas par, se termine par, ne se termine pas par
• Pour les types de dates : supérieur, inférieur, supérieur ou égal, inférieur ou égal, égal, différent, entre, différent de

Pour filtrer les données du tableau, cliquez sur l’icône de filtre d’un des éléments dans la zone souhaitée, puis sélectionnez l’opérateur et définissez la valeur de filtrage, et enfin cliquez sur Appliquer . Les champs auxquels le filtre est appliqué seront marqués d’un symbole de filtre spécifique.

Tableau

Les données du tableau s’affichent conformément à la configuration définie dans le panneau de configuration. Le tri par colonnes est activé en cliquant sur l’en-tête de la colonne.

Possibilité de cliquer sur une référence de document pour être redirigé vers le document.

Ouverture d’un nouvel onglet et positionnement sur le document.

Pour récupérer la mise en page originale de l’analyse, se positionner sur « _Default ».

La liste des profils d’affichages est consultable dans le module « ioi Pivot Expert Display Profile ».

Composant utilisé #

L’interface d’ioi Pivot Expert repose sur une librairie JavaScript DHTMLX Pivot.

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

Adminer #

Adminer est un outil de gestion de base de données léger et puissant qui permet d’interagir avec les bases de données SQL de Silicon ioi.

https://www.adminer.org

https://github.com/vrana/adminer

Adminer vous permet de vous connecter à votre base de données et d’exécuter des requêtes SQL directement. Cela est particulièrement utile pour préparer les données spécifiques que vous souhaitez utiliser dans ioi Pivot Expert.

Les bases de données Silicon ioi utilisent le système de gestion de base de données Mariadb.

https://mariadb.org

Accès à Adminer :

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

Utilisateur et mot de passe.

Liste des bases de données hébergées sur le serveur.

Sélection d’une base de données :

Liste des tables de la base de données :

Afficher les données d’une table :

Pour modifier la requête :

Résultat de la requête :

Les fonctions SQL que vous pouvez utiliser dans MariaDB pour extraire, manipuler et formater des données sont très similaires à celles d’autres bases de données SQL.

Elles vous permettent d’extraire des informations comme l’année, le mois, de créer des périodes sous forme de chaînes de caractères, de manipuler des chaînes (par exemple, prendre les premiers ou derniers caractères), de calculer des différences de dates, d’arrondir des valeurs numériques et de conditionner des résultats en fonction de certaines règles.

Ces fonctions sont particulièrement utiles pour préparer vos données avant de les analyser dans un outil comme IOI Pivot Expert.

Exemple d’une requête préparée pour être utilisée dans ioi Pivot Expert :

Des fonctions SQL utiles :

CONCAT()

Concaténer deux ou plusieurs chaînes de caractères

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

SUBSTRING()

Extraire une sous-chaîne à partir d’une position spécifique

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

LENGTH()

Retourner la longueur d’une chaîne de caractères

SELECT LENGTH(id_number) FROM orders;

REPLACE()

Remplacer une partie d’une chaîne de caractères par une autre

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

TRIM()

Supprimer les espaces blancs au début et à la fin d’une chaîne

SELECT TRIM(id_number) FROM orders;

UPPER()

Convertir une chaîne de caractères en majuscules

SELECT UPPER(customer_name) FROM orders;

LOWER()

Convertir une chaîne de caractères en minuscules

SELECT LOWER(customer_name) FROM orders;

LPAD() Ajouter des espaces ou des caractères à gauche d'une chaîne

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

RPAD()

Ajouter des espaces ou des caractères à droite d’une chaîne

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

INSTR() Trouver la position d'un caractère dans une chaîne

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

SOUNDEX()

Retourner la valeur phonétique d’une chaîne (pour comparaison)

SELECT SOUNDEX(customer_name) FROM orders;

NOW()

Retourner la date et l’heure actuelles

SELECT NOW();

CURDATE()

Retourner la date actuelle sans l’heure

SELECT CURDATE();

CURTIME()

Retourner l’heure actuelle sans la date

SELECT CURTIME();

DATE()

Extraire la date (sans heure) d’une valeur DATETIME ou TIMESTAMP

SELECT DATE(document_date) FROM orders;

YEAR()

Extraire l’année d’une date

SELECT YEAR(document_date) FROM orders;

MONTH()

Extraire le mois d’une date

SELECT MONTH(document_date) FROM orders;

DAY()

Extraire le jour d’une date

SELECT DAY(document_date) FROM orders;

WEEK()

Extraire le numéro de la semaine de l’année

SELECT WEEK(document_date) FROM orders;

DAYNAME()

Extraire le nom du jour de la semaine

SELECT DAYNAME(document_date) FROM orders;

DAYOFWEEK()

Extraire le numéro du jour de la semaine (1 = dimanche, 7 = samedi)

SELECT DAYOFWEEK(document_date) FROM orders;

DATE_ADD()

Ajouter un intervalle de temps à une date

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

DATE_SUB()

Soustraire un intervalle de temps à une date

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

DATEDIFF()

Retourner le nombre de jours entre deux dates

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

ADDDATE()

Ajouter une date à une autre

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

LAST_DAY()

Retourner le dernier jour du mois d’une date donnée

SELECT LAST_DAY(document_date) FROM orders;

ABS()

Retourner la valeur absolue d’un nombre

SELECT ABS(total_htva) FROM orders;

CEIL()

Retourner le plus petit entier supérieur ou égal à un nombre

SELECT CEIL(total_htva) FROM orders;

FLOOR()

Retourner le plus grand entier inférieur ou égal à un nombre

SELECT FLOOR(total_htva) FROM orders;

ROUND()

Arrondir un nombre à un certain nombre de décimales

SELECT ROUND(total_htva, 2) FROM orders;

POWER()

Calculer un nombre élevé à une puissance

SELECT POWER(total_htva, 2) FROM orders;

RAND()

Retourner un nombre aléatoire entre 0 et 1

SELECT RAND() FROM orders;

MOD()

Calculer le reste de la division entre deux nombres

SELECT MOD(total_htva, 100) FROM orders;

SIGN()

Retourner le signe d’un nombre (1 pour positif, -1 pour négatif, 0 pour zéro)

SELECT SIGN(total_htva) FROM orders;

COUNT()

Compter le nombre de lignes dans un groupe

SELECT COUNT(*) FROM orders;

SUM()

Calculer la somme des valeurs d’une colonne

SELECT SUM(total_htva) FROM orders;

AVG()

Calculer la moyenne des valeurs d’une colonne

SELECT AVG(total_htva) FROM orders;

MIN()

Trouver la valeur minimale d’une colonne

SELECT MIN(total_htva) FROM orders;

MAX()

Trouver la valeur maximale d’une colonne

SELECT MAX(total_htva) FROM orders;

GROUP_CONCAT()

Concaténer les valeurs d’un groupe

SELECT GROUP_CONCAT(customer_name) FROM orders GROUP BY customer_family_1_id;

IF()

Effectuer une condition dans le SELECT (similaire à un CASE)

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

CASE

Condition multiple dans une seule colonne

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

NULLIF()

Retourner NULL si deux valeurs sont égales, sinon la première

SELECT NULLIF(total_htva, 0) FROM orders;

COALESCE()

Retourner le premier argument non-NULL

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

System Console #

Le module System Console dans Silicon ioi est un outil intégré permettant aux utilisateurs d’exécuter des requêtes SQL. Ce qui peut être très utile pour tester les requêtes en vue de les utiliser dans ioi Pivot Expert.

Liste des tables #

Liste non exhaustive des tables les plus utilisées :

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`