Excel Drill Down not working correctly - double click produces all data view

[smokeysnow]

Hello...I'm working in Excel 2007 and I'm using a data connection to SQL 2008. I've created a few pivot tables using the same datasource/connection string. My users want to be able to drill down in the pivot table to see the detailed data behind. All but 1 of my pivot tables seem to be working correctly. Basically, I'm using fields directly from my datasource - no calculated/formula field. As shown below example, I have 31 records for Bus1. When I double click, I get 1160 records which is the total count of records I have for CRA, I only want to see the 31 records that apply to Total Open/Pending which have an indicator of 1. I noticed that if I move the field (Total Open/Pending) to report filter, I get the 31 records ONLY, but this impacts the other field counts since some records may not apply to Total Open/Pending.

Thank you in advance for your help


[TABLE="width: 1182"]
<tbody>[TR]
[TD="class: xl67, width: 319"]Line of Business
[/TD]
[TD="class: xl68, width: 93"]#Total Open/Pending
[/TD]
[TD="class: xl69, width: 80"]#Ack Due Today
[/TD]
[TD="class: xl70, width: 79"]#Ack Due Tomorrow
[/TD]
[TD="class: xl71, width: 65"]#Ack Past Due
[/TD]
[TD="class: xl69, width: 67"]#Due Today
[/TD]
[TD="class: xl70, width: 71"]#Due Tomorrow
[/TD]
[TD="class: xl71, width: 150"]#Due Date PastDue
[/TD]
[TD="class: xl69, width: 77"]# of Escalated Requests
[/TD]
[TD="class: xl71, width: 174"]# of Escalated Requests not acknowledged >1 day
[/TD]
[TD="class: xl72, width: 118"]# of Requests for Add'l Information
[/TD]
[TD="class: xl71, width: 101"]# of Requests for Add'l Information > 1 bus day
[/TD]
[TD="class: xl72, width: 107"]Total Escalated to PPerformance MTD
[/TD]
[TD="class: xl73, width: 73, bgcolor: #D8D8D8"]Total Resolved MTD
[/TD]
[/TR]
[TR]
[TD="class: xl66, bgcolor: transparent"]Bus1
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]31
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]7
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]3
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]0
[/TD]
[TD="class: xl66, bgcolor: transparent, align: right"]756
[/TD]
[/TR]
</tbody>[/TABLE]

 

[mrshteam]

Smokey, did you ever resolve this issue? I am encountering the same thing using Excel 2010 and basic pivot table design - no slicers. I work at an insurance company and am counting certain types of claims. When I click on the number in the pivot table in a particular cell to see that list of claims, I am getting back all the claims, not those filtered by that particular type.

 

[bdunning]

I am having the same problem in Excel 2013 and also work with claims. When I click a sub set of my data or have it filtered, the drill down function is not working properly as it is pulling the entire data set back on a new tab. Any assistance on this from some of the pros would be greatly appreciated!

 

[bdunning]

Looks like I found the solution...if you are using slicers, you have to make sure that you also have those same filters in your pivot table for them to work. After I added the filters back to my pivot table and hit those rows, the drill down function worked Here is the link that I was able to reference for the fix:

Be Careful Drilling into PivotTables with Slicers – Bacon Bits