Double-Click PivotTable Item without Displaying Excess Records

[Data Bender]

Thank you for reviewing my post.

When double-clicking a PivotTable value, how does one limit the records displayed?

For example, it makes intuitive sense to see only one record, when double-clicking the value of 1 in the following PivotTable:

However, I get the following nine records instead:

The desired result, of only the one record displayed, would appear as follows:

One more thing.... The file may not have macros, because many end-users view the reports on Macs.

I look forward to your help.

Sincerely,
--Data Bender

 

[Fazza]

In my experience (up to Excel 2003) the records you see are strictly only the one/s related to the PT cell double clicked.

If you're certain that isn't the case, suggest you post some sample data to have others confirm. A bug would be unlikely but maybe there is one?

Otherwise, all I can suggest is you disable the drill down. With macros not available, I'm not sure what else to suggest. Maybe a re-design of the report - perhaps some modification to the pivot table's dataset?

regards

 

[Data Bender]

In my experience (up to Excel 2003) the records you see are strictly only the one/s related to the PT cell double clicked.

If you're certain that isn't the case, suggest you post some sample data to have others confirm. A bug would be unlikely but maybe there is one?

Thank you for your reply, Fazza.

You're right. Those nine records (of several thousand) are strictly the only ones related to the PT cell double clicked. Excel is totaling all nine records related to "Software-Defined Storage Architect." (I.e., 1 + (8 * blank) = 1) So, it's not a bug. I'm just looking for a workaround.

Otherwise, all I can suggest is you disable the drill down. With macros not available, I'm not sure what else to suggest. Maybe a re-design of the report - perhaps some modification to the pivot table's dataset?

Since my boss said that the drill down "is money," disabling it is not an option.

I'll continue to investigate modifying the dataset. However, that has negatively affected other columns thus far.

(BTW, I'm using Excel 2010.)

Thanks, again.

--Data Bender

 

[RoryA]

As far as I know, there would be no way to do what you want without code, or rearranging your data.

 

[Data Bender]

As far as I know, there would be no way to do what you want without code, or rearranging your data.

Thank you for your reply, Rory.

At the very least, I can now show expert opinions to my boss, that his request is beyond Excel's capabilites.

 

[Fazza]

BTW, when I wrote about modifying the dataset, I was thinking of using SQL in between the source data and the pivot table: not changing the actual source data. This may or may not be able to achieve what you want. regards

 

[Data Bender]

Update: We decided to build a macro-based solution, after all. Mac users will just have to use Fusion, or wait for macro-less versions of the reports.

Here is the link to the solution: Excel 2010: Double-Click PivotTable Item without Displaying Excess - Microsoft Community

Thank you, everyone, for your support and feedback.

Kind Regards,
--Data Bender