In monthly reporting for the current year the most pertinent numbers for closed periods are the actuals, and for open or future months the forecast. In most cubes actual and forecast (or budget) are implemented as either different measures, or as part of a scenario hierarchy. This can lead to challenges in reporting, where putting both the period and the actual/forecast on the same axis means you will get duplicated months which show the forecast for periods when the actuals are already in as in the PivotTable below.
Handling Asymmetric Reporting
We would want the report to be asymmetric (i.e. different months returned for Actual and Budget). Various tools can handle that, and it’s always been possible to achieve that in XLCubed through formulae or in a grid. What’s more difficult is to make the report fully dynamic without the need to redesign it each time. Ideally we’d want the user to be able to change a slicer to select the last completed month (or pick this up directly from the cube), and have the appropriate months reported under Actual and Budget. Here’s how to achieve it in one grid.
Approach Overview
The approach uses a combination of:
- Excel Ranges to determine the available months, and which are for Actual and Budget
- An XLCubed Slicer to pick the ‘Last Actuals’
- One XLCubed grid with the ‘Exclude from display’ pointing to the relevant ranges above
Step-by-Step Example Using AdventureWorks DW 2008R2
Let’s work through this example which uses the Finance cube on the AdventureWorks DW 2008R2 database.
1. Data Preparation
We’re going to use data from FY 2006 and report Actual and Budget figures across departments. We will use a slicer to select the last Actual month we want reported. For the remaining FY 2006 months we will report Budget values.
2. Including an Entire Year of Months in The Report
There are several ways to achieve this – we added an XLCubed Excel driven slicer (XLCubed – Slicer – Excel) based on the range $AA$1:$AB$12 as shown below, containing all months in FY 2006. This lets the user pick the last month for which we want to report Actuals.
3. Configuring the Slicer Output
The slicer is set to output its selection into $B$1. In $C$1 the formula =VALUE(B1) converts the text output of the slicer into a number which we’ll use to calculate which months should report Actual and which Budget, see the screenshot below:
4. Creating the Date Table
This is an Excel range supporting the reporting logic. Row 2 contains every month in the reporting year. Row 3 assigns an incremental number to each month, 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.
5. Creating the Grid
At this stage we’ve used a little XLCubed, and the flexibility of Excel to match the appropriate months to Actual and Budget. We now need to add the report itself.
We’ll create our grid – initially we set it up to report Budget and Actual figures across the year, by basing the selection for Date. Fiscal on the range containing all months for the year ( $B$2:$M$2).
6. Excluding Members Using XLCubed
We then use XLCubed’s right-click menu (Keep – Exclude From Display) to exclude members. It doesn’t matter which slices are excluded at this point. To do this highlight ‘Actual’, followed by the first available month and then choose Exclude From Display. Repeat this for ‘Budget’ and the first available month. We will exclude July 2005 for Actuals and August 2005 for Budget. You will notice the red comment marker on the grid. Right-click on this cell and choose Axis – Edit, and go to the Excluded Slices tab.
The above screenshots show that we are currently excluding July 2005 from Actual as well as August 2005 from Budget.
7. Adjusting the Date Fiscal Selection
We can then easily change the Date Fiscal selection for each scenario. For Budget we will exclude Budget values for months in cell locations $B$4:$M$4 (as we want to report Actuals for those entries) and Actuals values for the months in cell locations $B$5:$M$5 (as we want to report Budgets for those entries).
8. Finalizing the Grid Report
The grid report now looks like this with Actuals being reported up to November 2005 and Budgeted for the remainder of FY 2006:
9. Additional Formatting Options
We have also set Merge Repeating Cells on (in Grid Properties). You could do further formatting – for example by colour filling all Budget values and hiding the grid headers.
The end result is a one grid report giving the Actual : Budget month mix that we want with the monthly maintenance process a case of simply changing a combo box selection.
