Tableau croisé dans terminal psql

Pour le fun nous avons essayé de reproduire, dans un terminal postgresql, le tableau:

(voir ce billet)

Commençons par afficher par exemple la cellule (cela, avr.) avec

select distinct contrepartie, (select  sum(solde) 
from opérationsv 
where contrepartie = 'cela'
and   an = '2013'
and   mois_n = '04') AS "Avril"
from opérationsv
where contrepartie = 'cela'

Modifions l'instruction SQL de manière telle que la condition contrepartie =  'cela' n'apparaisse qu'une seule fois (dans la requête principale). Dans le même ordre d'idée, transportons la condition an = '2013' de la sous-requête à la requête principale':

select distinct contrepartie, (select  sum(solde) 
from opérationsv B
where B.contrepartie = A.contrepartie
and   B.an = A.an
and   mois_n = '04') AS "Avril"
from opérationsv A
where an = '2013'
and contrepartie = 'cela'

Ces modifications ne changent en rien le résultat obtenu par exécution de l'instruction, c'est-à-dire l'affichage de la cellule (cela, avr.).

Pour compléter la colonne "Avril", il suffit d'enlever la dernière condition:

Il manque le total de la colonne avril. Ajoutons le:

select contrepartie, (select  sum(solde) 
from opérationsv B
where B.contrepartie = A.contrepartie
and   B.an = A.an
and   mois_n = '04') AS "Avril"
from opérationsv A
where an = '2013'
UNION
select 'ZZ-Total', (select  sum(solde) 
from opérationsv B
where  B.an = A.an
and   mois_n = '04')
from opérationsv A
where an = '2013'
order by 1

(la clause UNION rend le distinct superflu)

Pourquoi 'ZZ-Total'? Simplement pour que l'order by le place en dernière position.
Il reste à ajouter les colonnes pour les autres mois, la colonne 'Total' (avec une sous-requête du même genre que celles qui donnent les colonnes mois) et à améliorer le look de l'output.

Bref, voici le fichier bilancroisé.sql qui sera exécuté dans un terminal psql connecté à la base de données bdtest:

\set QUIET
\pset numericlocale on
\pset footer off
\pset linestyle u
\pset title 'Bilan Croisé 2013'
\pset border 2
\o | awk 'NR==4 {y=$0};/ZZ-TOTAL/ {print y};1'
select contrepartie AS "Usage"
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '01') as "Janvier "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '02') as "Février "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '03') as "  Mars  "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '04') as "  Avril "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an
                      and   mois_n = '05') as "   Mai  "
, (select sum(solde)
     from opérationsv B
                      where B.contrepartie = A.contrepartie
                      and   B.an = A.an) as "Total"
from opérationsv A
where an = '2013'
UNION
select 'ZZ-TOTAL'
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   mois_n = '01') 
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   mois_n = '02') 
, (select sum(solde)
     from opérationsv B                     
                      where   B.an = A.an
                      and   mois_n = '03') 
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   mois_n = '04') 
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   mois_n = '05') 
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an) 
from opérationsv A
where an = '2013'
order by 1
;
\pset title
\pset border 1
\unset QUIET
\o

L'output est amélioré grâce aux instructions awk que voici:

1. NR==4 {y=$0}
2. /ZZ-TOTAL/ {print y}
3. 1

Instruction 1: la ligne de séparation (la 4ième ligne) est sauvegardée en y
Instruction 2: si la ligne lue par awk comprend 'ZZ-TOTAL': impression de la ligne de séparation
Instruction 3: la condition 1 est toujours vraie, donc toutes les lignes lues sont imprimées (action par défaut).

Et voici le résultat:

Le tableau est déjà prêt pour recevoir les données du mois de Mai.
Pour les autres mois il faudra modifier le SQL en conséquence.
On peut également construire le tableau à l'envers avec les instructions (contenues dans bilancroisé2.sql):

