Pivot table, unhide grouped values

dlorenc

Board Regular
Joined
Aug 17, 2004
Messages
140
is there a way to force a pivot table to display the values in grouped fields?..

for example, I have the management lead as my first pivot column..row one has that person's name...row 2 (which also belongs to the same mgmt lead) is blank...I would like the table to display the person's name in each row....

I am importing this excel into the gnatt chart'er in visio...so the values are 'blank' in the gantt chart for each 'grouped' value....
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
mm..not quite...in your collapsed view you want the values shown at some level...in my case in the EXPANDED view, I want the values shown...

your example actually does show what I need...your item 8173 for the second item (cable.a) is blank...I am asking if there is a way to SHOW 8173 in column a for cable.a.....

my current solution, I pull data in a sql view...use a data connector to the sql database to pull the data into an excel pivot table...than use the gnatt chart importer in visio to pull the excel data into the gnatt chart...I use a HP plotter to produce the gnatt chart for a division's projects .... sooo...

I found there actually IS a pivot capability in sql 2008...I am using that to produce the pivot in sql..then import that sql view into excel (without using the excel pivot)...then sucking that data into visio for the gnatt chart...

seems to work, but excel pivot would provide a more user friendly GUI for my PMO analysts to slice and dice before doing the gnatt chart for the different divisions....
 
Upvote 0
It sounds like, instead of an Excel pivot table, you might want a query table. Either via meny ALT-D-D-N or via VBA. With VBA you have the further option of a recordset. The SQL to do a cross tab is like below. I've assumed SUM function but it could be different.

Another thought, presumably this could be done straight from VISIO without using Excel. I don't know Visio, but the way it is done in VBA it can be done from Word or Outlook or whatever. As it is just a query, I guess that can be done from Visio.

HTH, Fazza

Code:
TRANSFORM Sum(whatever)
SELECT row field/s
FROM data
WHERE where_criteria
GROUP BY row field/s
HAVING having_criteria
PIVOT column_field
And optionally the last line could be PIVOT column_field IN (list of specific fields)

PS Without a pivot table might use less memory too, though that may not be relevant. F
 
Upvote 0
excellent information Fazza...thank you...I will look into the sql solution..

as the visio solution I need uses a visio import utility for schedules, it only supports a few sources (txt, mpp, xlsx)...so no, I cannot do a direct import into visio from sql (for example)...
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,185
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top