Creating Free Form Reports using Cube functions in Excel - Follow Up

Back in 2007(!), I wrote a post on creating free form reports using Cube functions in Excel. I noticed that this is still being viewed by some readers. However, the screenshot in the article is missing and I am not able to find the original screenshot to update the post.

Although I do not have access to the original AdventureWorks cube that I had used to create this report, I was able to create a similar report using the following AdventureWorks Excel Power Pivot data model.

As you can see, the sales data is stored in the Sales table and the Budget data is stored in the Budget table. The Sales table contains each transaction at the product level whereas the Budget data is summarized by Category and Period. Also, the Budget table cannot be joined with the Calendar table as 1:N relationship. As such, we will not be able to combine these two tables in a Pivot Table report for comparison purposes (Budget vs Actual).

Therefore, I used the following steps to create a free form report that contains data from both tables and added columns to calculate the variance and variance %.

1. Created a pivot table from the Sales table.

2. Created a pivot table from the Budget table.

3. Converted both pivot tables to convert Pivot Table to Formulas (OLAP Tools > Convert to Formulas).

4. Created a combined report by copying and pasting formulas from the above two reports.

5. Added Variance and Variance % columns.

Here’s the resulting free form report.

Here’s the report showing underlying formulas.

The report uses the following two Cube functions:

CUBEMEMBER

CUBEVALUE

You can get more details on creating free form reports using Cube functions from the original post here.