Building pivot tables using cube formulas

Excel 2007 is great for using pivot tables. I especially like the new cube functions since they give far greater flexibility. They also allow reporting from multiple cubes if needed where standard pivot table functionality only allows to report off a single cube.

To create a blank report using just cube functions we need to firstly define our connection. Open up Excel and go to Data, from other sources, and select analysis services. Now select the required options and a cube or perspective, i used AdventureWorks for this example. Name the connection "adv" and select "only create a connection" in the last step.

Now we're ready to place some data into the spreadsheet. let's say we want to build a report that lists the top 20 cities and their sales figures. We will also have a parameter called country which we can change later to for example grab the top 20 cities for the United States or France on the fly. Type Country in cell B4 and United States in C4. This will be our parameter. Next we will use the "cubeset" function to create a set of the top 20 cities from the country listed in our parameter box. The cubeset function wants a connection, set expression and caption. Go to cell B5 and type the following: =CUBESET("adv",CONCATENATE("DESCENDANTS([Geography].[Geography].[All Geographies].[",$C$4,"], 2)"),"Top 20 Cities by Profit",2,"[Measures].[Reseller Gross Profit]"). You will see after typing "=cubeset(" that intellisense takes over. It already has the connection we created earlier and gives you all the cube structures to build the set. We used a simple descendants function concatenate function to string the text together. Also note the reference to $C$4 which tells the descendants function to get the members for United States, or whatever country is in there.

Now your text in cell B6 should read top 20 cities by profit. Directly under that we will create some headings. Type City, Order Qty, Sales Amount, Avg UnitPrice and Profit starting from B7. Next we will use another cube function called "Cuberankedmember". In cell B8 insert the value "=CUBERANKEDMEMBER("adv",$B$6,(ROW(A1)))". This again takes the same connection, and then gets the first member of our set which we defined earlier on. You should see Seattle in the cell. Now simply copy the formula downwards up to cell B27 (20 cells). You should end up with Daleville. If you only want the top 10 or top 50 simply copy the formula down less or more as needed.

At this stage your sheet should look like this:

Next let's populate the values. We will use another cube function called "cubevalue". Under Order Qty, next to Seattle type "=CUBEVALUE("aw",B8,"[Measures].[Reseller Order Quantity]")". It's just getting the cube value in terms of order quantity referencing B8 (Seattle) as the city. Now copy the formula down again all the way up to Daleville. Do the same for Sales amount, Avg Unit Price and Profit. If you see the same values in every cell you forgot to include the reference (B8) in the formula. also make sure you choose the reseller amounts, not internet sales etc. Now your finished sheet will look like this:

I've only added some formatting, conditional formatting etc. to put lipstick on the pig. We now have an OLAP report running purely off formula's and we can of course create more columns, using different connections and then pull in data from other cubes.

Try changing the parameter United states to say France, United Kingdom etc. You will see the data being refreshed on the fly. Very cool.