DB2 Web QUERY

DB2 Web Query

Pour utiliser DB2 Web Query, lancez votre navigateur et saisissez l'URL suivante :

http://<votre-as400>:12331/webquery/, puis signez vous :

Les différents rapports sont enregistrés dans des DOMAINES (ici BASEVINS par exemple) ces derniers pouvant contenir des sous dossiers à volonté.

Cliquez sur le sous dossier de création et choisissez "Nouveau/rapport", qui lance un assistant.

Indiquez la table (le fichier base de données) avec lequel vous souhaitez travailler

(ici le détail des commandes, pour faire des statistiques de vente)

NB pour l'administrateur, cette base de données peut être téléchargée à l'adresse suivante
ftp://www.redbooks.ibm.com/redbooks/SG247214/qwqcent.savf

Vous voilà sur la fenêtre principale de conception de rapports

La liste des champs disponibles est en haut à gauche

Le menu Vue propose le choix quant à l'affichage des champs

Structure / Par nom de zone (si vous connaissez bien la base)

Liste pour découvrir l'origine des champs

le tri latéral représente le critère de tri des lignes :
- Si vous optez pour un rapport détaillé (option Détail) les lignes sont triées
- Si vous optez pour un rapport récapitulatif (option Somme, par défaut) les lignes sont regroupées
Dans le cadre Requête, glissez les champs à traiter :
- Option détail, les champs sont affichées tel que.
- Option Somme,
  - les champs sont cumulés, s'il sont numériques
  - la dernière valeur du groupe est affichée dans le cas contraire
    
    Demandons le total commandé par type de produit
Contrairement à la version précédente, nous avons un aperçu temps réel

Si l'aperçu prend trop de temps à s'afficher (il est réactualisé à chaque modification)
Utilisez l'option (aperçu en direct revient à l'affichage par défaut)

A tout moment vous pourrez demander l'exécution, un aperçu ou une trace SQL

Sauvegardez le rapport par ,

puis lancez le

Pour voir le détail.

Attention, votre travail sauvegardé, n'est visible que pas vous, il faut le Publier pour qu'il soit visible par tout le monde.

Pendant la phase de conception, l'aperçu est lancé sur un nombre de lignes limité (ici 100)

Si vous remplacez par la valeur "tout", l'aperçu affichera la même chose que l'option exécuter.

puis optez pour Détail , ce qui donne

Vous pouvez aussi

retravailler la présentation des colonnes

Faite apparaître les options du champs

pour les valeurs numériques, "Format d'édition"

Ne modifiez PAS le type de format, mais jouez sur les options

Vous pouvez faire des choix multiples,
par exemple CM (C=Édition des séparateurs + M=symbole monétaire à gauche)

redéfinir les entêtes d'impression

Enfin vous pourrez utiliser d'autres fonctions que la somme

Par exemples, ajoutons le N° de commande et comptons les commandes (fonction Compte)

Remarquons les fonctions suivantes :

(Aucune)	en mode détail -> la valeur en mode récapitulatif -> somme de la valeur	préfixes (entêtes et pieds de page)
Somme	Somme des valeurs pour le groupe	SUM
Moyenne	la moyenne pour le groupe	AVE
Compte	Nombre de lignes dans le groupe	CNT
Compte distinct	Nombre de valeurs différentes	CNT.DST
Pourcentage de décompte	% par rapport au nombre de valeurs	PCT.CNT
valeurs distinctes	Somme des différentes valeurs	DST
Première valeur	première valeur du groupe	FIRST
Dernière valeur	dernière valeur du groupe	LAST
Valeur max.	plus grande valeur du groupe	MAX
Valeur min.	plus petite valeur du groupe	MIN
Total	Total général (hors groupe)	Total général (hors groupe)
Pourcentage	% de la somme par rapport au total	PCT
Pourcentage de la ligne	% par rapport au total de la ligne (tri latéral)	RPCT
Carré moyen	Calcule la somme moyenne des carrés pour l'écart type (analyses statistiques)	ASQ

résultat :

Si vous demandez un pourcentage, il sera calculé sur l'ensemble des données

Ici, le prix en % du total des vins possédés

la clause WITHIN permet de demander un % par rapport à ...

Ici nous demandons à ce que chaque fois soit noté en % du total de l'appellation (et non de l'ensemble du fichier)

