100100 CURRENCY & COIN - VAULT 37,752.50
100101 CCRRENCY & COIN - TELLER #1 11,892.59
100102 CCRRENCY & COIN - TELLER #2 14,850.55
100103 CURRENCY & COIN - TELLER #3 10,247.04
100104 CURRENCY & COIN - TELLER #4 -

In the above pivot table consisting of 3 columns and 5 rows how could I
automatically hide row 5 and all the other $0 value rows in my pivot table?

Thanks!

Hi PTC,

i am more than sure, there are much nicer solutions than mine :-)
however, i use this: in the database i add 1 more - in my terminology -
filtering column/field, in which you create a function of logical check and
if true: filerable if false: not filerable
e.g. IF(C2=0;"Zero";"Non-Zero")
where C2 is the value in the vault, if value is once
or If(SUMIF(A:A;A2;C:C)=0;"Zero";"Non-Zero) where A:A contains the unique ID
field, like teller & vaultnumber, A2 is indiviual ID, like tellerA & vault
#12; and C:C is the values
you fill up the database and
then you refresh the PivotTable (include the additional column) and use
Filterfield as PageField in the Pivot & "filter" only to Non-Zero

Any good?

Best regards,
ANdras
(Hungary)


"PTC" wrote:

100100 CURRENCY & COIN - VAULT 37,752.50
100101 CCRRENCY & COIN - TELLER #1 11,892.59
100102 CCRRENCY & COIN - TELLER #2 14,850.55
100103 CURRENCY & COIN - TELLER #3 10,247.04
100104 CURRENCY & COIN - TELLER #4 -

In the above pivot table consisting of 3 columns and 5 rows how could I
automatically hide row 5 and all the other $0 value rows in my pivot table?

Thanks!

Thanks Andras,

Definitely works better than my solution which didn't exist.


Regards,

Peter

"ANdras" wrote:

Hi PTC,

i am more than sure, there are much nicer solutions than mine :-)
however, i use this: in the database i add 1 more - in my terminology -
filtering column/field, in which you create a function of logical check and
if true: filerable if false: not filerable
e.g. IF(C2=0;"Zero";"Non-Zero")
where C2 is the value in the vault, if value is once
or If(SUMIF(A:A;A2;C:C)=0;"Zero";"Non-Zero) where A:A contains the unique ID
field, like teller & vaultnumber, A2 is indiviual ID, like tellerA & vault
#12; and C:C is the values
you fill up the database and
then you refresh the PivotTable (include the additional column) and use
Filterfield as PageField in the Pivot & "filter" only to Non-Zero

Any good?

Best regards,
ANdras
(Hungary)


"PTC" wrote:

100100 CURRENCY & COIN - VAULT 37,752.50
100101 CCRRENCY & COIN - TELLER #1 11,892.59
100102 CCRRENCY & COIN - TELLER #2 14,850.55
100103 CURRENCY & COIN - TELLER #3 10,247.04
100104 CURRENCY & COIN - TELLER #4 -

In the above pivot table consisting of 3 columns and 5 rows how could I
automatically hide row 5 and all the other $0 value rows in my pivot table?

Thanks!