Help Needed - Show All Values in a Pivot Table

[#1]

Hi,

It sounds so simple ... all I need to do is to show all values for one of my dimensions in a pivot table.  In the attached I've got two fields across the top (subledger and aged debt) with a set analysis function to sum the debt in the main data part.

I've got an odd mixture here.  I want only lines of greater than zero to appear (which I thought would be the case anyway, but apparently not) but I want all columns for aged debt to appear whether they are zero or not.  For example lease ref 404004 has no debt, so why does the lines appear?  In the "Other" subledger section I've only for <0, 61-90 and 90+ aged debt columns showing, but I'd like all of them (0-7, 8-14 etc.).

I've been trying different things until my eyes have gone square so I thought I'd throw it open to the group ... can anyone throw some light on this one for me?

Thanks,

Emma

[jonathandienst]

Hi

Your file is quite large. Could you post a reduced size sample?

Regards

Jonathan

[#3]

Hi, sorry, forgot to reduce one of the tables ... hopefully this one's a bit better ...

[jonathandienst]

Hi

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.

Regards

Jonathan