Comment utiliser la clause IN dans les DataSet avec BIRT

Si vous utilisez BIRT vous avez probablement remarqué qu’on ne peut pas utiliser de « IN » dans les requêtes SQL avec un paramètre de type multiselect de cette manière :

SELECT * FROM toto WHERE  id  IN (?)

Dans cet article deux solutions seront proposées : celle proposée par BIRT/Actuate dans leurs supports et celle que j’utilise.

Nous partirons du principe que le paramètre à passer au Data Set est un « report parameter » de type « multiselect ». Après cet article, vous serez en mesure d’utiliser les paramètres de multi-selection dans vos dataset et cela, de la manière qui vous convient le mieux selon vos contraintes techniques.

Un rapport d’exemple a été créé. Le but de ce dernier est d’afficher la liste des clients appartenant au(x) pays sélectionné(s) par un utilisateur grâce à un paramètre de type « multiselect ».  La source de données utilisée est ClassicModel.

Le rapport d’exemple est téléchargeable ici (clic droit –> Enregistrer Sous).

Avant toute chose, nous avons besoin d’un paramètre de type multiselect. Créer ce paramètre qui permet d’afficher un le listing de tous les pays. Dans le fichier téléchargé, vous pouvez regarder le dataset CountryList et les « report parameter »  : « CountryList_FILTER » et « CountryLIst_IN ». J’en ai créé deux car chaque solution utilise son propre report parameter.

multiselect_country

Première solution : Filtrer les donner grâce à l’onglet « Filter » dans votre data set

C’est la solution la plus simple mais nous allons voir ses limites :

  • Faite votre requête de votre dataset SANS filtre (sans WHERE xxxx IN (…), exemple :
SELECT COUNTRY,CUSTOMERNAME
FROM CUSTOMERS
ORDER BY CUSTOMERS.CUSTOMERNAME
  • Dans le Data Set rendez vous dans la partie « Filter » puis rajouter un filtre de type « IN » et renseigner directement le paramètre, dans mon cas « countryList_FILTER ».

FILTER_DataSet

  • Avantages :
    • simplicité d’utilisation
    • pas de scripting
    • peut être fait une fois sur le dataset ou dans chaque objet si le filtre est différent
  • Inconvénients :
    • Sur des tables contenant beaucoup de données, cela peut être très impactant au niveau des performances de récupérer toutes les lignes de la table PUIS de les filtrer dans BIRT
    • La totalité de la table sera ramenée autant de fois qu’il y a d’objet, pour une table de plusieurs millions de lignes, cela peut être très important

Dans le rptdesign fourni dans le fichier Zip, regardez le data set « Customers_FILTER », le report parameter « countryList » et l’objet « Customer Name and country filtered using the « Filter Data Set » mode ».

Deuxième solution : Filtrer directement les données au niveau de la requête en utilisant du script

Il s’agit ici de faire en sortant de filtrer les données directement grâce à la requête SQL. Cela n’étant pas possible avec un « ? », nous allons devoir scripter cela. Deux étapes sont nécessaires : la transformation du paramètre de type multiselect en liste puis l’injection de cette liste dans la requête SQL.

  • Transformer le « report parameter » en liste : se positionner dans la partie de script « beforeFactory »  du rapport et rajouter ce code :
//Converting the Java object to a String object containing a list of value
params['countryList_IN'].value = params['countryList_IN'].value.join();

Le .join() permet de transformer l’objet « Java » en String du type ‘toto’,’titi’ etc…
Et comme nous modifiont une liste de string que nous voulons intégrer dans un « IN » d’une requête SQL, nous devons rajouter des  ».

//Because we have string values, we must surround them by ''. You don't need that with Integer values
params["countryList_IN"].value="'"+params["countryList_IN"].value.replace(/,/g,"','")+"'";
  • Créer la requête SQL : la requête a créé dans notre cas doit avoir une particularité qu’on va pouvoir reconnaître et sur laquelle on va se baser. Pour simplifier la compréhension, on peut utiliser une partie de requête qui ressemble fortement à la requête finale. L’idée ici étant de ne pas écrire n’importe quoi pour rester relativement compréhensible. Nous utiliserons donc le bout de requête suivant :
    WHERE CLASSICMODELS.CUSTOMERS.COUNTRY IN ('myCountry')

    La requête est donc la suivante (Data Set Customers_IN):

    SELECT COUNTRY,CUSTOMERS.CUSTOMERNAME
    FROM CUSTOMERS
    WHERE CLASSICMODELS.CUSTOMERS.COUNTRY IN ('myCountry')
    ORDER BY CUSTOMERS.CUSTOMERNAME
  •  Injecter le paramètre ainsi modifier directement dans la requête : il est maintenant temps d’injecter le paramètre que nous avons modifier dans la requête, à la place du bout de code SQL que nous connaissons. Se placer sur le « beforeOpen » du Data Set et mettre ce bout de code :
    var SQL = this.queryText;
    this.queryText=SQL.replace("IN ('myCountry')","IN ("+params["countryList_IN"].value+")");

Comme vous pouvez le comprendre, la partie « IN (‘myCountry’) » a été remplacer par « IN + le paramètre modifié au préalable ». Au moment de la génération du rapport cette modification se fera juste avant l’exécution de la requête, le IN contiendra donc les bonnes valeurs et non ‘myCountry’.

  • Avantages
    • Permet de filtrer les données du côté MySQL : nous récupérons que les lignes que nous désirons (très important pour de grosse volumétrie)
    • Peux se combiner avec un fonctionnement classique « ? » permettant d’alterner des « = » et des « IN »
  • Inconvénients
    • Complexifie la reprise d’un rapport si de nombreux Data Set utilise cette méthode car il faut penser à regarder le script positionné dans le rapport
    • Les Data Set ne fonctionnent plus en mode « Edition/Preview Result » une fois que ce code est positionné puisqu’au moment de l’édition, le beforeFactory du rapport n’a pas été exécuté, c’est donc un objet Java qu’on tente d’envoyer dans le IN.Il faut alors commenter le script si vous avez besoin d’utiliser le Data Set normalement.

C’est cette deuxième solution que je préfère car je manipule souvent des tables possédant plusieurs dizaines de millions de lignes, j’aime autant que MySQL se charge de remonter que les quelques centaines de lignes dont j’ai besoin :). A vous de voir ce que vous préférez selon votre utilisation.

Vous l’aurez deviné, cela fonctionne avec les IN mais cela peut aussi fonctionner pour tout autre partie de requête que vous souhaitez remplacer lors de la génération de votre rapport.

En espérant que cela vous a aidé.

++

Tags: , , , , , ,

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.