3 Replies Latest reply: May 12, 2012 9:58 AM by Jonathan Dienst RSS

    Help Needed - Show All Values in a Pivot Table

      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

        • Help Needed - Show All Values in a Pivot Table
          Jonathan Dienst

          Hi

           

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

           

          Regards

          Jonathan

            • Re: Help Needed - Show All Values in a Pivot Table

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

                • Re: Help Needed - Show All Values in a Pivot Table
                  Jonathan Dienst

                  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