Dynamic Excel CUBEVALUE and CUBEMEMBER Functions

We recently ran into a situation where a client needed to change the filter in an Excel report that is using the CUBEVALUE and CUBEMEMBER functions. The report was built using a pivot table going against a cube. We then used Convert to Formulas so we could control the report format. This approach worked great for the current data, but lacked a simple way to update the report every month by selecting a new period from a drop down. After some struggles and some seemingly dead ends, we had enough information to solve the problem.

Problem

The CUBEMEMBER function used an array of values for retrieving data:

=CUBEMEMBER(“AW BI Cube”,{“[Date].[Calendar Weeks].[Calendar Year].&[2014]”,”[Account].[Accounts].&[47]”})

The bolded segment above is the array. We know it’s an array because of the braces {} surrounding the values. The array has 2 values:

1. [Date].[Calendar Weeks].[Calendar Year].&[2014]
2. [Account].[Accounts].&[47]

Because it’s an array we can’t simply replace &[2014] with &A1. Where A1 = [2015]. Excel won’t accept cell references within an array.

Solution

If we read the CUBEMEMBER documentation

we discover that a cell range can be used instead of an array constant. Now we just need to:

1. Convert the 2 member array to a cell range with 2 cells
2. Swap out cell references for the hardcoded values
3. Clean up the report

Exercise

Using AdventureWorks I’ve constructed a report by creating a Pivot Table and then Converting to Formulas

If we look at the formula in B5, we see the hardcoded year.

1. Let’s add 6 rows at the top to work in
2. In A2 enter the value [2014]
   

   

3. Copy the array in to Notepad

   

4. Break it down to 2 elements
   
   
5. Put the elements into B2 and B3
6.  Change the formula in B2 ( [2014]) to use a Cell Range with absolute position ($)

   

7. Change the formula in B11 to use a Cell Range

   

8.  Duplicate the Cell Range in B2 and B3 for C thru H by highlighting the 2 cells and dragging the corner over to column H
9. Update the Account number to match what is in row 11 for each column

   

10. Duplicate the formula in B11 by dragging the corner all the way to column H. This changes each formula to use the Cell Ranges in rows 2 and 3 your column headers should update but have the same values.

    

11. Change B10s formula for Column Labels to use a Cell Ref

    

12. Test it by changing A2 from [2014] to [2013]

    

13. Cleanup
    1. 1. Add a drop down list at A5 for year
       2. Change A2 to a Cell Ref pointing to A5
       3. Hide rows 1 thru 3
       4. Below is the completed report

    Finished Report

So there you have it. A dynamic report using CUBEMEMBER even though it contained arrays. The method is the same for CUBEVALUE.

I want to thank Saad Shah for partnering on this solution.