Fragments de rapports SQL

Cet article vous aidera à personnaliser les rapports SQL avec différentes fonctions. On y apprend comment ajouter des hyperliens dans un rapport, comment extraire l’information de champs MARC spécifiques, comment ajouter un menu d’options dans la page d’exécution du rapport et comment ajouter un tri de résultats.

Informations MARC

Toutes les informations MARC sont dans la table biblio_metadata. Assurez-vous que celle-ci soit dans votre section « FROM ».

Lieu de publication (008/15-17)

SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 16,3 )

Exemples d'utilisations

Rapport pour obtenir une liste de titres et le lieu de publication

SELECT
    title AS "Titre",
    SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 16,3 ) AS "Lieu de publication"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

Rapport pour sortir les titres des notices qui ont « quc » dans le lieu de publication

SELECT
     title AS "Titre"
 FROM 
     biblio_metadata
     LEFT JOIN biblio USING (biblionumber)
 WHERE 
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 16,3 ) = "quc"

Lieu de publication (264 $a)

ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="a"]')

Exemples d'utilisations

Rapport pour obtenir une liste de titres et le lieu de publication

SELECT
    title AS "Titre",
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="a"]') AS "Lieu de publication"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

Rapport pour obtenir la liste de titres dont le lieu de publication est (contient) Montréal

SELECT
    title AS "Titre",
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="a"]') AS "Lieu de publication"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)
WHERE
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="a"]') LIKE "%Montréal%"

Langue (008/35-37)

SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 )

Exemples d'utilisations

Rapport pour obtenir une liste de titres et la langue

SELECT
    title AS "Titre",
    SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) AS "Langue"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)

Rapport qui montre les notices dont la langue dans la zone 008 n’est pas la même que dans la zone 041$a

SELECT
     title AS "Titre",
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) AS "Langue (008)",
     ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]') AS "Langue (041a)"
 FROM
     biblio
     LEFT JOIN biblio_metadata USING (biblionumber)
 WHERE
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) != ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]')

Éditeur (264 $b)

ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]')

Exemples d'utilisations

Rapport pour obtenir le nombre de notices par éditeur

SELECT
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]') AS "Éditeur",
    count(*) AS "Nombre de notices"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)
GROUP BY ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]')

Rapport pour obtenir la liste de titres dont l’éditeur est La courte échelle (contient « courte échelle »)

SELECT
    title AS "Titre",
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]') AS "Éditeur"
FROM
    biblio
    LEFT JOIN biblio_metadata USING (biblionumber)
WHERE
    ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="b"]') LIKE "%courte échelle%"

Année de publication (008/07-10)

SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 )

Exemples d'utilisations

Rapport pour obtenir une liste de titres et l’année de publication

SELECT
     title AS "Titre",
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 8,4 ) AS "Année de publication" 
FROM
     biblio
     LEFT JOIN biblio_metadata USING (biblionumber)

Année de publication (264$ c)

ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]')

Exemples d'utilisations

Rapport pour obtenir une liste de tous titres et leurs dates de publication 264$ c

SELECT
   biblionumber,
   title AS "Titre",
   ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]') AS "Date de publication 264c"
FROM
   biblio
   LEFT JOIN biblio_metadata USING (biblionumber)

Rapport pour obtenir la liste des titres de l’année de publication 2024 (contient « 2024 »)

SELECT
   biblionumber,
   title AS "Titre",
   ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]') AS "Éditeur"
FROM
   biblio
   LEFT JOIN biblio_metadata USING (biblionumber)
WHERE
   ExtractValue(metadata, '//datafield[@tag="264"]/subfield[@code="c"]') LIKE "%2024%"

Ajouter des liens

Des liens seront automatiquement créés vers les notices bibliographiques, les exemplaires, ou les utilisateurs si la colonne contient des biblionumbers, itemnumbers, borrowernumbers, ou cardnumbers.

Attention

La colonne DOIT s’appeler biblionumber, itemnumber, borrowernumber, ou cardnumber.

Lien vers la notice bibliographique

Une colonne « biblionumber » affichera un menu pour voir ou modifier la notice bibliographique correspondante.

SELECT biblionumber,
       title,
       author
FROM biblio

Résultats d'un rapport avec les colonnes biblionumber, title, et author. La première colonne contient les biblionumbers qui sont des liens. Le curseur de la souris est sur le biblionumber 42 et un menu est affiché pour Modifier la notice ou Voir la notice

Lien vers l’exemplaire

Une colonne « itemnumber » affichera un menu pour voir ou modifier l’exemplaire correspondant.

SELECT itemnumber,
       barcode,
       dateaccessioned
