View Categories

Gestion avancée des requêtes

Table des matières

Les alias #

L’entête des colonnes n’est pas toujours parlante, il s’agit souvent du nom des zones dans la base de données. Il est possible de donner un autre nom dans la colonne ALIAS de l’espace Colonnes.

Ordre et ajout/suppression d’une colonne #

Pour ajouter, supprimer une colonne ou modifier leurs ordres, un menu est accessible en cliquant sur la flèche:

Requêtes - configuration des champs de la requête

Visualisation d’une colonne #

Les sélecteurs associées à la colonne Sortie de l’espace Colonnes précisent si la ligne apparait dans le résultat final.

Tris #

Les tris sont définis dans l’espace Colonnes. En regard de chaque zone concernée il faut définir l’ordre (1 pour le poids le plus important, 2 pour le suivant et ainsi de suite), et le type du tri (croissant ou décroissant).
Pour supprimer le critère :
– Sélectionnez le type de tri,
– cliquez sur le bouton suppr du clavier.

Groupements et agrégats #

Les groupements permettent de rassembler les enregistrements en fonction de la valeur d’une ou plusieurs colonnes. Par exemple, calculer le nombre de factures par client, ou le CA (Chiffre d’Affaires) par secteur d’activités.

Il s’agit de l’ordre GROUP BY de SQL. Son utilisation impose une contrainte : tous les résultats de la requête (c’est-à-dire toutes les colonnes renvoyées) doivent obligatoirement être:

  • Soit des colonnes participant au groupement
  • Soit des agrégats de colonnes, c’est-à-dire des sommes, des moyennes, des comptages…

Les colonnes participant au groupement sont définies en cochant la boite Groupes de l’espace Colonnes. Les agrégats sont sélectionnés dans la colonne Agrégats au moyen d’une liste de choix. Les principales valeurs sont:

  • AVG pour la valeur moyenne du groupe
  • MIN pour la valeur minimale du groupe
  • MAX pour la valeur maximale du groupe
  • COUNT pour le comptage du nombre d’enregistrement du groupe
  • SUM pour la somme des valeurs du groupe
  • Etc.

Codage du OU logique #

Voici la définition de « DateCommande<’01/02/2010′ ou DateCommande>’30/11/2004′ » :

Requêtes - Codage du OU logique (OR)

Codage du ET logique #

Voici la définition de « CodeClient = ‘VINET’ Et Commandes.DateCommande <’01/10/2010′ » :

Requêtes - codage du ET logique (And)

Remarque: les simples quotes ‘ doivent encadrer les valeurs constantes non numériques (chaînes de caractères, dates…) sinon une erreur sera générée.

Codage autres #

And et Or #

Voici la définition de « [ CodeClient = ‘VINET’ Et Commandes.DateCommande <’01/10/2010′ ] Ou [ CodeClient = ‘HANAR’ Et Commandes.DateCommande >’31/05/2010′ ] » :

Requêtes - Codage du And et Or

Between #

Voici la définition de « CodeClient = ‘VINET’ Et Commandes.DateCommande entre le ’01/05/2010′ et le ’31/10/2010′ » :

Requêtes - codage du Between

Propriétés de la requête #

Note : En fonction du type de SGBD (Oracle, MSSMS, …) les options des propriétés de la requête peut différer.

Requêtes - propriétés de la requête

Un double clic sur l’espace de travail fait apparaître une fenêtre permettant de préciser quelques attributs de la requête (dépend des possibilités de la base de données cible) :

  1. DISTINCT pour ne renvoyer qu’un seul enregistrement en cas de doublons.
  2. TOP afin de ne renvoyer qu’un nombre de ligne limité (nombre fixe ou pourcentage du nombre de lignes de l’ensemble résultat). WITH BY n’est à utiliser qu’avec GROUP BY et un pourcentage.
  3. INTO intègre dans une nouvelle table le contenu de la requête.
  4. GROUP BY ALL génère un groupement sur toutes les colonnes renvoyées. Les options WITH CUBE et WITH ROLLUP (incompatibles avec ALL) génèrent des récapitulatifs sur les différentes valeurs des différentes colonnes, à la manière des cubes utilisés dans les analyses multidimensionnelles. Elles génèrent des colonnes ayant la valeur indéfinie (NULL), surtout WITH CUBE qui génère un regroupement pour chaque valeur de chaque colonne, plaçant un NULL dans les autres colonnes.

Union #

L’Union est le procédé permettant d’ajouter des enregistrements provenant de plusieurs requêtes. Chaque requête devant avoir un résultat ayant le même format, c’est-à-dire des colonnes en même nombre, et compatibles entre elles.

Requêtes - codage Union

Après avoir codifié la première requête, il suffit de faire un clic bouton droit sur l’espace de travail et de sélectionner Union, puis Nouvelle requête Union. Ce procédé est à répéter autant de fois qu’il y a de requêtes à unir. Des boutons R en haut à droite de l’espace de travail permettent de passer d’une requête à l’autre. Pour des codifications complexes, il est possible de définir des crochets qui engloberont une série de requêtes.

Remarque: les opérateurs ensemblistes EXCEPT et INTERSECT existent seulement dans certains SGBD.

Sous-requêtes #

Les sous-requêtes sont utilisées lorsqu’une condition s’appuie sur le résultat d’un SELECT. Par exemple, lister les clients ayant commandé un produit. Il faut tout d’abord trouver toutes les commandes ayant contenu ce produit, puis lister les clients ayant passé ces commandes. L’ordre SQL se codifie ainsi :

Select Distinct
 CodeClient
From
 Commandes
Where
 NumCommande In (
   Select
    NumCommande
   From
    Commandes_Details
   Where
    CodeProduit = 11)
Order by CodeClient

Pour codifier cette requête avec l’interface graphique, il faut procéder ainsi :

  • Placer dans l’espace de travail la ou les tables contenant les colonnes à produire dans le résultat.
  • Sélectionner les colonnes qui participent au résultat et celles qui doivent apparaitre dans les clauses WHERE.
  • Si la colonne participant au WHERE (ici Commandes.NumCommande) n’est pas utile dans le résultat, décochez la case Sortie au niveau de l’espace Colonnes.
  • Dans l’espace Colonnes, au niveau de Critère, après un clic bouton droit sélectionnez l’option Insérer une sous-requête. Il faut être en mode écriture pour pouvoir afficher le menu.
Requêtes - options d'un champ
  • Le squelette d’une requête apparait. Vous pouvez le compléter dans cet emplacement ou au niveau de l’onglet Instruction SQL, qui est plus pratique, si vous connaissez SQL. Sinon utilisez l’interface graphique. Deux onglets sont maintenant disponibles dans l’espace de travail pour cette requête. Le premier, nommé Principale (pour requête principale), représente la requête de plus haut niveau qui contient les colonnes à produire. Le second, nommé pour l’instant Select * From (puis Select Demonstration_A.DB…), contient la requête imbriquée. Il suffit de codifier cette dernière comme nous l’avons fait jusque-là. Noter que le nom de l’onglet évolue en fonction du contenu de l’ordre SQL généré. Ne pas oublier de décocher la boite de la colonne Sortie pour la colonne CodeProduit qui ne doit pas apparaitre dans le résultat de la sous-requête.
  • Dans l’onglet Principale, la clause DISTINCT est codifiée au niveau des propriétés de la requête.
Requêtes - interface avec sous-requête
  • Voici un extrait du résultat renvoyé:
requêtes - résultat requête imbriquée

Gestion des paramètres #

Un paramètre, comparable à une variable, se construit toujours de la même manière. Il est défini par son nom, précédé du caractère deux points « : », et se déclare dans l’onglet Requêtes.
Remarque : il ne doit avoir aucun espace entre les deux points et le nom du paramètre.

