Pivot Table "Show Detail" work around

[dmcgimpsey]

hi guys ....

I know someone has done this before so here goes:

I have a pivot table, and it consists of X by Y dimensions (lets say) ...

what I want to do is that when the user drills into the data - instead of showing him the raw data in a new worksheet, I intercept this data and build my own query out of it...

here is the reason:

I have a pivot table that is made up of summary data; there is no details on the customer id ... but - if I know what the cell (raw data) that was selected, I can dynamically produce a query from the returned data and then I can derive a query that will get the customer information and then create my own results window (well, a lot more than that)

so again - here is the question:

Can I pass the raw data from the pivot table drill down data to my VBA macro ?

Thanks a lot

Don

 

[Tom Urtis]

One of these 3 alternatives I have posted to similarly posed questions might work for you depending on what you want by this you wrote:
"and then create my own results window (well, a lot more than that)"

In each alternative, no new sheet is created when a pivot table's data range is double-clicked for the drill-down data.

(1)
Drill down to show data set on same sheet (no new sheet created) below pivot table (note cool feature to double-click a data set to delete it from the stack).
http://www.mrexcel.com/forum/showthread.php?t=289427

(2)
Each drilled down dataset is stacked in the same dedicated sheet in the same workbook (not a new sheet created each time), separate from the sheet holding the source data:
http://www.mrexcel.com/forum/showthread.php?t=190025

(3)
Each drilled down dataset is placed into its own workbook, no new sheet created:
http://www.mrexcel.com/forum/showthread.php?t=282507


If you want something other than what these accomplish, you'd need to post back with more detail about what you are doing.

 

[dmcgimpsey]

Hi Tom:

hmmm, guess what? I was reading into many of your solutions already - I was going to post directly to you - I found the reply you wrote regarding the results into a new worksheet quite interesting.

Here is precicely what I am trying to do:

assume the pivot table drill down returns one row of detail information - lets say for arguments sake, Age and Gender then the individual metrics. Okay - we have age and gender for the target cell (these are the classes or dimensions) ... now, I want to dynamically create a query that goes against the master database to pull all IDs (or any other field) that has this criteria.

Lets say the person clicked on the intersetion of Age = 40 and Gender = "M" ... well, if they click on this cell, I want to generate the query

Select employee ID where age = 40 and Gender = "M"

from this point, I would do a lot of different things with this result, for instance, I could populate a combo box with those employee IDs and then allow the user to action these items. I don't have a problem with what to do with the results, I just need to know how to intercept the double click and then take some sort of action, based on the criteria that made up this selection ... I even know the dimensions that make up the selection, because they are the first columns in the results sheet.

in reality, my query will be much more complicated because there are in fact 80 million rows in the table (transaction file) and I am looking at 4 dimensions for now and this will surely grow.

As you can see, I am trying to build a sort of cube without the supporting data.

If you require more details, let me know - once I get this to work, I will have a lot more to do because this is the tip of the iceburg.

Thanks so much for your tips, I look forward to your enlightenment

REgards

Don

 

[Tom Urtis]

It's a better idea to do what you did regarding posting your question to the general board audience rather than to just me or any individual as you were considering, though I appreciate the thought. You never know what the unavailability is of your single intended recipient, and also, various people in a public forum will almost always have some useful input beyond just one person's skillset.

At the same time, in my opinion just on the outside looking in, your subject matter here appears more involved than is seemingly reasonable to get solved using message board posts:

- Expanding dimensions for what sounds like a "cubeless" cube
- 80 million records
- Complicated (your term) queries
- "tip of the iceburg (sic)"

You have a lot going on and this sounds like an interesting but complex sort of project that you should consult with a developer for, unless you have specific narrow tasks such as populating comboboxes with employee IDs as you were saying, but at this level it sounds as if you already know how to do that.

I'd suggest you hire someone who can do the job for you, or at least someone who you can show first-hand this monster you are building, so they can provide you with a more knowledgeable opinion for whatever minefields lay ahead in your design.

 

[charlie1105]

Just a thought - can you not change the double click event to call another routine that does what you need?

If the layout of your pivot table is the same then you can use the row and column reference of the double click target to pull the headers from the top and left of the table

Alternatively, create the showdetail sheet, get the information you need from it, then delete it