FROM items

Résultats d'un rapport avec les colonnes itemnumber, barcode, et dateaccessioned. La première colonne contient les itemnumbers qui sont des liens. Le curseur de la souris est sur l'itemnumber 42 et un menu est affiché pour Modifier la notice ou Voir la notice

Note

Bien que les étiquettes mentionnent les « notices », ce sont bien des liens pour voir et modifier les exemplaires.

Lien vers l’utilisateur

Une colonne « borrowernumber » affichera un menu pour voir le dossier de l’utilisateur correspondant, modifier ce dernier, ou faire un prêt.

SELECT borrowernumber,
       surname,
       firstname
FROM borrowers

Résultats d'un rapport avec les colonnes borrowernumber, surname, et firstname. La première colonne contient les borrowernumbers qui sont des liens. Le curseur de la souris est sur le borrowernumber 42 et un menu est affiché pour Voir l'utilisateur, Modifier l'utilisateur, ou Prêt.

Une colonne « cardnumber » offrira l’option de faire un prêt à l’utilisateur correspondant.

SELECT cardnumber,
       surname,
       firstname
FROM borrowers

Résultats d'un rapport avec les colonnes cardnumber, surname, et firstname. La première colonne contient les cardnumbers qui sont des liens. Le curseur de la souris est sur le cardnumber 23529000126806 et un menu est affiché avec l'option Prêt.

Ancienne méthode (version 21.11 et moins)

Dans la version 21.11 et les précédentes, il était nécessaire de créer les liens avec des concaténations.

Dans l’interface des rapports de Koha, il est possible d’ajouter des liens pour aller vers une notice ou un dossier d’utilisateur. Cela ne donne toutefois pas de bons résultats si vous exportez le résultat du rapport dans un tableur par exemple.

Les liens doivent être dans la section SELECT du rapport.

Lien vers la notice

CONCAT('<a href=\"cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">Voir la notice</a>')

Exemple d’utilisation

Rapport qui montre les notices dont la langue dans la zone 008 n’est pas la même que dans la zone 041$a, avec lien vers la notice

SELECT
     title AS "Titre",
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) AS "Langue (008)",
     ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]') AS "Langue (041a)",
    CONCAT('<a href=\"cgi-bin/koha/catalogue/detail.pl?biblionumber=', biblionumber, '\">Voir la notice</a>') AS ""
 FROM
     biblio
     LEFT JOIN biblio_metadata USING (biblionumber)
 WHERE
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) != ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]')

Lien vers la modification de la notice

CONCAT('<a href=\"/cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=', biblionumber, '\">Modifier la notice</a>')

Exemple d’utilisation

Rapport qui montre les notices dont la langue dans la zone 008 n’est pas la même que dans la zone 041$a, avec lien vers la modification de la notice

SELECT
     title AS "Titre",
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) AS "Langue (008)",
     ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]') AS "Langue (041a)",
    CONCAT('<a href=\"cgi-bin/koha/cataloguing/addbiblio.pl?biblionumber=', biblionumber, '\">Modifier la notice</a>') AS ""
 FROM
     biblio
     LEFT JOIN biblio_metadata USING (biblionumber)
 WHERE
     SUBSTRING( ExtractValue(metadata,'//controlfield[@tag="008"]'), 36,3 ) != ExtractValue(metadata, '//datafield[@tag="041"]/subfield[@code="a"]')

Lien vers la modification de l’exemplaire

CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblionumber, '&itemnumber=', itemnumber, '#edititem', '\">Modifier l\'exemplaire</a>')

Exemple d’utilisation

Liste de titres et cotes (090 $a et exemplaire), avec lien vers la modification de l’exemplaire

SELECT
    title AS "Titre",
    Extractvalue(metadata, '//datafield[@tag="090"]/subfield[@code="a"]') AS "Cote 090$a",
    itemcallnumber AS "Cote (exemplaire)",
    CONCAT('<a href=\"/cgi-bin/koha/cataloguing/additem.pl?op=edititem&biblionumber=', biblionumber, '&itemnumber=', itemnumber, '#edititem', '\">Modifier l\'exemplaire</a>')
FROM
    items
    LEFT JOIN biblio USING (biblionumber)
    LEFT JOIN biblio_metadata USING (biblionumber)

Lien vers le dossier de l’usager

CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">Voir dossier</a>') AS ""

Exemple d’utilisation

Rapport des utilisateurs qui n’ont pas de garant, avec choix de catégorie d’utilisateur et lien vers le dossier de l’usager

