{"id":1484,"date":"2015-11-25T12:57:29","date_gmt":"2015-11-25T12:57:29","guid":{"rendered":"http:\/\/turtlellc.com\/?p=1484"},"modified":"2017-09-22T19:56:00","modified_gmt":"2017-09-22T19:56:00","slug":"dynamic-excel-cubevalue-and-cubemember-functions","status":"publish","type":"post","link":"https:\/\/www.turtle.works\/knowledge\/dynamic-excel-cubevalue-and-cubemember-functions\/","title":{"rendered":"Dynamic Excel CUBEVALUE and CUBEMEMBER Functions"},"content":{"rendered":"

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.<\/p>\n

Problem<\/h3>\n

The CUBEMEMBER function used an array of values for retrieving data:<\/p>\n

=CUBEMEMBER(“AW BI Cube”,{“[Date].[Calendar Weeks].[Calendar Year].&[2014]”,”[Account].[Accounts].&[47]”}<\/strong>)<\/p>\n

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:<\/p>\n

    \n
  1. [Date].[Calendar Weeks].[Calendar Year].&[2014]<\/li>\n
  2. [Account].[Accounts].&[47]<\/li>\n<\/ol>\n

    Because it’s\u00a0an array we can’t simply replace &[2014] with\u00a0&A1. Where A1 = [2015]. Excel won’t accept cell references within an array.<\/p>\n

    Solution<\/h3>\n

    If we read the CUBEMEMBER documentation<\/a><\/p>\n

    \"https:\/\/support.office.com\/en-us\/article\/CUBEMEMBER-function-0f6a15b9-2c18-4819-ae89-e1b5c8b398ad\"<\/a>
    CUBEMEMBER Documentation<\/figcaption><\/figure>\n

    we discover that a cell range can be used instead of an array constant. Now we just need to:<\/p>\n

      \n
    1. Convert the 2 member array to a cell range with 2 cells<\/li>\n
    2. Swap out cell references for the hardcoded values<\/li>\n
    3. Clean up the report<\/li>\n<\/ol>\n

      Exercise<\/h3>\n

      Using AdventureWorks I’ve constructed a report by creating a Pivot Table and then Converting to Formulas<\/p>\n

      If we look at the formula in B5, we see the hardcoded year.<\/span><\/p>\n

      \"After<\/a>
      After Convert to Formulas<\/figcaption><\/figure>\n
        \n
      1. Let’s add 6 rows at the top to work in<\/li>\n
      2. In A2 enter the value [2014]
        \n
        \"Added<\/a>
        Added [2014]<\/figcaption><\/figure><\/li>\n
      3. Copy the array in to Notepad\n

        \"Array1\"<\/a>
        Array1<\/figcaption><\/figure><\/li>\n
      4. Break it down to 2 elements
        \n        \"Array2\"
        \n<\/a><\/li>\n
      5. Put the elements into B2 and B3<\/li>\n
      6. \u00a0Change the formula in B2 ( [2014]) to use a Cell Range with absolute position ($)\n

        \"Range<\/a>
        Range w Cell Ref<\/figcaption><\/figure><\/li>\n
      7. Change the formula in B11 to use a Cell Range\n

        \"B11<\/a>
        B11 After Range<\/figcaption><\/figure><\/li>\n
      8. \u00a0Duplicate the Cell Range in B2 and B3 for C thru H by highlighting the 2 cells and dragging the corner over to column H<\/li>\n
      9. Update the Account number to match what is in row 11 for each column\n

        \"Account<\/a>
        Account Number<\/figcaption><\/figure><\/li>\n
      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.\n

        \"Column<\/a>
        Column Header Formulas<\/figcaption><\/figure><\/li>\n
      11. Change B10s formula for Column Labels to use a Cell Ref\n

        \"Column<\/a>
        Column Label<\/figcaption><\/figure><\/li>\n
      12. Test it by changing A2 from [2014] to [2013]\n

        \"Test<\/a>
        Test 1<\/figcaption><\/figure><\/li>\n
      13. Cleanup\n
          \n
        1. \n
            \n
          1. Add a drop down list at A5 for year<\/li>\n
          2. Change A2 to a Cell Ref pointing to A5<\/li>\n
          3. Hide rows 1 thru 3<\/li>\n
          4. Below is the completed report\"Finished<\/a><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

            Finished Report<\/li>\n<\/ol>\n

            So there you have it. A dynamic report using CUBEMEMBER even though it contained arrays. The method is the same for CUBEVALUE.<\/p>\n

            I want to thank Saad Shah<\/a> for partnering on this solution.<\/p>\n","protected":false},"excerpt":{"rendered":"

            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 … <\/p>\n

            Continue reading “Dynamic Excel CUBEVALUE and CUBEMEMBER Functions”<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":1529,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6,10],"tags":[],"class_list":["post-1484","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-business-intelligence","category-excel"],"_links":{"self":[{"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/posts\/1484","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/comments?post=1484"}],"version-history":[{"count":1,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/posts\/1484\/revisions"}],"predecessor-version":[{"id":1703,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/posts\/1484\/revisions\/1703"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/"}],"wp:attachment":[{"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/media?parent=1484"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/categories?post=1484"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.turtle.works\/knowledge\/wp-json\/wp\/v2\/tags?post=1484"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}