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.