SSAS and Dynamic Excel Reports

Excel can be a very powerful tool for allowing power users to analyze data in cubes.  However it is also possible to create dynamic reports using Excel.  This can be accomplished using Excel Cube functions.  Cube functions allow you to create the same type of functionality achieved in a pivot table but in a individual cell.  Then utilizing other Excel functions it is possible to parameterize the report making it dynamic.

For this example, I will use one of the Adventures Works Cubes.  The first step is to create a pivot table that contains the information we want.  For this example I created a comparison of Internet Gross Profit sales for current month, prior month and the same month a year ago.  This data is further divided by primary sales territory.

Personally I found that using the Cube Functions in the formula editor to be complicated.  Thankfully there is a menu option in Excel 2007 that can help.  It is called ‘Convert to Formulas’ and is found in the PivotTable Tools –> OLAP Tools menu.

Once this table has been converted we can analyze a few key cells to understand what is going on. The first is A3 where the measure is located. (All cell references relate to the image of the pivot table above.) This cell now contains the formula:

=CUBEMEMBER("AdventureWorksDW",
"[Measures].[Internet Gross Profit]")

The function CUBEMEMBER allows you to select the measure.  If you edit the text you can select other measures, just delete all the text inside quote after this ‘[Measures].[’ and Excel will offer other options.

The second cell we want to inspect is B8 (or any other value cell.)

=CUBEVALUE("AdventureWorksDW",$A$3,$A8,B$7)

You can see that this cell just contains the name of the data source and references to other cells.  One thing to note is that not all cells that were in the pivot table are used by the cube functions so they can be deleted.  Here is my cleaned up table.

The next step is to parameterize the dates reference in my converted table so that we can make the report dynamic.  Evaluating the cell D4 will help us do that.

=CUBEMEMBER("AdventureWorksDW","[Date].[Calendar].[Month].&[2004]&[5]")

You can see that the year and month number are used, however this will vary depending on how the time dimension in your cube is setup.  The first step is to place the date to drive the report into cell B1.  Then change the formulas in B4, C4, and D4 to use this date.

I replaced the formulas in cells B4 and D4 with the ones below.

B4: =CUBEMEMBER("AdventureWorksDW", CONCATENATE("[Date].[Calendar].[Month].&[",YEAR(B1)-1,"]&[",MONTH(B1),"]"))

D4: =CUBEMEMBER("AdventureWorksDW",
CONCATENATE("[Date].[Calendar].[Month].&[",YEAR(B1),"]&[",MONTH(B1),"]"))

The only difference is we subtract 1 year from cell B4.  Cell C4 is a little more difficult because when the current month is January the prior month is December of the prior year.  If it is January the we need to add an if statement to see if we need to subtract 1 form the the year and set the month to 12.  If not we can just subtract 1 from the month number.

C4: =CUBEMEMBER("AdventureWorksDW", CONCATENATE("[Date].[Calendar].[Month].&[",IF(MONTH(B1)=1,YEAR(B1)-1,YEAR(B1)),"]&[",IF(MONTH(B1)=1,12,MONTH(B1)-1),"]"))

The final spreadsheet now looks like the image below but now the data in the table, which is pulled from the cube can be updated by changing the date in cell B2.

This is a straightforward example but could easily be expanded to handle more complex cases.  Graph data can also be updated using the same methodology.  So it is possible to see that this now gives you a complete report authoring environment that you can give to Excel power users and allow them to build their own reports from the cubes your organization has built.