Introduction

coresuite cube, based on Microsoft Analysis Services, is a flexible tool. We can access it via browser, iPad, Excel and much more. This time I want to focus on the Excel. For doing self-BI (self-service business intelligence), users just connect to the cube and work with pivot tables. It’s pretty fast, but sometimes limited. In this blog, I show how to make a more individual Excel report also containing budgets using the Excel formulas CUBEVALUE and CUBEMEMBER.

See the difference between a pivot table and cube formulas

For the first step, I just connected to a given cube, which is called SQL 2012 – Revenue and arranged some filters and dimensions to create a small revenue report by months in the year 2008.

When I click on any part of the pivot table, I don’t see any formula. To learn, how those formulas work, I transform the pivot table by using the button “OLAP Tools > Convert to Formulas”.

After converting to formulas, it is possible to click into the cells and directly read out the formulas. There are 2 types:

• CUBEMEMBER: This type of formula helps to define dimensions
• CUBEVALUE: This type of formula helps to define facts / measures

It is possible to access via formula each property and measure of a cube. This gives us following advantages:

• The design of the Excel report is completely flexible
• I can mix different data, which is not possible with a simple pivot table
• I can add more own formulas, for example to compare actuals with budget

Working with CUBEVALUE

Let’s try to make a cube formula by ourselves. In this example, I have an item group called IBM Drucker. I want to see the revenue of this item group in the year 2008. First, I only work with the formula CUBEVALUE. The first part of the formula is the connection. In my case it is the SQL 2012 – Revenue. Excel gives all the connections available, just select it.

The next step is the member expression. It is necessary to write the ” ” before and after every expression and also use the ; to divide the different parts of the formula. Excel lists all the possible member expressions. Because I want to see the revenue, I navigate to Measures and choose the TotalLC. I could now already close the bracket and Excel calculates the TotalLC without any filtering.

But I want to see the revenue of the item group IBM Drucker in the year 2008. For this reason, I extend the formula. First, I filter for the item group. Excel shows again automatically the different member expressions. But the item group name itself needs to be put manually into the formula.

The next step is to filter the revenue by the year 2008. This is just a new member expression with the year.

Working with CUBEMEMBER to split by item groups

The goal of this small Excel-project is a revenue-budget report by month and item groups from a certain year. So, to work only with CUBEVALUES is not very flexible. We need to introduce CUBEMEMBER. Instead of filtering each time the item group in the CUBEVALUE formula, I want just to link the CUBEVALUE to a CUBEMEMBER.

I create now a CUBEMEMBER for my item group IBM Drucker. When the Excel cell B3 shows IBM Drucker, the CUBEMEMBER is correct. Otherwise, the Excel cell shows #N/A.

Consider, I want to create those CUBEMEMBER for more than one item group. I write the name of the item group into column A and just change the CUBEMEMBER in column B by linking it to column A. Important is to correctly use the ” ” and [ ].

Now I use this formula for all my 4 item groups by dragging it.

If there will be a new item group, just write the new name into the column A and extend the formula in column B. Now we see, why it is easier than working only with CUBEVALUE.

Working with CUBEMEMBER to split by month

The next step is to split the revenue by months in the year 2008. For this, I need to create CUBEMEMBER which indicates the month and a filter for the year. I just write the year in cell A1 and create a CUBEMEMBER for the January in cell C2.

Now I copy the formula and adapt the months (“1” for January, “2” for February) by hand.

Mixing CUBEMEMBER with CUBEVALUE

I create now the value for HP Drucker in January 2008. I use again the CUBEVALUE like already done earlier by using the TotalLC measure. But instead to key in manually other member expressions manually, I just link to the cell which I have done before.

Take care to use correctly the $ sign. It indicates, that for the item group the column B is fix and for the months the row 2. Now I can copy-paste the formula into the cells. I also put budget values into the columns. Now I have a simple report containing actuals from the cube and budgets manually entered into Excel.

Conclusion

This was just a very simple example of working with formulas done in some minutes. Excel is a very powerful tool with unlimited possibilities. Consider also the chart functionality to make your report more meaningful.