Home

Hiding useless sub rows in pivot hierarchy without changing the calculation

%3CLINGO-SUB%20id%3D%22lingo-sub-690828%22%20slang%3D%22en-US%22%3EHiding%20useless%20sub%20rows%20in%20pivot%20hierarchy%20without%20changing%20the%20calculation%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-690828%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20have%20the%20following%20issue%20with%20excel%20pivot%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20I%20have%20a%20table%20with%20projects%2C%20sub%20projects%20and%20sub%20sub%20projects%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20360px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F118062i823A3939BCE8EA32%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20create%20a%20pivot%20table%2C%20it%20looks%20like%20this%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20242px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F118063iD17CB114F4646752%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20i%20try%20to%20hide%20the%20%22(empty)%22%20rows%2C%20pivot%20removes%20the%20whole%20row%2C%20so%20all%20that's%20left%20is%20the%20row%20that%20doesn't%20have%20any%20%22(empty)%22%20values%20in%20%22Main%20Project%22%2C%20%22Sub%20Project%22%20or%20%22Sub%20Sub%20Project%22%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20198px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F118067iE7BBEF3591769488%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20360px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F118064iFC9AB73A811226B4%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20what%20I%20actually%20want%20is%20the%20following%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-left%22%20style%3D%22width%3A%20246px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F118070i31DD432F57B17499%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20don't%20want%20to%20filter%20data%2C%20I%20just%20want%20to%20hide%20hierarchy%20levels%2C%20when%20they%20are%20not%20needed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20using%20SQL%20Server%20data%20and%20VBA%20with%20the%20copyfromrecordset%20method.%20I%20would%20prefer%20a%20simple%20solution%20without%20VBA%20or%20SQL%20Server%20but%20if%20there%20is%20a%20way%20with%20VBA%20or%20SQL%20Server%20to%20fix%20this%20hierarchy%20problem%20in%20pivot%20tables%2C%20it%20would%20be%20perfectly%20fine%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20your%20help%20and%20best%20regards.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-690828%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3Eexcel%20pivot%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPivot%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
sigdeluxe
Occasional Visitor

Hello, I have the following issue with excel pivot:

 

When I have a table with projects, sub projects and sub sub projects:

 

image.png

 

and create a pivot table, it looks like this:

 

image.png

 

When i try to hide the "(empty)" rows, pivot removes the whole row, so all that's left is the row that doesn't have any "(empty)" values in "Main Project", "Sub Project" or "Sub Sub Project":

 

image.png

 

image.png

 

But what I actually want is the following:

 

image.png

 

I don't want to filter data, I just want to hide hierarchy levels, when they are not needed.

 

I am using SQL Server data and VBA with the copyfromrecordset method. I would prefer a simple solution without VBA or SQL Server but if there is a way with VBA or SQL Server to fix this hierarchy problem in pivot tables, it would be perfectly fine too.

 

Thank you very much for your help and best regards.

Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
202 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies