Fun with pivot tables

[#1]

Hi,  I've got a very specific formatting requirement for my chart.  I have chosen Pivot table as I believe it gets me closest.  Also this is for consumption in both qlikview and nprinting

Full disclosure:  I do have 1 solution for this I'll describe below but it is a bit brute force and requires me to do do all of the work in the database and would double my data since I now have 2 charts that are slightly different in important ways (data wise.  formatting is identical)

So let's discuss requirements. 

1. Header row with a color border below both dimensions and expressions - currently cheating on this one and I'm solving this in a report by adding the boarder there.  In qlik I don't think we have a boarder.  We can get away with a little more flexibility in qlik at the moment.

2.  I need a sub total row below and a group row above - current solution is I add an extra subgroup row called "Fake" in the database results.  and I have a super small dimension that is white on white to get the subtotal.  It works.  My only issue is in my new second chart group 2 has no data so I get the "Group 2" row but no subgroup rows and no subtotal row.  I want to Hide the "Group 2 row" in this scenario.

3. Alternating colors but group rows, subtotal rows and total rows are all white.  I'm so close on this one.  Current solution is to return a color for the row from the database.  But in chart 2 subgroup 3 doesn't exist.  this means 2 yellow rows would be next to each other.  If I use rank on the dimension value to determine the even and odd rows almost everything works great.  Rank 1 and 0 are white.  Other rows are white for even and yellow for odd.    Rank only seems to look at the first character of the string for ranking purposes so I get some odd results.  Second I have had problems with thinks like rank in the dimension coloring. 

Here is what chart 2 would look like

Colors, Names, and numbers are fake for dramatic effect.  Making Group 2 not show up looks like just a aggr so that should be relatively simple.  Getting the alternating color for only the subgroup rows is proving to be a giant pain.  I would rather not return another dataset from the db just to have the formatting for this chart. 

P.S.  just noticed on my second chart I'm missing a bar above the total row.  That is a mistake not a requirement.  the first chart is correct.

[#2]

Please hold off on  solutions here.  I realized I have too much details and too many questions.  I'll break it up into multiple small easy questions and post the final result here for anyone curious.