Dynamic OLAP Cube Reports In Excel
Let’s open up our original Excel file. You should have a report like the one below. It is already a pretty useful report, but the requirements could easily be difficult to achieve with a Pivot Table.
Let’s say for example, the Financial Controller (we’ll call him Ken) gives you the following requirements.
1. Make the report so that I can pick a Period and the report automatically shows me P&L data from the 6 months prior to the selected period.
2. I want to be able to choose a Department and only show data for it.
3. I don’t like the P&L as it stands at the moment as it doesn’t give me enough detail but the rest of the report is fine. I want the line for Operating Expenses to be itemised by a more detailed level, i.e. Labour, Travel, Marketing..
So apart from thinking “cheers Ken”, you think that the answer could be simple – Get the BI team to amend the “Summary P&L” to provide more detail for Operating Expenses. BI Team say that they can’t do that as it’s already used in 300 reports that can’t be changed. So you ask them to create a new Set including the extra information and call it “Ken’s P&L”. BI Team says yes they can do that, but they are busy with Phase 3 of the BI roll-out, so how does 3 months time sound? OK you get the picture – Ken wants this next week, but the BI team can’t deliver for 3 months.
You also cant add the detail to your rows – from your knowledge of the cube, you know that the detail exists in Level4 of your Accounts dimension (see left) because the rows already contain an OLAP “named set” based on the accounts dimension and therefore can’t have another set of members based upon that dimension. Try it and see – you’ll get an error like this
Convert To Formulas
The first thing we can do is to is to convert the whole report to OLAP Formulas. This makes the whole report now formula driven (Using CUBEMEMBER and CUBEVALUE functions) and the Pivot Table no longer exists.
Select OLAP Tools>Convert To Formulas from the Pivot Table Options menu.
NOTE: DO NOT check the box that says “Convert Report Filters” = we need them to drive Ken’s request for being able to select a Financial Period and Department. (this is where you could code this using neat Excel stuff & VBA, but why bother when you can drive it using the existing filters?). The result will be as per the screenshot below. Note how you can still select Departments & Fiscal Years.
To see all the new formulas at a glance, a neat Excel Keyboard shortcut is CTRL+`- This allows you to view FORMULAS as opposed to their RESULTS in an Excel sheets (see below).
Note how the row and column headings have changed to CUBEMEMBER functions and the Values to CUBEVALUE functions.
Note also that the CUBEVALUE functions Member_Expression parameters actually refer to Excel Cells ($B$2, $A$54 etc) – this is really useful to note – They are actually referring to Member_Expressions being generated by the CUBEMEMBER functions in the rows and columns AND Member_Expressions generated by the old Pivot Table report filter value cells (in B1 and B2 of the worksheet). This gives us our first clue as to how we can use this to make the report fully dynamic. Press CTRL+` to return to the values view.