Methode-import-sigmah

Christophe Paquette - [e-mail] 12/01/2012
METHODE POUR UNE MISE A JOUR AUTOMATIQUE DE TABLEAUX DE BORD
EXCEL A PARTIR DES DONNEES SIGMAH.
Ce document présente une méthode permettant d'actualiser automatiquement un tableau de
bord sous Excel à partir des données de Sigmah. L'intérêt de cette méthode est qu'elle ne
nécessite aucune programmation ni de Macros : les services administratifs et financiers
maîtrisent généralement bien Excel et devraient pouvoir l'appliquer sans difficultés majeures.
Les pré-requis pour créer le tableau de bord automatisé sont donc : être familiarisé avec les
notions de base de Sigmah et bien maîtriser Excel, (en particulier les Tableaux croisés
dynamiques, mais aucune Macro n’est requise). Quelques notions en matière de bases de
données sont souhaitables
1
. Pour l’utilisation du tableau de bord, aucun pré-requis : une fois
qu’il est créé, les mises à jour sont automatiquement faites à l’ouverture du classeur Excel.
Autrement dit, il y a un peu de boulot pour mettre au point l’outil et ensuite tout doit être
automatique (Inch Allah).
N'étant pas moi-même informaticien, excusez mes maladresses et approximations de
vocabulaire technique.
Je précise que notre organisation est en train d’évaluer la possibilité d’utiliser Sigmah : ce
système et le tableau de bord ne sont pas actuellement en utilisation opérationnelle mais
seulement installés dans une configuration de test. Dans notre association, notre tableau de
bord des projets s’appelle « le tableau A3 » : ce nom est utilisé dans l’exemple ci-joint.
Si des collègues d’autres ONG souhaitent me contacter pour des précisions, je leur répondrais avec
plaisir (dans la limite de ma disponibilité de temps !) : cpaqu[email protected]
Problématique et principe de résolution.
Notre objectif est de disposer d’un tableau Excel qui comporte une ligne par projet et une
colonne par rubrique (par exemple : Nom du projet, Responsable, Zone géographique, nom du
partenaire, Date de Début, Date de Fin, Budget, Sources de financement, montant des frais
administratifs).
Il est assez facile d'extraire à partir d'Excel les informations d'une base de données, au moyen
d'une « requête SQL » définie dans l'onglet "Données" d'Excel. C'est d'ailleurs ainsi que seront
extraites les "données de base" d'un projet (nom du projet, dates de début et de fin, budget
prévu, etc.), qui se trouvent dans des tables de structure fixe (une colonne "numéro du projet",
une colonne "nom du projet", une colonne « Date de Début », etc.). La difficulté provient de la
structure "flexible" de Sigmah. En effet, au moyen des "modèles de projets", chaque organisation
peut définir les informations particulières qu'elle souhaite enregistrer dans le système. Ces
informations se situent dans différentes tables liées entre elles par des numéros de référence
(c’est le principe des bases de données relationnelles). Par exemple, si nous créons un champ
"frais administratifs", une première table (la table des libellés, appelée dans Sigmah « table
Flexible_element ») comporte dans une colonne le libellé "Frais administratifs" et dans un autre
son numéro de référence (par exemple 3824). Une autre table (la table des valeurs, appelée
dans Sigmah « table Value ») comportera dans une colonne le numéro de référence (3824) et
dans une autre la valeur (5 480,00 €, par exemple). La solution consiste à extraire l'information
"brute" dans une feuille de calcul et à l'organiser dans une autre feuille au moyen de Tableaux
croisés dynamiques et de la fonction "RecherchV".
1
Je suis parti sans aucune connaissance des requêtes SQL et toute ma science provient d’un site web très
basique : http://sql.1keydata.com/fr/

