Even though linking Excel Reports to SQL Server Analysis Services (SSAS) cubes via Pivot Tables is now a relatively easy thing to do post Excel 2007, many people do not realise that you don’t have to be limited to Pivot Table/Pivot Chart reporting. As an ex-accountant, I know from experience that the reports required are often much more complex than can be readily delivered using a Pivot Table in Excel. In this series of posts, I am going to show you how you can build much richer, customised reports in Excel using the new Cube functions that were introduced with Excel 2007.

This post assumes that the user has a pretty good grasp of Excel concepts such as Named Ranges, Conditional Formatting. Whilst some of the steps in this post could be enhanced via the use of VBA, this is outside the scope of these articles.

If you are wondering what Cube functions are, they can be found under the “More Functions” section of the Formulas tab of the Excel UI (see picture on the left).

The key functions we are going to be covering are

CUBEMEMBER (returns a member/tuple from the cube, taking a Connection parameter, a Member_Expression parameter and an optional Caption parameter)

CUBEVALUE (returns a Measure value from the cube, taking a Connection parameter and a Member_Expression parameter)

The Excel junkies amongst you will already see that the Connection parameter is common to both functions and therefore is an ideal candidate for holding in an Excel Named Range for a number of reasons. Firstly, it makes it easier to construct the formulas and secondly, in many BI deployments you will have different environments (Development, UAT, Production etc). By storing the Connection parameter in one place in the workbook, it makes it relatively easy to “point” the report at the different environments by simply changing one value within the Excel Workbook (as opposed to hundreds of Excel formulas).

Prerequisites

I have assumed that people wishing to develop this type of report have access to the following tools

• SQL Server Management Studio tools (and the ability to connect to an instance of SQL Server Analysis Services). The developer editions of SQL Server are usually free with Technet & MSDN subscriptions but can be purchased separately for around $50. For this post I am using SQL Server 2008 R2, however the approach used should work for all SQL Server versions > SQL Server 2005. You can also obtain 180 day evaluation editions here.
• The AdventureWorks Sample Databases & Cubes (the sample databases can be downloaded here). Note that the AdventureWorks cube will need to be deployed & processed on your development SSAS server before it can be browsed via SSMS or Excel.
• Excel 2007 or Excel 2010
• An enquiring mind

Build a basic report as a starting point

Let’s start by connecting to our OLAP Cube and building a fairly straightforward Pivot Table report. As you get more familiar with the Cube functions this probably won’t be necessary but we’ll start with a full walk-through to cater for everyone

Let’s open up Excel and create our base OLAP Cube report using a Pivot Table as shown in the picture to the left.

 

 

 

 

 

 

 

 

 

Follow the OLAP connection wizard as per below to set up your cube connection and select “Pivot Table Report” after clicking the finish button in step 3 below.

You should now have a blank Pivot Table report ready to populate that looks something like this

Create the Pivot Table report as per the screenshot below –

Columns = Month of Year

Rows = Summary P&L (fount under Accounts>Sets)

Filter = Date.Fiscal Year and Departments

 

 

 

 

 

 

 

 

 

For Part 1 of this series of posts, that’s where we will leave it. Save your Excel Workbook as OLAP PT Report 1 and we’ll carry on in the next post.