SELECT
    cardnumber AS "Numéro de carte",
    surname AS "Nom de famille",
    firstname AS "Prénom",
    dateofbirth AS "Date de naissance",
    CONCAT('<a href=\"/cgi-bin/koha/members/moremember.pl?borrowernumber=', borrowernumber, '\">Voir dossier</a>') AS ""
FROM
    borrowers
WHERE
    categorycode = <<Catégorie d'utilisateur |categorycode>>
    AND guarantorid IS NULL

Ajouter des choix

Koha permet de choisir des variables au moment d’exécuter le rapport. Ceci permet de faire un seul rapport qui pourra servir pour plus qu’une utilisation.

Le principe des choix est de mettre le texte qui sera affiché, suivi d’une barre verticale (ou pipe en anglais), |, puis de la liste de choix, et ce, entre double chevrons (<< et >>).

Ces choix doivent être dans la section WHERE du rapport.

Pour insérer un choix, placez le curseur à l’endroit du rapport où vous voulez insérer la variable (dans la section WHERE), puis cliquez sur le bouton « Insérer un choix » et choisissez la variable à insérer.

Dans la page de création d'un rapport SQL, le bouton Insérer un choix est ouvert et le curseur de la souris se trouve sur l'option Bibliothèques.

Un formulaire apparaîtra pour changer l’étiquette du choix, ainsi que les paramètres du choix.

Formulaire pour insérer un choix de bibliothèque. L'étiquette du choix est par défaut à Bibliothèque. Sous le champ de texte pour l'étiquette, on peut lire L'étiquette pour le champ qui est affichée lorsque le rapport est exécuté, par exemple, Choisir une bibliothèque. Une deuxième section contient les Options du paramètre : Paramètre unique seulement (la requête devrait utiliser =), Inclure une option 'Tous' (la requête doit utilise LIKE), Autoriser des sélections multiples (la requête doit utiliser IN)

Les options du paramètre permettent de déterminer ce qui sera offert comme options à l’utilisateur qui exécute le rapport.

Paramètre unique seulement : lorsqu’on exécute le rapport, on pourra choisir une option parmi la liste. Dans ce cas, la clause WHERE devrait utiliser l’opérateur « = ».

SELECT itemcallnumber,
       title,
       author,
       location
FROM items
LEFT JOIN biblio USING (biblionumber)
WHERE homebranch = <<Bibliothèque|branches>>

Choix de Bibliothèque, le menu déroulant contient toutes les bibliothèques.

Inclure une option « Tous » : lorsqu’on exécute le rapport, on pourra choisir une option parmi la liste et cette liste incluera une option « Tous ». Dans ce cas, la clause WHERE devrait utiliser l’opérateur « LIKE ».

SELECT itemcallnumber,
       title,
       author,
       location
FROM items
LEFT JOIN biblio USING (biblionumber)
WHERE homebranch LIKE <<Bibliothèque|branches:all>>

Choix de Bibliothèque, le menu déroulant contient toutes les bibliothèques, ainsi qu'une option Toutes.

Autoriser des sélections multiples : lorsqu’on exécute le rapport, on pourra sélectionner plusieurs options parmi la liste en maintenant la touche Ctrl enfoncée. Dans ce cas, la clause WHERE devrait utiliser l’opérateur « IN ».

SELECT itemcallnumber,
       title,
       author,
       location
FROM items
LEFT JOIN biblio USING (biblionumber)
WHERE homebranch IN <<Bibliothèque|branches:in>>

Choix de Bibliothèque, le menu déroulant a été remplacé par un champ de sélection multiple.

Ancienne méthode (version 20.11 et moins)

Dans la version 20.11 et les précédentes, il était nécessaire de taper manuellement les codes pour les choix.

Le principe des choix est de mettre le texte qui sera affiché, suivi d’une barre verticale (ou pipe en anglais), |, puis de la liste de choix, et ce, entre double chevrons (<< et >>). La liste de choix peut être un liste de valeurs autorisées :

LOC pour les localisations
CCODE pour les codes de collection
LOST pour les statuts « perdu »
DAMAGED pour les statuts « endommagé »
WITHDRAWN pour les statuts « élagué »
NOT_LOAN pour les statuts de prêt
ou toute autre liste de valeurs autorisées dans votre système

ou une des listes prédéfinies suivantes :

branches pour les bibliothèques
categorycode pour les catégories d’utilisateur
itemtypes pour les types de document
date donnera un petit calendrier pour choisir une date

Par exemple, <<Bibliothèque |branches>> affichera

Alors que <<Choisir la bibliothèque |branches>> affichera

Ces choix doivent être dans la section WHERE du rapport.