Exemple :
« : pCoceClient » → Non valide
« :pCodeClient » → Valide

Requêtes - utilisation champ de type paramètre

Le paramètre créé est disponible dans l’onglet Paramètres. Le paramètre prend automatiquement le format de la colonne qui le contient et reste modifiable selon les besoins.
En renseignant la valeur du paramètre on peut tester la requête.
In fine, nous pourrons donner à l’utilisateur la possibilité de modifier la valeur de ce paramètre des tableaux de bord.
Remarque : pour certain type de base de données (DB2 par exemple), la valeur vide d’un paramètre n’est pas supportée lors de l’exécution d’une requête.

Valeurs nulles #

Vous aurez parfois besoin d’obtenir l’ensemble des valeurs possibles si l’utilisateur ne fait aucun choix (plutôt qu’obtenir aucune donnée). La valeur null est représentée par ‘#NULL’ s’il s’agit d’une chaîne de caractères, ‘1900-01-01’ d’une date, -987 pour un numérique.
il est alors possible d’utiliser l’astuce suivante : (requête d’exemple établie sur la base Demonstration) :

Select
 Commandes.LivraisonPays,
 Commandes.DateCommande,
 Commandes.NumCommande
From
 Commandes
Where
     ( Commandes.LivraisonPays = :CHOIX_Caractères or  :CHOIX_Caractères = '#NULL')
and  ( Commandes.DateCommande = :CHOIX_Date or :CHOIX_Date = '1900-01-01' )
and  (  Commandes.NumCommande = :CHOIX_Numérique or :CHOIX_Numérique = -987 )

 Ensuite, pour tester la requête, positionner ces trois valeurs (sans les guillemets) dans l’onglet paramètres.

Fonctions avancées :

Certains paramètres peuvent interpréter des fonctions, il suffit de les nommer comme les exemples suivants dans la requête :

  •  :BIBOARDCALC
  •  :BIBOARDCALC1
  •  :BIBOARDCALC3
  •  :BIBOARDCALCDATEDEBUT
  •  :BIBOARDCALCDATEFIN
  • etc

→ Il est nécessaire de nommer le début du paramètre :BIBOARDCALC.
Pour renseigner une formule vous pouvez double-cliquer sur le nom du paramètre ou renseigner directement la formule.

Les variables systèmes #

  • _NOW : Date + heure
  • _DATE : Date
  • _TIME : Heure
  • _USER : DOMAIN\USER
  • _PI : 3.14159265

Exemples de formule pour un paramètre :BIBOARDCALC #

Si _DATE = 17/01/2011

  • Jour courant = 17/01/2011

Formule: _DATE

  • Jour précédent = 16/01/2011

Formule: ADDDAYS(_DATE,-1)

  • Premier jour du mois courant = 01/01/2011

Formule: « 01/ » & COPY(_DATE,3,8)

  • Dernier jour du mois courant = 31/01/2011

Formule: LASTDAY(ADDMONTHS(_DATE,-1)) & « / » & COPY(_DATE,3,2) & « / » & YEAR(ADDMONTHS(_DATE,-1))

  • Premier jour du mois précédent = 01/12/2010

Formule: « 01/ » & COPY(ADDMONTHS(_DATE,-1),3,8)

  • Dernier jour du mois précédent = 31/12/2010

Formule: LASTDAY(ADDMONTHS(_DATE,-1)) & « / » & COPY(ADDMONTHS(_DATE,-1),3,2) & « / » & YEAR(ADDMONTHS(_DATE,-1))

  • Premier jour du trimestre courant = 01/01/2011

Formule: « 01/ » &  Format(« 00 »,(1+(QUARTER(_DATE)-1)*3)) & « / » & YEAR(_DATE)

  • Dernier jour du trimestre courant = 31/03/2011

