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
Tabs and Dark Mode
cjc2112 in Discussions on
38 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
11 Replies