Hi, sorry, forgot to reduce one of the tables ... hopefully this one's a bit better ...
QV_Pivot_Query.qvw 3.5 MB
Turning on suppress zeroes will hide the lines as you require, but as you have seen, it also suppresses the age buckets that are empty.
The reason that checking the 'Show All Values' option for the age buckets is that those buckets are empty, there is no data linked to them at all.
One way to allow them to display would be to concatenate one null (0 value) transaction in GLTran to each combination of Property Ref, Lease Ref, Sub ledger description and age bucket. This would link those missing buckets and they should then display.
Something like this should do the trick:
Concatenate(GLTran) LOAD DISTINCT DebtorID, //proxy for Property Ref and Lease Ref sub_ledger_code, //proxy for sub_ledger_description ChargeID, //proxy for GLChargeDaysOldGroup 0 As [GL Tran Amount], 0 As [GL Tran VAT], 'FAKE' As tr_type Resideny GLTran;
If you use transaction counts, you may need to adjust them to not count these fake transactions. That is why I gave them a tr_type that indicates the fake transactions.