This post carries on where we left off in Part 1, with a pre-built Excel Pivot Table report that is connected to your Analysis Services AdventureWorks cube.
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
You’re not as stuffed as you think, because within Excel, you have the power to customise the report to Kens liking.
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.
OK, the next thing we need to do is to move the selection Parameters to a new sheet. Then we can keep the inputs separate from the report itself.
Select a cell within the Pivot Table filter area (in my example Cell A1) and in the Pivot Table and select the option Move PivotTable. Move it to a New Sheet and rename that sheet “Parameters”.
Rename the sheet containing the data as “Report”.
In the Report Worksheet, delete the data columns to the right of June (i.e. columns H:N) as Financial Controller “Ken” said he only wanted a report containing the six months data up to and including the period selected in the report parameter.
Lastly, set up a new Worksheet called “Lists”.
Setting up Parameters, Defined Names and some simple MDX
Cells A1:B2 in your Parameters sheet should now contain the old Pivot Table filters for Fiscal Year and Department. Set up 9 more parameters in the sheet as per the picture below.
I have placed in column D the names you should call your cells in the equivalent rows in column B (for example, B1 should be named pYear, B8 should be named mdxPerSeletced etc). To name a cell, select the cell or range you want to name, select the formulas tab from the ribbon UI and click Define Name
Now we have our ranges named (don’t worry about having no values in B4:B6 and B8:B13 for now), swith to your “Lists” Worksheet and create a table in A1:D13 as per the first picture below. In columns A & B place the values as per the picture 1, in columns C & D place the formulas as per the second picture below. Finally, Name your table (Range A2:D13 as lkpMDXPeriod and Range A2:A13 as lMonths)
Having entered the formulas correctly, you should get the results as seen in picture 1. Now move back to Worksheet “Parameters” and enter the formulas and values (values for cell B6) shown in the picture below.
For cell B4 we do something different. Select the cell, then navigate to the Data Menu tab and select “Data Validation” – set the validation settings to be derived from a List, and specify in the source box, the formula =lMonths
The pPeriod (B6) parameter should now be limited (via a dropdown list when the cell is selected) to the moths of the year (January to December).
So Now we have our Pivot Table “decoupled”, we have a “Lists” worksheet and a “Parameters worksheet set up and in the next post we will move on to formatting our report and satisfying “Ken”, our somewhat fictitious Financial Controller. Along the way, without knowing it you have also constructed some MDX (the multidimensional expression language of SQL Server Analysis Services) which is something that often scares alot of people. More on MDX in Part 3.