Dans les rapports mensuels pour l'année en cours, les chiffres les plus pertinents pour les périodes fermées sont les chiffres réels, et pour les mois ouverts ou futurs, les prévisions. Dans la plupart des cubes, les chiffres réels et les prévisions (ou le budget) sont mis en œuvre soit comme des mesures différentes, soit comme partie d'une hiérarchie de scénarios. Cela peut entraîner des difficultés dans l'établissement des rapports, car si vous placez la période et les chiffres réels/prévus sur le même axe, vous obtiendrez des mois en double qui montrent les prévisions pour des périodes où les chiffres réels sont déjà présents, comme dans le tableau croisé dynamique ci-dessous.
Nous souhaiterions que le rapport soit asymétrique (c'est-à-dire que les mois retournés soient différents pour le réel et le budget). Différents outils peuvent gérer cela, et il a toujours été possible d'y parvenir dans XLCubed grâce à des formules ou dans une grille. Le plus difficile est de rendre le rapport entièrement dynamique sans devoir le redessiner à chaque fois. L'idéal serait que l'utilisateur puisse changer de trancheur pour sélectionner le dernier mois écoulé (ou le reprendre directement dans le cube), et que les mois appropriés soient reportés dans les rubriques Réel et Budget. Voici comment y parvenir dans une grille....
L'approche utilise une combinaison de :
- Fourchettes Excel pour déterminer les mois disponibles, et qui sont pour Réel et Budget
- Une trancheuse XLCubed pour choisir les "dernières actualités".
- Une grille XLCubed avec la mention "Exclure de l'affichage" pointant vers les plages pertinentes ci-dessus
Voyons cet exemple qui utilise le cube Finance de la base de données AdventureWorks DW 2008R2.
Nous allons utiliser les données de l'exercice 2006 et communiquer les chiffres réels et budgétaires de tous les services. Nous utiliserons un slicer pour sélectionner le dernier mois réel que nous voulons rapporter. Pour les autres mois de l'exercice 2006, nous communiquerons les valeurs budgétaires.
Année entière de Mois dans le rapport
Il existe plusieurs moyens d'y parvenir - nous avons ajouté un trancheur piloté par Excel (XLCubed - Slicer - Excel) basé sur la fourchette $AA$1:$AB$12 comme indiqué ci-dessous, contenant tous les mois de l'exercice 2006. Cela permet à l'utilisateur de choisir le dernier mois pour lequel nous voulons rapporter les chiffres réels.
Dans $C$1, la formule =VALUE(B1) convertit le texte produit par le trancheur en un nombre que nous utiliserons pour calculer quels mois doivent déclarer le réel et quel budget, voir la capture d'écran ci-dessous :
Tableau des dates
Il s'agit d'une plage Excel qui soutient la logique de déclaration. La rangée 2 contient tous les mois de l'année de déclaration. La rangée 3 attribue un numéro supplémentaire à chaque mois, de 1 à 12.
We can then compare the value of the selected month from the slicer ($C$1) with the value for each month. Basically if the month is less than or equal to the selected last actuals month it should be reported as Actuals, otherwise as Budget. An example formula contained in B4 for Actuals would be: =IF(B3<=$C$1,B2,””) . For the Budget row the formula has the inverse logic.
À ce stade, nous avons utilisé un peu de XLCubed et la flexibilité d'Excel pour faire correspondre les mois appropriés aux données réelles et au budget. Nous devons maintenant ajouter le rapport lui-même.
Nous allons créer notre grille - dans un premier temps, nous l'avons configurée pour présenter les chiffres du budget et du réel sur toute l'année, en basant la sélection de Date.Fiscal sur la fourchette contenant tous les mois de l'année ( G$2:M$2).
Nous utilisons ensuite le menu contextuel de XLCubed (Keep - Exclude From Display) pour exclure les membres. Peu importe les tranches qui sont exclues à ce stade. Pour ce faire, sélectionnez "Actual", puis le premier mois disponible et enfin "Exclude From Display". Répétez cette opération pour "Budget" et le premier mois disponible. Nous exclurons juillet 2005 pour "Réel" et août 2005 pour "Budget". Vous remarquerez le marqueur de commentaire rouge sur la grille. Cliquez avec le bouton droit de la souris sur cette cellule et choisissez Axe - Modifier, puis allez à l'onglet Tranches exclues.
Les captures d'écran ci-dessus montrent que nous excluons actuellement le mois de juillet 2005 des données réelles ainsi que le mois d'août 2005 du budget.
Nous pouvons alors facilement modifier la sélection de la date de l'exercice pour chaque scénario. Pour le Budget, nous exclurons les valeurs du Budget pour les mois dans les cellules $B$4:$M$4 (car nous voulons rapporter les Réels pour ces entrées) et les valeurs des Réels pour les mois dans les cellules $B$5:$M$5 (car nous voulons rapporter les Budgets pour ces entrées).
Le rapport de la grille ressemble maintenant à ceci, les chiffres réels étant rapportés jusqu'en novembre 2005 et budgétisés pour le reste de l'exercice 2006 :
Nous avons également activé l'option Fusionner les cellules répétitives (dans Propriétés de la grille). Vous pouvez effectuer un formatage supplémentaire, par exemple en colorant toutes les valeurs de Budget et en masquant les en-têtes de grille.
Le résultat final est un rapport à grille unique donnant la combinaison Réel:Budget du mois que nous voulons avec le processus de maintenance mensuelle, un cas de simple changement de sélection d'une boîte combinée.
