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

https://support.office.com/en-us/article/CUBEMEMBER-function-0f6a15b9-2c18-4819-ae89-e1b5c8b398ad
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.

After Convert to Formulas
After Convert to Formulas
  1. Let’s add 6 rows at the top to work in
  2. In A2 enter the value [2014]
    Added [2014]
    Added [2014]
  3. Copy the array in to Notepad

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

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

    B11 After Range
    B11 After 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

    Account Number
    Account Number
  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.

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

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

    Test 1
    Test 1
  13. Cleanup
      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 reportFinished 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.

Leave a Reply

Your email address will not be published. Required fields are marked *