locked
Way to have dimensions repeat in subtotals / grand totals? RRS feed

  • Question

  • Hello,

    In many reports I create I have a number of stacked dimensions in the row area, but sometimes within the subtotals I would like it to repeat the grouping of a lower dimension, not just report a total by a single row.

    Say I have:

    City
    House
    Furniture

    And these are all placed in the row values where Furniture is the lowest dimension in the tree. When it goes to total 'city', would there be a way to have it maintain the breakdown of 'house' for example?

    Currently, the only way I can do this is by removing lower-level dimensions (ie furniture), however for certain reports it would come in very handy if there was some kind of solution!

    Hope this makes sense, thanks.

    Friday, April 8, 2011 3:41 PM

Answers

  • Hi Happy_Trails

    One way to achieve this kind of 'asymmetric' report is by using Cube functions.  In the 'pivot table tools' tab (which appears on top of the Excel ribbon when you select a cell inside the pivot table), go to OLAP tools -> Convert to Formulas.

    Once you do that, each cell will have a reference to a dimension member, set or measure in the PowerPivot engine. You can then add or remove totals, much beyond the layout of a regular pivot table;

     




    Javier Guillen
    http://www.msbicentral.com/Blogs/JavierGuillen.aspx
    Monday, April 18, 2011 2:10 PM
    Answerer