Format zeros as blank in planning analytics

Post Reply
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Format zeros as blank in planning analytics

Post by BigG »

Hi there, does anyone know how to make a zero value blank in PA. The methods in TM1 have caused an issue with PAW (returns an error for zero) and PAX (returns #Value from any excel calcs referencing the blank). Obviously still works in architect when developing.

My fix is forcing a zero, which I can live with, But just checking I am not missing another option. This one works but does not display as blank:

Code: Select all

c:#,##0_);(#,##0);0
I have tried # and "", and _) for the zero values, but 0 is the one that works (probably because out of all those options it is the only one which is an actual number)

BTW - I found leaving the zero area of format blank worked for PAX but not PAW (get error on zero cells)
GG
lotsaram
MVP
Posts: 3651
Joined: Fri Mar 13, 2009 11:14 am
OLAP Product: TableManager1
Version: PA 2.0.x
Excel Version: Office 365
Location: Switzerland

Re: Format zeros as blank in planning analytics

Post by lotsaram »

Leaving blank for zero SHOULD work and if it doesn't it's a bug.
E.g. 0;-0;
That's the universally standard and accepted method to specify blank for zero or null.
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
BigG
Community Contributor
Posts: 211
Joined: Tue Sep 15, 2009 11:13 pm
OLAP Product: IBMPA
Version: PA 2.0 Cloud
Excel Version: 2010

Re: Format zeros as blank in planning analytics

Post by BigG »

thanks lotsaram

Probably a bug but IBM support were not that interested, maybe it will be fixed, but it is an issue at the moment.

Cheers
GG
ellissj3
Posts: 54
Joined: Tue Jun 15, 2010 1:43 pm
OLAP Product: Cognos TM1
Version: 9.0 - 10.2
Excel Version: 2010

Re: Format zeros as blank in planning analytics

Post by ellissj3 »

I raised this with IBM. They mentioned that the number formatting must be on "0.00" for this to work. They mentioned it would be fixed in future releases.

I found this on 10.2.2 FP3.

HTH
Post Reply