02 • 34 • 09 • 31 • 70
Les dernières évolutions (obligatoires et contraintes) d’IBM Planning Analytics entrainent la disparition de Perspective, Architect et le passage à :
- Excel PAFE pour tout ce qui est analyse et reporting
- Workspace pour ce qui est de la modélisation et de l’administration
Ces changements sont pour la plupart bénéfiques et l’on peut notamment citer :
- Protocol http – https pour l’accès de Pafe à TM1
- Nouveaux types de reporting (Rapport Rapide, Rapport Universel)
- Accès depuis partout (http – https) au workspace
- Développement « color-coding » avec le workspace
- Administration, Exploitation avec le workspace
Mais …… et c’est là que le bât blesse :
Des fonctionnalités de Perspective disparaissent, non couvertes par PAFE, notamment :
- Pas de bac à sable sur tous les types de rapports
- Pas d’impression de book de reporting
- Pas de sous-ensemble comme coordonnées DBR(W)
- Pas de consolidation utilisateur (Rollup) accessible en écriture
Nous avons déjà évoqué certains sujets au travers de précédents articles :
Planning Analytics - Saisie aux niveaux consolidés
Planning Analytics - Utilisation de PAFE et tm1ellist
Planning Analytics - Consolidations définies par l'utilisateur
Intéressons-nous maintenant à la possibilité sous PAFE de générer un reporting de masse (book), comme nous en avions l’habitude et l’aisance avec Perspective.
Générer un book dans PAFE (Planning Analytics pour Excel)
À titre d’exemple et de démonstration, nous allons développer un simple report sur la base d’exemple IBM : SData (https://www.next-decision.fr/formations/elaboration-budgetaire/ibm-planning-analytics-for-excel)
Construisons une simple vue sur le cube PNLCube.
Nous allons pouvoir générer un reporting (dynamique), avec suppression des lignes à zéro contenant deux onglets : "Actual" et "Budget".
À des fins de démonstration, nous avons une présentation légèrement différente de ces deux onglets : Actual et Budget
Nous désirons réaliser la chose suivante :
Pour chaque "pays" présent dans le sous_ensemble SBS_Reporting,
- Générer un fichier excel statique
- Ayant le pays dans le nom du fichier
- Données actualisées
- Sans aucunes formules (exploitable par un utilisateur sans licences)
Afin de simplifier le rafraichissement de notre reporting, nous avons créé un onglet "Param" contenant toutes les sélections de point de vue (en l’occurrence le choix sur la dimension Region) et avons affecté chaque sélection des onglets Actual et Budget à cette valeur.
Pour arriver à nos fins, il nous faudra :
- Choisir le sous-ensemble que l’on désire parcourir (par exemple SBS_Reporting)
- Parcourir ce sous-ensemble
- Pour Chaque Membre de ce sous-ensemble
- Mettre la valeur dans les cellules concernées de "Param"
- Rafraîchir totalement le report (les deux onglets)
- Transformer ce report en "Cliché – Snapshot"
- Enregistrer ce fichier avec le nom du "Pays"
1/ Choix du sous_ensemble de la dimension Region que nous allons parcourir :
Nous ajoutons un onglet "Cacher" afin de contrôler et de piloter le balayage du sous-ensemble listant les uns après les autres, les membres de la dimension Region présents dans le sous-ensemble "SBS_Reporting".
Nous allons même offrir la possibilité de choisir ce sous-ensemble, par parcours de la dimension }Subsets_region.
=SUBNM("sdata:}Subsets_Region";"SBS_Subset";"SBS_Reporting";"MEMBER_NAME")
le nombre de membres contenus dans ce sous-ensemble est automatiquement affiché
=SUBSIZ("Sdata:Region";B2)
Construction du tableau à parcourir grâce à l’utilisation de la fonction TM1ElList
Création d’une nouvelle zone nommée au sens excel :
TM1ELLIST(Dimension";Sous_ensemble;;;;;5)
TM1ELLIST("Sdata:Region";$B$2;;;;;5
On vérifie, avec le « gestionnaire de nom » et on enlève les " en trop
Pour générer un tableau avec les valeurs des membres, nous utilisons la fonction « Index » d’excel :
Index ( zone nommée , numéro d’ordre)
Il ne nous reste plus qu’à écrire la macro qui va faire le reste :
- Lire le tableau à concurrence de 9 (Cellule D2) éléments
- Mettre à jour la Cellule : Param!B3
- Appeler le modèle d’api Excel de Planning Analytics pour mettre à jour
- (Fonction RefreshAllData de l’API PAFE)
- Transformer en snapshot (par copier-coller valeur partout)
- Cacher les onglets « Cacher » et « Param »
- Enregistrer ce fichier en accolant le nom du membre, sans macros.
2/ Implémentation du modele d’API VB de PAFE
Deux fichiers IBM sont à importer dans le modelé de macro vba :
CognosOfficeAutomationExample.bas
CognosOfficeMessageSuppressor.cls
Ces fichiers sont disponibles sous https://github.com/IBM/paxapi/raw/master/attachments/Automation.zip
Vous pourrez y trouver notamment nos deux fichiers :
Enregistrez-les sur votre poste afin de pouvoir facilement les importer dans votre modèle vba.
Import de ces deux fichiers dans le modele vba de notre fichier excel:
3/ Texte de la macro
Créez et adaptez ce code vba selon vos besoins :
Option Explicit
Sub Export_Fichiers_Sans_Formules_XLSX()
Dim wsCacher As Worksheet, wsParam As Worksheet
Dim wb As Workbook, wbCopy As Workbook
Dim NbMax As Long, i As Long
Dim valParam As String
Dim chemin As String, nomBase As String, nouveauNom As String
Dim ws As Worksheet
Dim oPA As Object
'---------------------------------------
'--- Obtenir l’objet PAFE Automation ---
'---------------------------------------
On Error Resume Next
Set oPA = Application.COMAddIns("CognosOffice12.ConnectPAfEAddin").Object.AutomationServer
If oPA Is Nothing Then
Set oPA = Application.COMAddIns("CognosOffice12.Connect").Object.AutomationServer
End If
On Error GoTo 0
If oPA Is Nothing Then
MsgBox "API PAFE non accessible. Importez CognosOfficeAutomationExample.bas et reconnectez TM1.", vbCritical
Exit Sub
End If
'---------------------------------------------------------------
'---- Positionnement des variables de boucle et de pilotage ----
'---------------------------------------------------------------
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set wb = ThisWorkbook
Set wsCacher = wb.Sheets("Cacher")
Set wsParam = wb.Sheets("Param")
NbMax = wsCacher.Range("D2").Value
chemin = wb.Path & "\REPORTING\"
nomBase = Left(wb.Name, InStrRev(wb.Name, ".") - 1)
'------------------------------------------------------
'--- Parcourt en boucle des membres de la dimension ---
'------------------------------------------------------
For i = 1 To NbMax
valParam = wsCacher.Range("B4").Offset(i, 0).Value
wsParam.Range("B3").Value = valParam
'--- Refresh PAFE via API
'---------------------------
oPA.RefreshAllData
' Créer la copie temporaire
'---------------------------
wb.SaveCopyAs chemin & "TempCopy.xlsm"
' Ouvrir la copie
'----------------
Set wbCopy = Workbooks.Open(chemin & "TempCopy.xlsm")
' Supprimer les modules VBA
On Error Resume Next
Dim vbComp As Object
For Each vbComp In wbCopy.VBProject.VBComponents
wbCopy.VBProject.VBComponents.Remove vbComp
Next vbComp
On Error GoTo 0
' Convertir toutes les formules en valeurs (sur les feuilles restantes)
' ---------------------------------------------------------------------
Dim ws2 As Worksheet
For Each ws2 In wbCopy.Worksheets
ws2.Cells.Copy
ws2.Cells.PasteSpecial Paste:=xlPasteValues
'ws2.Cells(1, 1).Select ' évite le mode "copie en cours"
Next ws2
Application.CutCopyMode = False
' Supprimer les feuilles "Cacher" et "Param" si elles existent
'-------------------------------------------------------------
Application.DisplayAlerts = False
On Error Resume Next
wbCopy.Worksheets("Cacher").Delete
wbCopy.Worksheets("Param").Delete
On Error GoTo 0
Application.DisplayAlerts = True
' Sauver en .xlsx (sans macro)
' ----------------------------
nouveauNom = chemin & nomBase & "_" & valParam & ".xlsx"
Application.DisplayAlerts = False
wbCopy.SaveAs fileName:=nouveauNom, FileFormat:=xlOpenXMLWorkbook ' .xlsx
wbCopy.Close SaveChanges:=False
Application.DisplayAlerts = True
' Supprimer la copie temporaire
Kill chemin & "TempCopy.xlsm"
Next i
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
MsgBox "Export terminé avec succès (" & NbMax & " fichiers .xlsx sans macro, sans onglets Cacher et Param)", vbInformation
End Sub
Après exécution, nous avons notre résultat tant attendu :
Tous nos fichiers, générés, prêts à être diffusés aux destinataires.
Et... cerise sur le gâteau !
Souvenez-vous, nous avions demandé "la suppression des lignes ne contenant que des zéros". C’est chose faite !
4/ Pour aller plus loin (suggestions) :
Diffusion par email aux destinataires
Créer un attribut "email responsable " dans la dimension Region
L’afficher dans la liste de l’onglet « Cacher » (fonction Dbra)
Modifier la macro pour envoyer chaque fichier résultat à son « Responsable »
Diffusion sous forme de PDF
Adapter la macro pour (au choix)
Enregistrer le fichier final en tant que PDF
Ou
Imprimer en tant que pdf
5/ Synthèse documentaire
Fonctions et objets utilisés :
Subnm (Choix liste déroulante des sous_ensembles de la dimention Region)
TM1ElList (Zone nommée contenant la liste des membres)
SubSize (Donne le nombre d’éléments d’un sous-ensemble)
RefreshAllData (de l’API Pafe vb)
Documentation IBM API PAFE : https://ibm.github.io/paxapi/#introduction
Vous souhaitez bénéficier d'experts, de développeurs, ou d'une formation sur TM1 / Planning Analytics ? Rendez-vous sur la page Contact !