de fait, cette appellation ayant trois vins, la somme des trois fait 100 % !

Autres fonctionnalités :

Création de nouveaux champs

DEFINE, permet de créer un nouveau champ basé sur des données scalaires (calcul réalisé ligne à ligne)
COMPUTE permet de créer un nouveau champ basé sur des données agrégées (calcul réalisé une fois par total)

Le bouton , affiche la liste des zones

vous pouvez jouer sur la notion de format pour retravailler une zone, particulièrement les dates :

D : affichage du jour (1 à 31)

M affichage du mois (1 à 12)

Y affichage de l'année (01 à 99)

YY affichage de l'année (0001 à 9999)

T associé au mois (TM ou MT) affichage le mois sur 3 lettres en majuscules

t associé au mois (tM ou Mt) affichage le mois sur 3 lettres la première en majuscule

TR , comme T, mais le mois est entier

tr, comme t mais le mois est en entier

Q, affiche le n° de trimestre (1 à 4)

W, seul : affiche le N° du jour de la semaine
avec un format de date affiche le Nom du jour en Majuscule

w, idem W, mais seules la première lettre est en majuscule

WR, idem W mais le nom est en entier

wr, idem WR, seules la première lettre est en majuscule

JUL, format julien (N° de jour dans l'année)

YYJUL, format julien avec l'année sur 4 positions

Vous pouvez combiner plusieurs options :

DMY, affiche par exemple 08/08/07

WDMTRY , affiche WED, 8 AUGUST 07

YQ, affiche 07Q2 (Quarter en Anglais)

le séparateur par défaut est le /, mais vous pouvez en préciser un autre

D.M.Y, affiche par exemple 08.08.07

les formats I6YMD et surtout I8YYMD permettent de représenter une date numérique (6 ou 8 chiffres)

Vous pouvez alors définir une zone calculée sur cette nouvelle zone (la zone intermédiaire, pouvant ne pas être affichée)

Mais vous pouvez aussi saisir un calcul et/ou une fonction afin de calculer une nouvelle valeur :

les opérateurs + , - , / , * sont admis (QTE * PRIX, par exemple) , ainsi que ** pour l'élévation à la puissance

fonctions de chaînes

LENGTH(chaîne) , donne la longueur d'une chaîne ou d'une variable
SUBSTR(chaîne, départ, lg) , extrait une chaîne de caractères
UPCASE(chaîne) , conversion en majuscules

LOCASE(chaîne) , conversion en minuscules

Par exemple,
vous pouvez définir une nouvelle version minuscules d'une zone

La placer en tant que filtre

Et ainsi, accepter une saisie en minuscules

LCWORD(chaîne) , passe le premier caractère de chaque mot en majuscules, le reste en minuscules
RJUST(chaîne) , cadrage à droite
LJUST(chaîne) , cadrage à gauche
TRIM(chaîne), retrait des blancs aux deux extrémités
EDIT(zone, 'masque') transforme la variable avec le masque indiqué
Le masque pouvant contenir
- 9 : le caractère à cette position est répercuté tel que.
- $ : le caractère à cette position est supprimé.
- (autre), ce caractère est ajouté.
- rien, transformation en caractère simple

fonctions numériques
- ABS() valeur absolue
- SQRT() racine carrée
- MAX(zone1 , zone2) , retourne le plus grand des deux
- MIN(zone1 , zone2) , retourne le plus petit des deux
fonctions dates
- AYM(datenum, nb , 'FORMAT') , ajoute à une date numérique i4YM ou I6YYM un nombre de mois au format demandé
- AYMD(datenum, nb , 'FORMAT') , ajoute à une date numérique i6YMD ou I8YYMD un nombre de jours au format demandé
  par exemple AYMD(datcmd , 90 , 'I8YYMD'), si datcmd est numérique 8,0
- DATECVT(datenum , 'FORMAT-NUM' , 'FORMAT-DATE')'
  - par exemple DATECVT(datcmd , 'I8YYMD' , 'YYMD') transforme un decimal 8 en date
- HGETC(l , 'FORMAT') retourne le timestamp en cours
  - l = 8 ou 10
    - 8 Timestamp avec millisecondes
    - 10 Timestamp avec microsecondes
  - FORMAT
    - HYYMDS pour un timestamp
- HDATE( timestamp , 'FORMAT') transforme un timestamp en date
  - HDATE(HGETC(8 'HYYMDS) , 'YYMD) retourne la date du jour en ISO
- DATEADD(date, 'code' , nb), ajoute nb jours|mois|années (suivant code) à date (de type DATE), nb peut être négatif
  - Y on ajouté des années
  - M on ajoute des mois
  - D on ajoute des jours
  - WD on ajoute des jours dans la semaine (si la date est un samedi ou dimanche, on passe au lundi suivant)
  - BD on ajoute des jours travaillés
    - par exemple DATEADD(datliv , 'D' , -15) : on retranche 15 jours
- DATEDIFF(date1 , date2, 'code') fait la différence entre deux dates
  - mêmes codes que DATEADD
- DATEMOV(date , 'destination'), passe la date à la destination demandée
  - EOM : fin de mois (End Of Month)
  - BOM : début de mois
  - EOQ : fin de trimestre
  - BOQ : début de trimestre
  - EOY : fin d'année
  - BOY : début d'année
  - EOW : fin de semaine
  - BOW : début de semaine
  - NWD : prochain jour de semaine
  - NBD : prochain jour travaillé
  - PWD : précédent jour de semaine
  - PBD : précédent jour travaillé
  - WD- : jour en cours ou précédent jour de la semaine (si Week-End)
  - BD- : jour en cours ou précédent jour travaillé
  - WD+ : jour en cours ou jour suivant de la semaine (si Été)
  - BD- : jour en cours ou jour travaillé suivant
- les mots-réservés suivants peuvent être utilisés, mais produisent du CHAR (plutôt pour les entêtes et pieds de page)
  - &TOD : heure de lancement
  - &DATE : date du jour (format américain M/D/Y)
  - &DMY : date du jour, année sur 2
  - &DMYY : date du jour, année sur 4
  - &YMD et &YYMD sur le même principe
Fonctions système

GETENV, retourne une variable d'environnement
GETUSER, retourne l'utilisateur en cours
HHMMSS, retourne l'heure en cours
TODAY, retourne la date du jour (en caractère)

Fonction particulière
- DECODE zone (code1 remplacement1 code2 remplacement2 ELSE par-défaut)
  remplace des codes par des libellés, par exemple :
  DECODE status ('Mr' 'Monsieur' 'Mme' 'Madame' 'Mlle' 'Mademoiselle' ELSE 'non précisé')

Tous ces traitements peuvent s'enchaîner :

Exemple, deux zones caractères concaténées avec un zone numérique éditée (transformée en caractère)

| représente la concaténation simple, || la concaténation avec élimination des blancs

Pour voir la liste complète des fonctions , demandez de l'aide

Vous pouvez aussi télécharger la documentation ici (ce PDF, donne la liste des fonctions)

Enfin vous pouvez utiliser nativement toutes les fonctions SQL, sous la forme SQL.nom-fonction

Par exemple :

SQL.DAYNAME(ORDERDATE) retourne le nom du jour de commande (Lundi, Mardi, etc...)

SQL.MONTHNAME(ORDERDATE) retourne le nom du mois

SQL.WEEK_ISO(ORDERDATE) retourne le n° de semaine

SQL.DAYOFWEEK(ORDERDATE) retourne le n° de jour (1=Dimanche, 2=Lundi)

SQL.DAYOFWEEK_ISO(ORDERDATE) retourne le n° de jour (1=Lundi)

Sélection d'enregistrement

Créons à l'identique la zone Année (AN) , basée sur ORDERDATE, puis cliquons sur l'onglet "critères de sélection"

Faites glisser le champ sur lequel porte la sélection (ici la nouvelle zone AN)

Choisissez l'opérateur de comparaison (égal à , supérieur, dans une liste de valeurs, etc...)

Vous pouvez ici, saisir une valeur dans la zone "Ajouter"

OU bien cliquer sur valeur et choisir une option

Extraire toutes les valeurs de la source, affiche une liste des valeurs présentes dans le fichier base de données
(Extraire les valeurs du fichier sur disque, affiche les valeurs stockées dans un fichier à plat ou un fichier Excel)

Inviter, permet de saisir une valeur à l'exécution

Dynamique (résultat d'un select)
Statique (liste de valeur fournies à la conception)
Inviter en utilisant une zone de texte (saisie manuelle d'une valeur simple)

Saisissez 2009 ou affichez les valeurs (extraire toutes les valeurs) et sélectez 2009

Le Filtre étant créé, il apparaît dans la liste des filtres et peut être pour ajouter des critères

Filtre avancé affiche cette fenêtre :

ou vous pourrez ajouter des critère (ici Insérer après)

tableaux croisés

Dernier point, nous pouvons afficher des tableaux croisés ou à double entrée

Pour cela, nous allons ajouter le trimestre dans la partie "Tri horizontal"

(rappel, l'aperçu n'affiche que les 500 premières lignes, par défaut)

Style conditionné

Créons un rapport trié par type de produit et catégorie (PRODUCTTYPE / PRODUCTCATEGORY)
qui affiche le montant et les prix de revient (LINETOTAL / COSTOFGOODSOLD) et la différence entre les deux (zone calculée LINETOTAL - COSTFOGOODSOLD) :

Prenez l'option Plus/Conditions de feux de signalisation

Renseignez les paramètres :

et cliquez sur Style

Indiquez une police avec ces attributs (Gras par exemple) et une couleur

et voilà

Nouveau en V2, l'option "Barres de données"

Activée, affiche ceci :

Vous pouvez afficher un total général

Par colonne -->

Par ligne (tri horizontal) ->

Vous pouvez aussi afficher des sous totaux

Sélectez (dans la colonne Tri vertical), la zone de rupture concernée

et utilisez :

Simple si vous souhaitez faire la sommes des valeurs affichées (dans notre cas, c'est parfait)
Recalculées, si vous souhaitez recalculer la formule

par exemple, si vous avez une colonne pourcentage
- simple, vous fait la somme des pourcentages (aucun intérêt)
- Recalculés, vous refait le pourcentage au niveau total (le % global)

cliquez ensuite sur l'option Sous total

Indiquez ici, les colonnes dont il faut faire le total (par défaut, toutes les sommes)

puis le texte à associer aux sous totaux

Vous pouvez définir un critère de tri qui ne soit pas un critère de rupture

Ajoutez la zone dans les tri (ici bénéfice)
Définissez, votre critère de tri (croissant/décroissant)
(cela doit ne pas changer les ruptures, uniquement définies pour PRODUCTTYPE et PRODUCTCATEGORY)
Vérifiez bien que ce critère de tri vient Après le type de produit (glissez le à la souris, s'il le faut)
Vous pouvez en profiter pour afficher le rang, sur chaque ligne

correspond au préfixe RNK (par exemple RNK.FAMILLE) peut aussi être utilisé dans un calcul

Là encore, vérifiez bien la hiérarchie des tris

Et voilà

Pour terminer cette étape, le rang peut constituer un critère d'affichage (uniquement les 10 premiers pour un TOP10)
Limite

Enfin, vous pouvez aussi paramétrer l'affichage en indiquant entête et pied de page (Cliquez sur rapport )

Puis En-tête et Pied

(ici, nous avons utilisé &DMY et &TOD)

Vous pouvez utiliser dans ces entêtes et pieds de page

une variable système sous la forme &NOM
- &TOD : heure de lancement
- &DATE : date du jour (format américain M/D/Y)
- &DMY : date du jour, année sur 2
- &DMYY : date du jour, année sur 4
- &YMD et &YYMD sur le même principe
une variable sous la forme <NOMVARIABLE> (variable du fichier ou calculée, affichée dans le rapport ou non)
une référence de page
- <TABPAGENO> : N° de la page en cours
- <TABLASTPAGE> : nombre total de pages
un préfixe sur une variable, correspondant aux fonctions d'agrégation vues plus haut
- exemple : Montant le plus élevé : <MAX.MONTANT>
plus un des trois préfixes réservés aux entêtes et pieds de page
- ST : Sous total
- CT : Cumul
- TOT : Total général

Cela permet de résoudre un problème pourtant simple dans l'absolu, faire apparaître le nombre de lignes listées

demander à créer un nouveau champs : DEFINE pour une liste simple, COMPUTE pour une liste récapitulative (somme)

,dans lequel vous placez 1

masquez le, sur le rapport

Insérez un pied de page, avec le texte suivant :

Paramètres en entrée

Allez dans la liste des zones, glissez un nouveau filtre (ici le type de produit), Cliquez"Inviter"'

Si vous choisissez Statiques, vous fournissez les valeurs saisissables
Avec Dynamiques, vous faites référence aux valeurs présentes dans la base de données

Indiquez un libelle :

Dans tous les cas, "sélection multiple" permettra le choix de plusieurs valeurs (touche ctrl du clavier)

Premier affichage

Après un choix

Remarquez que nous avons placé le contenu de la zone ProductType dans l'entête

Nous pouvons alors, lier deux rapports entre eux :

Modifions le premier rapport créé :

Activez la colonne souhaitée, cliquez sur son nom dans la barre d'outils (afin d'avoir les options propres à cette colonne), puis liens

Cette option s'affiche :

L'option "Zoom en profondeur" permet de réaliser une action complexe

cochez Rapport (Page Web, donne l'accès à une page web externe au produit)

Indiquez le rapport à lancer (parcourir permet d'en voir la liste)

Le bouton permet d'ajouter un paramètre

indiquez ici

Le nom du paramètre en entrée dans le rapport à lancer (identique)

la valeur à transmettre en choisissant une colonne (normalement la même que le choix de départ)

Divers

Jointure

Cliquez sur Nouveau, choisissez un fichier base de données

Choisissez un fichier

Résultat :

Faites la jointure (ici entre ORDERDATE et DC_DATE) à l'aide la souris

Puis indiquez en cliquant sur la flèche rouge
- le type de jointure
  - Interne : uniquement les enregistrements en correspondance
  - Externe Gauche, tous les enregistrements de gauche, même si la correspondance n'est pas possible
- Confirmez les zones de jointure (l'information commune) dans les deux fichiers
  
  Du coup, la liste des zones disponibles est modifiée en conséquence
  
  NB : il est possible que l'administrateur est prévu pour vous des données déjà mises en relation
  - Soit en créant des vues regroupant logiquement les données
  - Soit en mettant en place l'intégrité référentielle, qui est automatiquement analysée lors de la création des méta-données
    (liste des zones et des caractéristiques, interne au produit Web Query)

Options "Format"
- HTML, page WEB (en général, dans l'onglet principal))
- PDF, format Acrobat Reader
- Power Point, sera très utile pour les graphes
- Excel (97/2000) et Excel avec formule, dans le dernier cas, les cumuls contiennent la formule SOMME(--:--) d'Excel
- Rapport actif : page HTML active, utilisable en mode autonome
  - chaque colonne peut être un critère de tri (Sort) , de sélection (Filter), la base d'un calcul (Calculate)
  - Vous pouvez créer des tableaux croisés dynamique (Pivot)
  - et des graphes (Chart)

Le PDF actif offre des service analogues (acceptez, s'il le faut, la connexion au site adobe)

Même chose pour le Flash Actif (il faut avoir Flash Player)

le Bouton IM permet d'obtenir un "mini" InfoAssist à l'exécution

Format , propose de choisir le format à l'exécution

Segment, de choisir les segments (OLAP) et de limiter aux n premières lignes
,

glissez une colonne pour créer un segment

Exécution avec InfoMini

De nombreuses options de formatage sont proposées (Menu Accueil)
Affichage de bandes

Après avoir choisi la couleur
Style du rapport
Choix de la feuille de style (fichier .sty propriétaire)

FRblack_dark.sty	FRblue_light1.sty
FRteal_medium1.sty	FRturquoise_theme.sty

•Format/Navigation

Table des matières permet un choix dynamique des informations à afficher

, cliquez sur le bouton en hait à gauche

SI vos critères de tri sont multiples (par exemple Pays/Région/type de produit)

La table des matières sera affichées arborescente

•Accordéon, affiche la même arborescence, mais directement dans la page

•Pages sur demande, affiche ce ruban en bas de page, permettant un accès direct à un numéro de page

•Zoom auto et Analyse permet d'activer ou non, les fonctions OLAP

Enfin l'option DB2 et imprimante propose une sortie dans un fichier DB2 IBM i

Vous devez alors indiquer, la bibliothèque et le fichier
- Vous auriez pu aussi indiquer l'option Imprimante et choisir un nom d'imprimante
- En ligne, représente l'affichage dans la page web (par défaut)

En version 2.1 l'option se présente comme ceci

Vous aurez en plus le choix d'enregistrer en tant que fichier à plat (dans l'IFS)

de nombreux formats étant proposés