PIVOT TABLE HELP - Show Items With No Data - Subcategory

[LogicalFunction]

Please help!

I am having a huge issue with the Show Items With No Data Option. My Subcategories are tied specifically to their respective MainCategories.

MAIN1 has SUB1, SUB2, and SUB3
MAIN2 has SUB4, SUB5, and SUB6
MAIN3 has SUB7, SUB8, and SUB9

Not all SUBs have data in them.

My expected rows are:

MAIN1 SUB1
MAIN1 SUB2
MAIN1 SUB3
MAIN2 SUB4
MAIN2 SUB5
MAIN2 SUB6
MAIN3 SUB7
MAIN3 SUB8
MAIN3 SUB9

When I attempt to Show Items with no data, I get the following:

MAIN1 SUB1
MAIN1 SUB2
MAIN1 SUB3
MAIN1 SUB4
MAIN1 SUB5
MAIN1 SUB6
MAIN1 SUB7
MAIN1 SUB8
MAIN1 SUB9
MAIN2 SUB1
MAIN2 SUB2
MAIN2 SUB3
MAIN2 SUB4
MAIN2 SUB5
MAIN2 SUB6
MAIN2 SUB7
MAIN2 SUB8
MAIN2 SUB9
MAIN3 SUB1
MAIN3 SUB2
MAIN3 SUB3
MAIN3 SUB4
MAIN3 SUB5
MAIN3 SUB6
MAIN3 SUB7
MAIN3 SUB8
MAIN3 SUB9

Is there any way to tie the subcategories to the main category so I get what I expected?

 

[kalikj]

the simple answer is yes, but you would need to structure your original data table so that it has separate "Main Category" Vs "Sub Category" Fields.