Christophe Paquette - [e-mail] 12/01/2012
Etapes.
1. Dans Sigmah, identifier les données que vous souhaitez importer dans Excel .
Pour éviter d'importer dans Excel la totalité des informations "flexibles" (de nombreuses
informations ne nous intéressent pas pour le Tableau de Bord), l'astuce consiste à intégrer, lors
de la préparation d'un modèle de projet dans Sigmah, un signe distinctif dans le libellé des
champs qui nous intéressent. Par exemple, au lieu de définir un champ "Frais Administratifs"
nous pouvons l'appeler "Frais Administratifs:" (remarquer les deux points). Nous aurons donc
plusieurs champs ainsi "marqués" dans le modèle de projet ("Frais Administratifs:", "Secteur:",
"Partenaire :", etc.). Une autre solution consisterait à faire des requêtes SQL plus longues (les
requêtes SQL permettent d’extraire des informations d’une base de données, comme expliqué
ci-dessous), cette astuce permet de simplifier.
2. Dans Excel, préparer et enregistrer une requête SQL pour importer les données qui
nous intéressent dans une première feuille.
C'est l'objet de la feuille « Données-flexibles » du classeur Excel d’exemple ci-joint.
2.1. Vérifiez si vous avez dans Excel le « fournisseur de données » pour PostgreSQL. Dans
le ruban « Données », bouton « A partir d’Autres Sources », choisir « Assistant de
connexion de données » puis « Autres/Avancées » et cliquer sur « suivant ». Dans
l’image ci-dessous, le bon fournisseur est surligné. Il n’était pas présent par défaut dans
ma version d’Excel 2007, je l’ai téléchargé sur http://pgfoundry.org/projects/oledb/ .

Christophe Paquette - [e-mail] 12/01/2012
2.2. Indiquez les paramètres de connexion à votre base de
données.
Ces informations sont à demander à l’administrateur
système qui a installé Sigmah dans votre organisation.
Dans mon cas, il s’agit d’une installation de test « locale »
(mon ordinateur est à la fois serveur et client) et les
paramètres sont présentés dans l’image ci-contre.
Cliquez sur « tester la connexion » pour vérifier.
La connexion étant testée, cliquons sur « annuler », nous
reviendrons plus tard sur cette étape.
2.3. Préparer la requête SQL.
Nous avons besoin d’une requête qui importe dans une feuille de calcul des informations qui
se trouvent dans plusieurs tables de la Base de Données Sigmah : Flexible_element, Value et
User_database et nous souhaitons importer quatre informations pour chaque projet : le nom
du projet, le numéro de référence des « champs flexibles » qui nous intéressent (et donc les
champs dont le libellé comporte « : »), ce libellé et la valeur. Cette requête est la suivante :
Select public.userdatabase.name, public.flexible_element.label,
public.value.id_value, public.value.value, public.userdatabase.fullname
From public.userdatabase Inner Join
public.value On public.userdatabase.databaseid = public.value.id_project
Inner Join
public.flexible_element On public.value.id_flexible_element =
public.flexible_element.id_flexible_element
Where public.flexible_element.label Like '%:%'
Un informaticien l’aurait certainement rédigée directement, pour ma part j’ai utilise un outil
graphique (un complément à Excel) appelé “SQL Drill” et construit ma requête comme
indiqué ci-dessous.

