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....
 

dlorenc

Board Regular
Joined
Aug 17, 2004
Messages
140
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....
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,192
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
 

dlorenc

Board Regular
Joined
Aug 17, 2004
Messages
140
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)...
 

Forum statistics

Threads
1,082,143
Messages
5,363,374
Members
400,731
Latest member
Jackserver

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top