\set QUIET
\pset numericlocale on
\pset footer off
\pset linestyle u
\pset title 'Bilan Croisé 2013'
\pset border 2
\o | awk 'NR==4 {y=$0};/TOTAL/ {print y};1' | tee bilancroisé2.txt
select mois_n || ' ' || mois AS "Mois"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'Autre') AS "Autre"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'ceci') AS "ceci"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'cela') AS "cela"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'Div') AS "Div"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'GS') AS "GS"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'Loyer') AS "Loyer"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'RN1') AS "RN1"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'RN2') AS "RN2"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'Tél') AS "Tél"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'TV') AS "TV"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an
                      and   contrepartie = 'VOIT') AS "VOIT"
, (select sum(solde)
     from opérationsv B
                      where B.mois_n = A.mois_n
                      and   B.an = A.an) AS "Total"
from opérationsv a
where an = '2013'
UNION
select 'TOTAL'
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'Autre')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'ceci')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'cela')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'Div')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'GS')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'Loyer')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'RN1')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'RN2')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'Tél')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'TV')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an
                      and   contrepartie = 'VOIT')
, (select sum(solde)
     from opérationsv B                      
                      where   B.an = A.an)
from opérationsv a
where an = '2013'
;
\pset title
\pset border 1
\pset footer on
\unset QUIET
\o

Ce qui donne:

Pour autant que les postes de dépenses et de recettes ne changent pas, le SQL ne doit pas être adapté en cas de nouvelles opérations en 2013: les mois s'ajouteront automatiquement.