Formule: LASTDAY(« 01/ » & (QUARTER(_DATE)*3) & « / » & YEAR(_DATE)) & « / » & Format(« 00 »,QUARTER(_DATE)*3)& « / » & YEAR(_DATE)

  • Premier jour de la semaine courante = 17/01/2011

Formule: ADDDAYS(_DATE, 0 -(WEEKDAY(_DATE)-1))

  • Dernier jour de la semaine courante = 23/01/2011

Formule: ADDDAYS(_DATE,7-(WEEKDAY(_DATE)))

  • Premier jour de l’année courante = 01/01/2011

Formule: « 01/01/ » & YEAR(_DATE)

  • Dernier jour de l’année courante = 31/12/2011

Formule: « 31/12/ » & YEAR(_DATE)

  • Premier jour du trimestre précédent = 01/10/2010

Formule: ADDMONTHS(« 01/ » & COPY(« 0 » & (1+(QUARTER(_DATE)-1)*3),LENGTH((1+(QUARTER(_DATE)-1)*3)),2) & « / » & YEAR(_DATE),-3)

  • Dernier jour du trimestre précédent = 31/12/2010

Formule: ADDMONTHS(LASTDAY(« 01/ » & (QUARTER(_DATE)*3) & « / » & YEAR(_DATE)) & « / » & COPY(« 0 » & (QUARTER(_DATE)*3),LENGTH((1+(QUARTER(_DATE)-1)*3)),2) & « / » & YEAR(_DATE),-3)

  • Premier jour de la semaine précédente = 10/01/2011

Formule: ADDDAYS(_DATE, 0 -(WEEKDAY(_DATE)-1)-7)

  • Dernier jour de la semaine précédente = 16/01/2011

Formule: ADDDAYS(_DATE,0-(WEEKDAY(_DATE)))

  • Premier jour de l’année précédente = 01/01/2010

Formule: « 01/01/ » & (YEAR(_DATE)-1)

  • Dernier jour de l’année précédente = 31/12/2010

Formule: « 31/12/ » & (YEAR(_DATE)-1)Remarque: grâce aux fonctions du hub les possibilités sont ainsi illimitées: calendrier glissant, emploi de la fonction iff etc…

Paramètres Dynamiques (valeur automatique et non éditable) #

  •  :BIBOARDUSERNAME:  Domaine + Identifiant AD
  •  :BIBOARDUSERNAMEONLY: Identifiant AD
  •  :BIBOARDDOMAINNAME:  Domaine
  •  :BIBOARDDATE:  Date du jour
  •  :BIBOARDDATETIME:  Date et heure du jour
  •  :BIBOARDYEAR:  Année courante
  •  :BIBOARDMONTH:  Mois courant
  •  :BIBOARDTIME:  Heure courante
  •  :BIBOARDDAYMONTH:  Jour du mois courant
  •  :BIBOARDDAYWEEK:  Jour de la semaine
  •  :BIBOARDDAYWEEKNAME:  Nom du jour de la semaine
  •  :BIBOARDWEEK:  Semaine courante de l’année
  •  :BIBOARDWEEKFIRSTFOURDAYWEEK:  Numéro de la semaine (Première semaine de 4 jours)
  •  :BIBOARDWEEKFIRSTDAY:  Numéro de semaine ayant le 1er Janvier
  •  :BIBOARDWEEKFIRSTFULLWEEK:  Numéro de la première semaine entière
  •  :BIBOARDDAYYEAR:  Jour courant de l’année
  •  :BIBOARDUSERLANGUE:  Nom de la langue par exemple Français…
  •  :BIBOARDUSERCULTURE:  Culture utilisateur par exemple fr
  •  :BIBOARDUSERLOGIN:  Nom de l’utilisateur en authentification SQL/SQLQUERY
  •  :BIBOARDTDBNAME: Nom du tableau de bord courant
  •  :BIBOARDTDBID : ID du tableau de bord courant

    Retour en haut