OSBI.FR - Open Source Business Intelligence

Le requêtage SQL dynamique avec Eclipse BIRT (2/3)

Après avoir étudié dans un article précédent l’implémentation d’une requête SQL dynamique dans iReport (JasperSoft), voyons maintenant comment faire de même avec le moteur de reporting Eclipse BIRT (supporté par Actuate)

Le rapport à mettre en place étant identique à celui conçu avec iReport, je vous renvoie à mon premier billet pour les aspects techniques et fonctionnels (base de données de test + cas étudié) .

Note : la version de BIRT utilisée ici est la 2.6.2, rien ne vous empêche cependant d’utiliser une version plus ancienne (2.3.x, 2.5.x) ou plus récente.

BIRT 3.7 vient en effet tout juste d’arriver (j’en parlerai prochainement sur osbi.fr…)

La différence de gestion des paramètres dans BIRT et JasperReports

Comme tout bon moteur de reporting, BIRT permet de filtrer les lignes d’une requête SQL en fonction d’un (ou plusieurs) paramètres sélectionnés par l’utilisateur.

A la différence de JasperReports, les requêtes qui chargent les valeurs possibles pour chaque paramètre sont stockées dans un fichier rptdesign (l’extension des fichiers de définition de BIRT).

Un rapport BIRT est donc par conséquent une unité d’exécution complètement autonome qu’il suffit de déposer sur un serveur web comportant par exemple BIRT Viewer.

Du côté de JasperReports, les requêtes de peuplement des paramètres ne sont pas enregistrées dans le rapport et doivent donc être créées (et stockées) à part :

  • Soit depuis iReport, via la vue « JasperServer Repository » lorsque l’on publie les rapports sur un serveur JasperReports Server. On peut alors dans ce cas mutualiser les paramètres en les utilisant dans plusieurs rapports (plutôt commode)
  • Soit avec une gestion manuelle (code PHP,  Java ou autre) lorsqu’on publie les rapports jasper dans une application web « maison ». L’idée est de reconstruire l’URL complète de génération du rapport, les paramètres étant passés dans la requête http

Utilisation (basique) des paramètres dans BIRT

La mise en place de paramètres standard est plutôt simple avec BIRT… Voyez plutôt !

1. Création de la source de données (Data Source)

N’oubliez pas de rajouter le driver JDBC Hypersonic dans la liste des drivers (bouton « Manage Drivers… ») => Voir le tutoriel vidéo à la fin de ce post pour le détail de la manipulation.

Note : User=sa / Password=password

2. Création de la requête principale du rapport (Data Set)

Ci-dessous la requête « en dur » permettant de récupérer le nombre de produits vendus (consolidés par gamme et par année) pour un pays donné (la France)

3. Création de la requête permettant d’afficher une liste déroulante avec la liste des pays :

Il suffit juste de créer un second « Data Set » nommé « Liste Pays » avec la requête SQL suivante :

SELECT DISTINCT COUNTRY FROM CUSTOMERS

4. Création du paramètre « Choix du Pays »

Afin de permettre à l’utilisateur de sélectionner le pays, il suffit de créer un paramètre de rapport  intitulé « paramPays » (Report Parameter) avec la configuration ci-dessous.

A noter que la récupération des pays est dynamique (le contenu de la liste est donc toujours à jour en fonction des données enregistrées en base)

5. Prise en compte du paramètre dans la requête principale

Pour que la requête SQL créée en 2/ soit filtrée, il suffit de définir un paramètre sur le Data Set (section « Parameters »).

(Attention à ne pas confondre les paramètres des Data Set avec les paramètres de rapports)

Ne pas oublier de lier le paramètre de rapport « paramPays » avec le paramètre de filtrage de la requête (le nom par défaut étant « param_1 ».

La requête principale peut alors être filtrée simplement en ajoutant un point d’interrogation dans la clause WHERE :

Note: Si vous avez plusieurs paramètres de requêtes, il faut mettre autant de point d’interrogation que de paramètres et dans le même ordre.

Mise en place d’une requête SQL dynamique

Pour pouvoir modifier dynamiquement la structure de la requête SQL, il faut reconstruire celle-ci partiellement ou totalement, selon les besoins…

L’architecture de BIRT, nativement orientée « web », va bien nous aider :

  • la mise en page effectuée dans BIRT n’est pas strictement celles des  « Banded Reports » tels que ceux qu’on  obtient avec JasperReports ou Pentaho Reporting. Dans BIRT, le positionnement des éléments correspond plutôt à un tableau html (ou un assemblage de <div>)
  • la gestion des événements : il existe de nombreuses  méthodes de type: After open/ Before open / After close / Before close, et ceci sur beaucoup d’objets…

Jsutement, dans les propriétés avancées du Data Set, la méthode « Before open » permet de modifier la requête juste avant son exécution :

Afin de sélectionner la colonne de la table correspondant à l’indicateur sélectionné (QUANTITYORDERED ou TOTALPRICE), un nouveau paramètre doit être créé (de type statique) :

Il reste ensuite à créer une requête SQL dynamique en renseignant la valeur ci-dessous pour la méthode « Before Open » du Data Set (attention, il faut écrire tout sur une seule ligne !) :

this.queryText = "SELECT sum(ORDERFACT." + params["paramIndicateur"] + ") AS MESURE,
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 = '" + params["paramPays"] + "' GROUP BY PAYS, GAMME_PRODUIT, ANNEE"

Notez que l’on met à jour la variable queryText (chargée de conserver la requête)  en passant les 2 paramètres  avec la syntaxe params[« nom_du_paramètre« ]

Tutoriel Vidéo

Télécharger l’exemple:

Retrouvez un autre exemple sur BIRT Exchange

A suivre: Le requêtage SQL dynamique dans Pentaho Report Designer (3/3)…

4 Comments

  1. comment on peut ajouter le total dans un tableau de bord tel que on le ajoute soit dans les résumé ou bien dans les groupe dans data cube comme je veut ?

  2. Je ne suis pas sur d’avoir compris la question, mais si tu veux afficher les totaux tu peux par exemple utiliser l’item « d’agrégation »

  3. Bonjour,

    Je n’ai pas très bien compris le principe du point « Mise en place d’une requête SQL dynamique »
    Une requête crée avec simplement des paramètres n’est pas déjà dynamique ?

    Si on fait la même requête mais avec

    « SELECT sum(ORDERFACT.?) AS MESURE,
    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 = ?
    GROUP BY PAYS, GAMME_PRODUIT, ANNEE »

    Cela ne fonctionnerais pas j’imagine ?

  4. Bonjour

    Bien sûr, si on écrit un article sur la manière de passer des paramètres AILLEURS que dans la clause WHERE, c’est sans doute qu’il y a une raison 🙂
    Après, rien n’empêche d’essayer la méthode que vous indiquez, peut-être cela marche-t’il désormais pour la dernière version de BIRT ?
    Commentaires bienvenus 😉

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée.


*