Part I: Analysis Services Reporting in Excel
The main objectives in this post are to show some screenshots that demonstrate what you can expect when you complete the exercise and to do all of the preliminary setup on the workbook. The goal of this example is to show how you can create reports in Excel that consume Analysis Services data and provide basic drill up/drill down functionality.
Functionality Overview
Figure 1 shows the empty worksheet I setup to demonstrate basic drilling functionality.
Figure 1: The empty worksheet used by this exercise.
When you click “Default Report”, the report will execute a default Multidimensional Expression (MDX) statement against the Sales cube in the Food Mart 2000 database and return the results to the worksheet. MDX is to Analysis Services what SQL is to SQL Server.
Figure 2: The results of the default report.
In Figure 2 you can see the results of the default report and the MDX used to retrieve data from Analysis Services. One thing you’ll notice here is that the different levels of the Store hierarchy are displayed in a single column. This has advantages over the PivotTable method where levels are displayed in separate columns. One immediate advantage is that the fact columns are always in the same column no matter how many levels are displayed. For example, Figures 3 and 4 demonstrate the column shift that occurs in PivotTables when you drill down/up.
Figure 3: A PivotTable report showing the same data as Figure 2. Notice that Q1 and Q2 are in columns B & C respectively.
Figure 4: Drilling down in a PivotTable causes the columns to shift over to
accommodate the new level that appears. Notice that Q1 and Q2 are in columns C & D.
Back to the default report for this exercise, if you double-click on OR, the report drills down on the data underneath OR.
Figure 5: Drilling down on Oregon
Notice that the MDX from the prior report was copied to the PriorMDX range. This makes it extremely easy to provide a level of undo (aka FlashBack for you Essbase diehards). If you double-click on OR again, the report will drill up which results in the same view as in Figure 2.
Figure 6: Simple drill up/down allows users to determine the amount of detail they want
Setting up the Report Worksheet
The first thing you can do is set the worksheet up as shown in Figure 1. If you’re going to literally follow along, begin by naming the worksheet “Report”.
The code that I’ll be presenting requires three named ranges: CurrentMDX, PriorMDX, and TopLeft. You do not need to populate these cells with any data.
1) Select cell A1
2) Enter ‘Report’!CurrentMDX in the name box and press Enter.
3) Select cell A2
4) Enter ‘Report’!PriorMDX in the name box and press Enter.
5) Select cell A7
6) Enter ‘Report’!TopLeft in the name box and press Enter.
The code that I’ll present later on in the series will refer to these named ranges. The CurrentMDX and PriorMDX are just persistent storage places for MDX statements. I put them directly on the Report worksheet to keep things simple and to provide a live glimpse of the MDX that gets constructed by your code for learning (and occasional debugging J). Normally, I’d use a separate worksheet for the MDX statements and set this worksheet to xlSheetVeryHidden. The TopLeft name is just a “stake in the ground” so that the process that displays the results knows where to dump the data.
For this example, the columns returned are always Q1 and Q2. The display process will not build the column headings, so you should go ahead and enter Q1 in cell B8 and enter Q2 in cell C8.
In the upper right corner of the sheet, I placed two simple drawing objects and added the text “Default Report” to one and “Previous Report” to the other. You’ll attach a macro to each of these in the future. Alternatively, you could use a command button.
That's it for setting up the reporting worksheet. Starting with the next post, I'll go over the code required to make this happen.