Choix de bibliothèque

<<Bibliothèque |branches>>

Exemples d’utilisation

Rapport pour obtenir la liste des utilisateurs expirés par bibliothèque

SELECT
    cardnumber AS "Numéro de carte",
    surname AS "Nom de famille",
    firstname AS "Prénom",
    dateexpiry AS "Date d'échéance"
FROM
    borrowers
WHERE
    dateexpiry <= CURDATE()
    AND branchcode = <<Bibliothèque |branches>>

Rapport pour obtenir la liste des documents perdus par bibliothèque et par localisation

SELECT
    title AS "Titre",
    itemlost_on AS "Perdu le",
    itemcallnumber AS "Cote"
FROM
    items
    LEFT JOIN biblio USING (biblionumber)
WHERE
    holdingbranch = <<Bibliothèque |branches>>
    AND location = <<Localisation |LOC>>
    AND itemlost != 0
ORDER BY
    cn_sort

Choix de catégorie d’utilisateur

<<Catégorie d'utilisateur |categorycode>>

Exemple d’utilisation

Rapport pour obtenir la liste des utilisateurs expirés par catégorie

SELECT
    cardnumber AS "Numéro de carte",
    surname AS "Nom de famille",
    firstname AS "Prénom",
    dateexpiry AS "Date d'échéance"
FROM
    borrowers
WHERE
    dateexpiry <= CURDATE()
    AND categorycode = <<Catégorie d'utilisateur |categorycode>>

Choix de type de document

<<Type de document |itemtype>>

Exemple d’utilisation

Rapport pour créer une liste de titre par type de document

SELECT  
     biblio.title AS "Titre",
     items.itemcallnumber AS "Cote",
     biblio.seriestitle AS "Collection",
     items.itype AS "Type de document",
     items.location AS "Localisation",
     items.barcode AS "Code-barres" 
 FROM 
     items 
     LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
     LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
 WHERE 
     items.homebranch=<<Bibliothèque|branches>> 
     AND items.itype=<<Type de document|itemtypes>>
 ORDER BY 
     items.barcode asc

Choix de dates (entre le … et le…)

 BETWEEN << Date début(aaaa-mm-dd)|date>> AND << jusqu'au|date>>

Exemple d’utilisation

Rapport qui identifie une liste des nouveaux abonnés selon la date d’inscription

SELECT
    surname AS "Nom",
    firstname AS "Prénom",
    city AS "Ville",
    categorycode AS "Catégorie d'utilisateur",
    dateenrolled AS "Date d'inscription",
    dateexpiry AS "Date d'expiration"
 FROM
    borrowers
 WHERE
    DATE(dateenrolled) BETWEEN << Date début(aaaa-mm-dd)|date>> AND << jusqu'au|date>>
 ORDER BY dateenrolled, categorycode, surname ASC

Ajouter un tri de résultats

ORDER BY ...

Un simple ajout d’une fonction « ORDER BY » permet de trier les résultats directement dans le rapport SQL.

Exemples d'utilisations

Pour ajouter un tri de résultats, inscrire à la fin du rapport «ORDER BY» + [nom du champ] + type de tri

Les résultats peuvent être triés selon:

ASC : ordre croissant (ex. A à Z, ou 0 à 9)
DESC : ordre décroissant (ex. Z à A, ou 9 à 0)

Dans cet exemple de rapport, on utilise la fonction «ORDER BY» pour trier les résultats en ordre croissant de codes-barres (voir la dernière portion du rapport)

ORDER BY 
     items.barcode asc

SELECT  
     biblio.title AS "Titre",
     items.itemcallnumber AS "Cote",
     biblio.seriestitle AS "Collection",
     items.itype AS "Type de document",
     items.location AS "Localisation",
     items.barcode AS "Code-barres" 
 FROM 
     items 
     LEFT JOIN biblioitems on (items.biblioitemnumber=biblioitems.biblioitemnumber) 
     LEFT JOIN biblio on (biblioitems.biblionumber=biblio.biblionumber)   
 WHERE 
     items.homebranch=<<Bibliothèque|branches>> 
     AND items.itype=<<Type de document|itemtypes>>
 ORDER BY 
     items.barcode asc

pour obtenir un tri en ordre décroissant, remplacer «asc» par «desc»

ORDER BY 
     items.barcode desc

Voir aussi

Ressources de la communauté Koha

SQL Reports Library (anglais)
Query MARC (SQL reports library) (anglais)
Links (SQL reports library) (anglais)
Runtime parameters (SQL reports library) (anglais)
Schéma de la base de données (anglais)

Tags: