none
Pivot table drill down... How to add additional fact fields RRS feed

  • Question

  • When i double click on a value in a pivot table, i get the facts plus associated dimension values for each fact row.

    How can i add additional fields from the same fact row to the output?

    So, if my fact table currently includes Black, collard, button-down, long-sleeve, $25, ser#12345.

    I have dimensions for the 1st 4 values.  Associated with the price (a fact) is a serial number.  This serial number is in the fact table, not in any dimension.

    When i double click, i get all teh above except the "ser#12345".  How can i add that additional field in the default action of the "double click" drill down?

    A related question... I can also create a "drill-through" action on the underlying cube... but also, it only allows me to choose actual measures, and doesn't allow me to choose other attributes of the fact table, that i would like to display.

    Thanks.


    steve

    Thursday, September 13, 2012 12:03 PM

Answers

  • I have solved my problem.  To display non-numeric facts in drill downs, you have to create a "Degenerate dimension"...or "Fact dimension".  There are of course some (performance) downsides to this.  I have hidden the dimension after creating it.  It still shows up in the drill-down (which is what i wanted).  Users can't see the dimension, preventing them from using it...preventing the user-side performance issues.

    Steve


    steve

    Tuesday, September 18, 2012 2:48 PM

All replies

  • check this link this may help you out

    http://chandoo.org/wp/2010/01/27/pivot-table-tricks/

    Thursday, September 13, 2012 5:29 PM
  • Hi,

    As you described, you mean that after you double- click the value in the Pivot Table, then it will show all the fields in the table, but not the ser#12345 column?

    as I tested in Excel 2010 Pivot Table, when I double click the value in the Pivot Table, there will show a record of the value which I double clicked in another sheet. It includes all the files in the table.

    Then just check whether this column is included in the table which used to create the Pivot Table. Second, if the range of data you didn’t used as a table, then check whether the Pivot Table based on the range include the column. Here’s a simple way to check, just check the field name in the PivotTable Field List, if it is listed in it, then double-click should show the field.


    Jaynet Zhang

    TechNet Community Support

    Friday, September 14, 2012 2:53 AM
    Moderator
  • Jaynet,

      I appreciate your response, but it did not solve my issue.   i should have specified that my pivot table is built from an OLAP cube.  When i click-thru a fact, it brings up all the underlying facts that build up that original number.  The resulting excel sheet also includes all the dimensional descriptors.  Other fields from the underlying fact table are not included.  I am trying to include other fields from the fact table.

       How is that done?  Thanks!


    steve

    Monday, September 17, 2012 12:48 PM
  • I have solved my problem.  To display non-numeric facts in drill downs, you have to create a "Degenerate dimension"...or "Fact dimension".  There are of course some (performance) downsides to this.  I have hidden the dimension after creating it.  It still shows up in the drill-down (which is what i wanted).  Users can't see the dimension, preventing them from using it...preventing the user-side performance issues.

    Steve


    steve

    Tuesday, September 18, 2012 2:48 PM
  • Hi,

    Thank you for sharing your solution to us! It will help the others who meet the same issue.


    Jaynet Zhang

    TechNet Community Support

    Wednesday, September 19, 2012 1:35 AM
    Moderator