OSBI.FR - Open Source Business Intelligence

Le requêtage SQL dynamique avec Jasper Reports (1/3)

Note : Ce billet est le premier article d’une « trilogie » traitant de la mise en place de requêtes SQL dynamiques dans les moteurs de reporting open source Jasper Reports, Pentaho Reporting et Eclipse BIRT.

Préambule

Tout moteur de reporting qui se respecte doit être capable de délivrer des rapports dont les données sont filtrées par des paramètres sélectionnés par l’utilisateur.

Citons un exemple (très) simple : celui du cas d’un responsable des ventes qui souhaite récupérer la liste des clients pour une région géographique donnée. Par exemple par pays, par département ou par zone commerciale.

Au travers d’une liste déroulante, l’utilisateur doit pouvoir facilement choisir la zone géographique qui l’intéresse et ainsi obtenir le listing des clients correspondants :

Jasper Reports, Eclipse BIRT et Pentaho Reporting permettent d’atteindre cet objectif sans trop de difficultés, avec un mécanisme finalement assez similaire qui consiste à passer les paramètres sous forme de variable dans la clause WHERE de la requête SQL.

Pour Jasper Reports, la requête SQL paramétrée avec le client de conception iReport ressemble à ceci :

SELECT nom_client, ville_client, pays_client FROM clients WHERE pays_client = $P{param_pays}

Vous remarquerez qu’il n’y a pas besoin d’ajouter de simples quotes , les paramètres étant typés, Jasper Reports les rajoute automatiquement lors de la génération lorsque c’est nécessaire (pour les chaînes de caractères notamment).

Inutile donc d’écrire ceci, sous peine d’erreur : where pays_client = ‘$P{param_pays}’

Imaginons maintenant que notre utilisateur métier souhaite plus de souplesse, notamment en spécifiant les tris à effectuer  (par nom du client ou par ville), ou mieux, en choisissant les colonnes qu’il souhaite voir dans le rapport.

Dans ce cas, c’est un peu plus complexe, car il ne s’agit plus seulement de filtrer les données de la requête SQL (qui est invariable), mais plutôt de construire dynamiquement celle-ci (ou du moins une partie).

Exemple de mise en place d’une requête SQL dynamique dans iReport

1. Configuration de test
  • Base de données SampleData (Hypersonic), téléchargeable sur osbi.fr. (Extraire le zip, puis cliquer sur start_hypersonic.bat pour démarrer la base de données en memoire)
  • Version récente de iReport community (3.5, 3.7, 4.0)
2. Rapport à mettre en place

Un responsable des ventes souhaite consulter le chiffre d’affaire ou le nombre de produits vendus pour un pays donné, en sélectionnant 2 paramètres :

  • Un paramètre « indicateur » qui permet de sélectionner la valeur à afficher : soit le CA, soit la quantité de produits vendus.
  • Un paramètre « pays » qui permet de sélectionner le pays sur lequel l’indicateur est consolidé

Quelques exemples de rapports :

  • Rapport A  (Indicateur: Chiffre d’affaires / Pays: France)

  • Rapport B (Indicateur: Nb de produits vendus / Pays: France)

  • Rapport C (Indicateur: Chiffre d’affaires / Pays: USA)

  • Rapport D (Indicateur: Nb de produits vendus / Pays: USA)

Cela n’est peut-être pas évident à première vue, mais les paramètres « indicateur » et « pays » sont de types différents.

Le paramètre « indicateur » permet de sélectionner la donnée à afficher : soit le CA, soit le nb de produits vendus. Il s’agit d’un paramètre utilisé pour la construction dynamique d’une partie de la requête SQL.

En effet, chaque valeur de l’indicateur est stockée dans une colonne différente de la table des ventes. L’information « chiffre d’affaires » est stockée dans la colonne « TOTALPRICE », l’information « nb de produits vendus » dans la colonne « QUANTITYORDERED ».

Le paramètre « pays » est un paramètre standard, permettant simplement de filtrer les données par paramétrage de la clause where.

Observez de plus près les requêtes SQL (en dur) nécessaires à la génération des rapports A et D :

Requête SQL rapport A :

SELECT
SUM(ORDERFACT.TOTALPRICE) AS INDICATEUR,
CUSTOMERS.COUNTRY AS PAYS,
PRODUCTS.PRODUCTLINE AS GAMME_PRODUIT,
ORDERFACT.YEAR_ID AS ANNEE
FROM
PRODUCTS
INNER JOIN ORDERFACT ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
INNER JOIN CUSTOMERS ON ORDERFACT.CUSTOMERNUMBER = CUSTOMERS.CUSTOMERNUMBER
WHERE CUSTOMERS.COUNTRY = 'France'
GROUP BY PAYS, GAMME_PRODUIT, ANNEE

Requête SQL rapport D :

SELECT
SUM(ORDERFACT.QUANTITYORDERED) AS INDICATEUR,
CUSTOMERS.COUNTRY AS PAYS,
PRODUCTS.PRODUCTLINE AS GAMME_PRODUIT,
ORDERFACT.YEAR_ID AS ANNEE
FROM
PRODUCTS
INNER JOIN ORDERFACT ON PRODUCTS.PRODUCTCODE = ORDERFACT.PRODUCTCODE
INNER JOIN CUSTOMERS ON ORDERFACT.CUSTOMERNUMBER = CUSTOMERS.CUSTOMERNUMBER
WHERE CUSTOMERS.COUNTRY = 'USA'
GROUP BY PAYS, GAMME_PRODUIT, ANNEE

On constate que pour filtrer le pays, il suffit de rajouter un paramètre dans la clause WHERE, du style :

… WHERE CUSTOMERS.COUNTRY = ${param_pays}

Par contre, pour sélectionner le bon indicateur, il faut modifier le code SQL pour requêter la bonne colonne :

  • Pour le chiffre d’affaires : sum(ORDERFACT.TOTALPRICE) AS INDICATEUR, …
  • Pour le nb de produits vendus : sum(ORDERFACT.QUANTITYORDERED) AS INDICATEUR, …

=> JasperReports permet d’effectuer cette sélection très simplement.

Pour cela il suffit d’ajouter un point d’exclamation après le P de $P{} et le tour est joué: la valeur du paramètre est remplacée telle quelle dans la requête SQL

La syntaxe de la requête paramétrée est donc la suivante  :

3. Phase de conception (iReport)

Afin de comprendre en détail la mise en place, je vous propose de suivre ce tutoriel en vidéo pour l’élaboration du rapport sous iReport :

 

Conclusion

Jasper Reports permet de mettre en place très simplement des paramètres pour la construction de requêtes dynamiques.
On peut pousser très loin le mécanisme, jusqu’à paramétrer intégralement la requête SQL via un paramètre $P!{}.

Dans ce cas, soyez prudent, car pour être utilisés dans un rapport, il est plus commode d’avoir des noms fixes pour les champs : faites donc un bon usage de l’aliasing SQL ! (…as colonne1, as colonne2, etc …)

Pour conclure : soyez dynamiques… dans vos rapports ! 😉

Télécharger l’exemple:

 

1 Comment

  1. Merci ! C’est souvent compliqué de démarrer sur les solution Open Source. Voilà qui permet d’aller droit au but !

Les commentaires sont fermés.