(Pour la définition de la table et les données voir la fin de l'article précédent)

Sous-totaux dans terminal psql

Nous allons dans un terminal psql (connecté à la base de données bdtest) produire un tableau avec sous-totaux par mois tel que celui construit dans ce billet.
L'instruction SQL se présentera suivant le schéma:

SELECT ligne détail
UNION
SELECT ligne sous-total
UNION
SELECT ligne total

étant entendu que chaque SELECT devra produire le même nombre de colonne avec le même type de données. Ainsi si la 4ième colonne de la ligne détail est une date, il faudra que cela soit aussi une date pour les deux SELECT suivant, ce qui évidemment posera un grave problème. C'est pourquoi nous afficherons dans la ligne détail les dates avec le type 'text'. Toutes les instructions nécessaire à la production du tableau seront placées dans un fichier bilan.sql que voici:

\set QUIET
\pset numericlocale on
\pset footer off
\pset linestyle u
\pset title 'Bilan'
\pset border 2
\o | awk 'NR==4 {y=$0};/SOUS-TOTAL/ {print y;print;print y};!/SOUS-TOTAL/'
SELECT mois_n ||'-' || mois AS "Mois",  '' AS " ", reference,
date_exec::text, crédit, débit,
  (SELECT SUM(solde)
  FROM opérationsv B
  WHERE B.reference <= A.reference
  AND B.an = A.an
  AND B.mois_n = A.mois_n) AS solde,
  contrepartie AS "usage"
FROM opérationsv A
WHERE an = '2013'
UNION
SELECT mois_n || '-' || mois, 'SOUS-TOTAL', '', '',
SUM(crédit) AS crédit, SUM(débit) AS débit, SUM(solde) AS solde,
'' AS " "
FROM opérationsv
WHERE an = '2013'
GROUP BY mois_n, mois
UNION
SELECT 'Ensemble', 'TOTAL', '', '',
SUM(crédit) AS crédit, SUM(débit) AS débit, SUM(solde) AS solde,
'' AS " "
FROM opérationsv
WHERE an = '2013'
ORDER by 1, 2 , 3;
\pset footer
\pset title
\pset border 1
\unset QUIET
\o

La deuxième colonne de la ligne détail est une colonne vide qui laissera la place au mot 'SOUS-TOTAL' et 'TOTAL' pour les deux SELECT suivant.
L'output est envoyé dans awk en vue de quelques améliorations.
Examinons de plus près les instructions awk:

1. NR==4 {y=$0}
2. /SOUS-TOTAL/ {print y;print;print y}
3. !/SOUS-TOTAL/

Instruction 1: la ligne de séparation qui est la 4ième ligne lue est sauvegardée dans y
Instruction 2: toute ligne contenant le mot 'SOUS-TOTAL' est imprimée précédée et suivie de y (ligne de séparation)
Instruction 3: toutes les lignes sont imprimées (action par défaut) sauf les lignes avec 'SOUS-TOTAL' puisqu'elles l'ont déjà été.
Et voici le résultat:

Pour rappel la table opérations ainsi que la vue opérationsv sont définies dans cet article.
Depuis lors les données suivantes ont été ajoutées:

2013-0020 2013-03-28 -956.00 Loyer 
2013-0021 2013-03-31 1874.56 RN1
2013-0022 2013-03-31 -78.45 GS
2013-0023 2013-04-05 -119.23 cela
2013-0024 2013-04-12 -303.54 Tél
2013-0025 2013-04-15 -46.00 cela

Bilans mensuels (variante)

Revenons sur le contenu de cet article. Pour la réalisation des rapports libreoffice, nous avions travaillé avec une vue qui fournissait le mois de l'opération à partir de la date d'exécution (date_exec). Mais cela n'est pas nécessaire. Nous pouvons par exemple prendre comme source de données la vue basée sur la table opérations et créée par cet instruction:

CREATE VIEW opérationsvs AS 
SELECT substr(a.reference::text, 1, 4) AS an,
a.reference,
a.date_exec, 
        CASE
            WHEN a.montant > 0::numeric THEN a.montant
            ELSE 0::numeric
        END AS crédit, 
        CASE
            WHEN a.montant < 0::numeric THEN - a.montant
            ELSE 0::numeric
        END AS débit, 
a.montant AS solde, 
a.contrepartie,
( SELECT SUM( montant )
 FROM opérations b
 WHERE b.reference <= a.reference
 AND extract(year FROM b.date_exec) = extract(year FROM a.date_exec)
 AND extract(month FROM b.date_exec) = extract(month FROM a.date_exec) 
) AS solde_c
   FROM opérations a

Là où on affichait le mois (comme en-tête de groupe, au dessus du trait rouge), on mettra maintenant la date

mais évidemment avec un format approprié:

Reste à s'occuper du groupement:

Et voilà!
Ci-dessous par exemple, les opérations de février (page 3 du rapport) avec le solde relatif aux opérations de février seulement:

Tableau croisé avec des données postgresql (suite)

Nous n'allons plus cette fois comme dans le billet précédent lier directement le tableau à une table postgresql, mais procéder de manière indirecte.
Après appui sur F4 nous choisissons la table "opérations" (de la base de données bdtest) comme source de données. Nous n'appliquons aucun filtre, aucun tri.
Après sélection de l'ensemble de la table (en cliquant sur le rectangle gris du coin supérieur gauche)

nous insérons les données dans le tableur via l'icône "Données dans le texte":

Ensuite comme auparavant nous passons par Données => Table du pilote => Créer, mais au moment de sélectionner la source nous optons pour "Sélection active":

Il reste à procéder en faisant glisser ce qui convient là où ça convient...

pour obtenir ceci:

Cette méthode présente deux avantages par rapport à la précédente:

• apparition de la cellule "Filtrer" (qui refusait obstinément d'apparaître en cas de lien direct)
• les dates sont directement au bon format (alors que précédemment 30/12/12 s'affichait 41273)

Cliquer sur "Filtrer" nous permet de ne retenir que les dates de 2013:

Il reste à grouper par mois (via F12):

Attention: en cas de modification des données, il faut cette fois actualiser la feuille qui contient les données (et qui elle est liée à la table postgresql):
Ouvrir le navigateur avec F5:

Double-cliquer sur la plage de donnée adéquate (par défaut Importer1 si on n'a effectué qu'une importation), puis actualiser la plage:

En principe le tableau croisé devrait être mis à jour automatiquement, ou sinon procéder comme précédemment: clic droit sur le tableau puis choisir 'Actualiser' dans le menu contextuel qui surgit.

Tableau croisé dynamique avec des données postgresql

Dans le billet précédent, nous nous sommes efforcés de construire dans Libreoffice Calc un rapport du même genre que celui que nous avions obtenu dans Libreoffice Base. Mais Libreoffice Calc permet beaucoup plus. Nous allons ici construire à partir des mêmes données postgresql un tableau croisé dynamique (pour la définition de la table et de la vue utilisée, ainsi que pour les données: voir ce billet).

On commence par utiliser le menu Données => Table de Pilote => Créer:

Nous supposons que le fichier bdtest.odb permettant la connexion à postgresql existe et qu'il a été enregistré:

Nous sélectionnons la source de données opérationsv qui correspond à une vue définie au niveau de postgresql:

Dans la fenêtre qui surgit, il suffit de faire glisser ce qui convient là où ça convient:

"an" placé dans la zone "Champs de page" nous permettra de filtrer le tableau suivant l'année:

Il reste à formater convenablement les cellules et c'est terminé:

Plutôt que de placer "mois-n" et "mois" dans la zone "Champs de colonne", on pourrait y placer "date_exec". On arriverait alors à un tableau tel que celui-ci (dont nous n'affichons qu'une partie):

(Il faut mettre la ligne "contrepartie" au format date)

Ensuite appuyant sur F12 après avoir sélectionné une date, nous avons la possibilité de grouper par mois:

ce qui transforme radicalement le tableau:

En plaçant "crédit" et "débit" dans la zone "Champs de données", on aboutirait  à ceci:

En cas de changement de données au niveau de postgresql, il suffit de cliquer droit sur le tableau qui nous intéresse et dans le menu contextuel qui surgit, choisir "Actualiser".

Utilisation données postgresql dans lbo calc

Dans le cadre de ce blog, nous avons toujours parlé de connexion entre Libreoffice Base et une base de données postgresql. Ainsi par exemple suite à une connexion vers la base de données bdtest, il en est résulté un fichier bdtest.odb qui contient non seulement les paramètres de connexion

mais aussi tout ce qui a pu être créé (requêtes, formulaires, rapport).
Ainsi on peut y obtenir ce rapport basé sur les données figurant dans une table opérations:

(voir ce billet) 

Nous allons montrer ici comment utiliser Libreoffice Calc pour réaliser un rapport de ce genre.

Ouvrons Libreoffice Calc et appuyons sur F4 (EDIT: CTRL-MAJ-F4) pour afficher les sources de données:

Si bdtest.odb n'y figure pas, il faut l'ajouter via Outils=> Options => Libreoffice Base => Base de données=> Nouveau

Après que bdtest ait été référencé, nous pouvons y choisir comme source de données pour ce rapport la requête opérationsr (la même qui a été utilisée dans ce billet) et nous filtrons sur l'année 2013:

Ensuite nous trions suivant mois_n et référence:

Après avoir sélectionné l'ensemble des données, il reste à les insérer dans le tableau avec l'icône "Données dans le texte":

Ensuite passant par le menu Données => Sous-totaux:

nous avons la possibilité de calculer les sous-totaux pour chaque mois:

Dans l'onglet "Options", nous n'oublions pas de décocher la case "Trier" car d'une part les données ont déjà été triées, d'autre part le tri positionnerait par exemple "Février" avant "Janvier".

Le reste est du peaufinage:

• nous formatons de manière adéquate les cellules numériques,
• déplaçons la colonne "contrepartie" après la colonne "solde_c",
• déplaçons la colonne "reference" après la colonne "mois",
• supprimons les colonnes "an" et "seq" et masquons la colonne "mois_n".

Et voici le résultat:

Notons que pour déplacer une colonne, il faut la sélectionner (en cliquant sur l'en-tête) et ensuite procéder avec la souris tout en maintenant la touche 'Alt Gr' enfoncée. Pour la colonne "reference", il faut au préalable insérer une colonne vide avant la colonne "date_exec".

Nous pouvons aussi décider de masquer les détails:

(ne pas sélectionner les deux dernières lignes)