locked
link on a pivot table to see the data behind summary figures RRS feed

  • Question

  • Hello,

    I need your support because I am not sure if what I need to do is possible.

    I have a flattened table and I have created several pivot tables and slicers based on it. Once I have created the scenario I want to analyze (using slicers), I get some summarized figures in the pivot table. What I need is a way to put a link on those figures to allow me to see the data associated to them.

    For instance, if after applying some slicer I get one figure 3, another 2 and grand total 5, I need to see the 2, 3 or 5 records behind them filtered in the existing data (not just double-click and create a new worksheet) .

    Thanks in advance for your comments,

           Parseval


    • Edited by Parseval Wednesday, July 30, 2014 3:50 PM
    Wednesday, July 30, 2014 3:28 PM

Answers

  • Hello Mike,

    I have found a workaround to get it. It is not elegant but it works. The trick is to add in the filter section the same fields we have in the slicers (except of course if some of them is in the rows section). Then you can hide those filters and it seems you are doing everything with the slicers. When double-click, you will see just the records in scope.

    Let me know if I am unclear.

    Thank you very much for your response

    • Marked as answer by Elvis Long Thursday, August 7, 2014 8:22 AM
    Thursday, July 31, 2014 10:40 AM

All replies

  • Hyperlinks in pivots are not supported.

    There is some limited drill through functionality built into pivots, but you can't do a lot to configure how it behaves.

    If it is enabled, which I believe it is by default, you show be able to double click on a aggregated total and a new sheet will pop up with the underlying records.  If your measure is a relatively simple aggregation and the underlying data is a flat file then this should work pretty well.  The results can be unexepected if the measures are complex or there are a lot of underlying relationships in the model.

    The other possibility is to use the pivots ability to collapse/expand fields. If you can put the row ID for the individual records into the rows of your pivot beneath what ever aggregated row you are currently displaying, then you should be able to expand into the rows and see the lower levels of the "hierarchy".  By default, all rows in you pivot would be collapsed, but then a user could click the little plus sign next to the row label and expand it to see the underlying records.


    Wednesday, July 30, 2014 3:42 PM
    Answerer
  • Hello Mike,

    thank you very much. My issue is that when I double click on the figure, the rows show as data behind that figure are not being affected by the slicers. It is showing the whole figure without any constraint. Any clue on how to "link" slicers to add feature?

    Regards

    Thursday, July 31, 2014 7:09 AM
  • Sorry but I don't believe that can be done.
    Thursday, July 31, 2014 7:16 AM
    Answerer
  • Hello Mike,

    I have found a workaround to get it. It is not elegant but it works. The trick is to add in the filter section the same fields we have in the slicers (except of course if some of them is in the rows section). Then you can hide those filters and it seems you are doing everything with the slicers. When double-click, you will see just the records in scope.

    Let me know if I am unclear.

    Thank you very much for your response

    • Marked as answer by Elvis Long Thursday, August 7, 2014 8:22 AM
    Thursday, July 31, 2014 10:40 AM
  • I'm not sure I fully understand, but if it's getting you the results you need then it sounds like a good solution to me!
    Thursday, July 31, 2014 2:34 PM
    Answerer