Christophe Paquette - [e-mail] 12/01/2012
Copier la requête SQL, qui sera ensuite (voir ci-dessous) collée à l’endroit approprié dans
Excel.
Si vous n’êtes pas intéressés par personnaliser votre requête (par exemple pour importer les
informations dont le libellé comporterait « ; » au lieu de « : »), vous pouvez simplement copier
la requête SQL encadrée ci-dessus.
2.4. Introduire la requête SQL dans Excel.
Retournons dans la première feuille de
notre classeur Excel (la feuille appelée
« Données-flexibles » du classeur Excel
d’exemple ci-joint) et établissons une
connexion avec la base de données
comme expliqué aux points 2.1 et 2.2 ci-
dessus. Cliquer sur OK, puis décocher la
case « Connexion à une table
spécifique », puis cliquer sur Suivant.
Donner un nom au fichier (par exemple
« MonTableau.odc » et cocher la case
« Enregistrer le mot de passe dans le
fichier » et choisir « Oui » en réponse au
message qui apparait (« le mot de passe
sera enregistré sans chiffrement, etc ».),
puis cliquer sur « Terminer ». Dans la
fenêtre qui apparaît, choisir n’importe
quelle table et cliquer sur OK. Dans la
fenêtre « Importation de données » qui apparaît, cliquer sur le bouton « Propriétés… ».
Choisir l’onglet « Définition ». Cocher la case « Toujours utiliser le fichier de connexion et,
dans le menu déroulant « type de commande », l’option « SQL ». Dans la case « texte de la
commande » : effacer le contenu et y coller la requête SQL que nous avons copiée dans
l’étape 2.3 ci-dessus.
Puis : OK.
Le plus compliqué est fait! Nous avons à présent une feuille Excel qui extrait les informations
de la base de données Sigmah. Si vous modifiez une donnée dans Sigmah (par exemple les
« Frais administratifs » augmentent à 6 300 €), vous constaterez qu’il suffit de cliquer sur le
bouton « actualiser tout » dans l’onglet « Données d’Excel » pour que
l’information soit mise à jour dans cette feuille. Il ne reste plus qu’à
ordonner l’information.
3. Dans Excel, créer un Tableau Croisé Dynamique pour regrouper les
informations de type « flexible ».
La difficulté avec les TCD est qu’ils ne peuvent comporter dans la zone
« Valeur » que des chiffres, pas du texte, alors que nous souhaitons
avoir dans notre tableau de bord des informations telles que le nom du
partenaire. Pour contourner la difficulté, nous allons regrouper dans le
TCD non pas la valeur mais le numéro d’identifiant de cette valeur
(id_flexible).

Christophe Paquette - [e-mail] 12/01/2012
Nous créons donc ce TCD dans une deuxième feuille du classeur (TCD-Flexible, dans
l’exemple ci-joint), qui va rechercher les informations dans la première feuille (Données-
flexible) et les ordonner.
Nous obtenons un TCD qui comporte une ligne par projet, les libellés (Nom du projet, nom du
partenaire, frais administratifs, etc.) en entêtes de colonnes et des numéros de référence en
valeurs (le contenu du tableau). Il ne reste plus qu’à remplacer ces numéros de références par
les valeurs, ce qui est l’objet de l’étape suivante.
4. Dans Excel, créer un tableau qui présente les valeurs.
Dans une nouvelle feuille du classeur (dans l’exemple, la feuille « A3 Brut »), nous utilisons la
formule « RechercheV » d’Excel, qui va rechercher dans chaque cellule du TCD le numéro de
référence, puis va rechercher dans la première feuille ce numéro et le remplacer par la valeur. La
formule de base pour la Cellule B3 de notre nouvelle feuille est : RECHERCHEV('TCD-
Flexible'!B3;'Donnees-flexible'!$C:$D;2;FAUX). Autrement dit, identifier dans la feuille TCD-
Flexible le chiffre qui se trouve dans la Cellule B3 puis rechercher dans les colonnes C et D de la
feuille Donnes-flexibles ce chiffre et rapporter la valeur de la deuxième colonne (la colonne D,
c'est-à-dire « value »).
Dans l’exemple ci-joint la formule est un peu plus compliquée simplement pour des raisons
esthétiques : si la donnée recherchée n’existe pas, le résultat est un truc du genre #NA. C’est
moche, et la formule présentée dans le classeur d’exemple permet une présentation plus
satisfaisante.
=SI(ESTNA(RECHERCHEV('TCD-Flexible'!B3;'Donnees-flexible'!$C:$D;2;FAUX));"";RECHERCHEV('TCD-Flexible'!B3;'Donnees-
flexible'!$C:$D;2;FAUX))
Autre petite subtilité : dans la base de donnée de Sigmah, la séparation des décimales est
enregistrée sous forme de virgule dans certaines tables, mais sous forme de points dans d’autres
tables. Nous devons donc allonger un peu notre formule pour remplacer les points par des
virgules, au moyen de la fonction SUBSTITUTE.
La formule finale devient donc :
=SI(ESTNA(RECHERCHEV('TCD-Flexible'!B3;'Donnees-flexible'!$C:$F;2;FAUX));"";SUBSTITUE(RECHERCHEV('TCD-
Flexible'!B3;'Donnees-flexible'!$C:$F;2;FAUX);".";","))
5. Ajouter les informations qui nous intéressent mais qui proviennent d’autres tables de Sigmah..
Voila notre tableau de bord «brut », qui regroupe les informations « flexibles » de Sigmah.
Cependant, d’autres informations nous intéressent, qui se trouvent dans d’autres tables. Nous
allons à présent créer de nouvelles feuilles, qui comporteront les requètes SQL permettant
d’importer ces informations dans notre classeur, puis introduire dans notre feuille « A3 Brut » les
formules appropriées pour aller piocher l’information ainsi récupérée.
5.1 Le nom complet du projet
Il se trouve déjà dans la table « données flexibles » créée à l’étape 2.4. Il suffit d’aller chercher
l’information au moyen de la formule
=SI(ESTNA(RECHERCHEV(A3;'Donnees-flexible'!$A:$F;5;FAUX));"";RECHERCHEV(A3;'Donnees-flexible'!$A:$F;5;FAUX))
5.2 Les informations de base du projet.
Les informations de base se trouvent dans des tables « fixes » et non pas « flexibles » ; en
particulier la table Project. Donc, pas besoin de TCD. Il suffit, dans une nouvelle feuille du