Pivot table - Hide calc items when shows 0

Hi Folks,

I am facing same problem and i have been searching the solutions for long time. But yet to got a clue on this calculated Item issues.

Do some one could help us in this regard. would be great help for us...

Cheers,
Chantty...

I am also having this problem - does anyone have any suggestions?

Ta,

lulunich

This link should direct you on how to do what you seek.

I may be having a dense morning, but to me this doesn't actually tell me how to hide zeroes, just how to show them or how to hide blanks. I am misreading?

Ta,

lulu

Hi, Lulu

I wonder if this approach is suitable? Instead of using the pivot table calculated field, create the field via SQL. I have posted a number of examples in the past [so you could try searching for my old posts on the subject], though it is likely just as easy to post some specific information of your example.

The basic idea is to create the pivot table and via SQL do the calculation. Such as below. Anyway, if you want specific help, please post some specific information on the fields. If creating manually, start from a file separate from the data and at the first step of the pivot table wizard take the external data option.

regards, Fazza

Hi Fazza,

Thanks for your reply - I have spotted some of your other posts and will attempt to do follow them - never worked in sql before so was trying to avoid it but it looks like I will have to brave and try it.

Cheers,

Lulu

Ok, Lulu. I just googled and found many old posts. Some are below. You might need to read a few. Or some others, via Google,
"site:mrexcel.com Fazza SQL calculated field pivot table"

http://www.mrexcel.com/forum/showthread.php?p=1622672
<TABLE style="WIDTH: 48pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=64 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17></TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16457" width=450><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 338pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=450 height=17>http://www.mrexcel.com/forum/showthread.php?p=1695075</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16457" width=450><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 338pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=450 height=17>http://www.mrexcel.com/forum/showthread.php?p=1749494</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16457" width=450><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 338pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=450 height=17>http://www.mrexcel.com/forum/showthread.php?p=1904058</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16457" width=450><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 338pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=450 height=17>http://www.mrexcel.com/forum/showthread.php?p=1906533</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16457" width=450><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 338pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=450 height=17>http://www.mrexcel.com/forum/showthread.php?p=2001459</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16457" width=450><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 338pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=450 height=17>http://www.mrexcel.com/forum/showthread.php?p=2015614</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16457" width=450><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 338pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=450 height=17>http://www.mrexcel.com/forum/showthread.php?p=2038144</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16457" width=450><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 338pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=450 height=17>http://www.mrexcel.com/forum/showthread.php?p=2120437</TD></TR></TBODY></TABLE>

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=450 border=0 x:str><COLGROUP><COL style="WIDTH: 338pt; mso-width-source: userset; mso-width-alt: 16457" width=450><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 338pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=450 height=17>http://www.mrexcel.com/forum/showthread.php?p=2221359</TD></TR></TBODY></TABLE>

In fact SQL can be simple. It just takes a short time to learn the basics. Please post again as required. regards, F