Having created and populated our Paramaters and Lists Worksheets, let’s now turn our attention to the Report Worksheet. Here’s how we left it
With some very simple formula replacements and a bit of formatting, we can turn it in to this
Enter the formula in column B as per the picture below – given the way the parameterized formula is constructed, it should be possible to simply enter =CUBEVALUE(pCube,pDept,pMeasure,$A7,B$6) in cell B7 and copy down to B13 and across to column G.
For Row 6, enter the formulas in the cells as follows
Column A’s formulas can be left as they are for now.
Now you should be able to change the Department, the Fiscal Year and the Month at will in the parameters sheet and the results will be shown in your report, including changing the report name and the column headings (B:G) to correctly reflect the prior six months to the reporting date.
I also feel that workbooks constructed this way are so much easier to audit.
By replacing hard coded cell values with sensibly named named ranges, it makes it easier to see that cell B7 for example refers to the cells immediately above and to the left of it (which are obviously something to do with a date period and an account or group of accounts), and that it’s value is also determined by the variables pCube, pDept and pMeasure. These can easily be located (and the current values of them can be viewed) in the workbook using the Name Manager utility that can be launched from the formulas tab in the Excel Ribbon.
Let’s move on to Ken’s request for changes to the P&L. We haven’t looked at column A yet. Let’s move some things around so that the P&L makes more sense and shorten the formulas in A7 to A13 as follows
Now if we look at the report, we can see that it makes more sense as a P&L… Gross Sales, less Returns, Adjustments & Discounts leaves us with Net Sales, less Cost Of Sales = Gross Margin, less operating Expenses gives us our Net Profit. Hey it’s starting to make sense. But Ken’s still not happy – he needed more detail than just a single line of “Operating Expenses”.
In your Report Worksheet, insert 6 new rows between Gross Margin and Operating Expenses. Also insert a row between Column A and Column B. Now we need to go and find what the accounts are that make up the Operating Expenses – we do this by either using SQL Server Management Studio and browsing the cube or if you don’t have access to the SSMS tools, we can also do it by constructing a quick PivotTable in Excel and finding them out by converting to formulas.
Let’s say Ken wanted the following subdivisions of Operating Expenses
Telephone and Utilities
And wanted the remainder grouped under a heading “Other Expenses”, then here’s the formulas for how I would do it.
Note that cell C18 is basically Total Operating Expenses, less the itemised Operating Expenses that Ken wanted to see.
The end result of the report looks like the picture below – a fully parameter driven Excel Report with customisations that wouldn’t be possible within a standard Pivot Table without some customisation within the Analysis Services cube. Often this isn’t possible in the short term so I hope this servers as a small introduction to the power of the new CUBE functions in Excel, coupled with some standard functionality (lists, names etc) that not everyone is aware of.
Hope this helps