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

0 Replies