TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post Reply
Ashleigh W
Posts: 88
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Ashleigh W »

TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Alan Kirk »

Ashleigh W wrote: Wed Mar 14, 2018 9:11 am TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help
The usual cause is that the values aren't really zero but really tiny way-down-in-the-decimal-places values, usually derived from rules. I'd slice the view in question and format Excel to show the maximum number of decimal places possible.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Ashleigh W
Posts: 88
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Ashleigh W »

Thank you Alan..that answer the way tm1 might be behaving but I do not see any decimal values even if i maximize the decimal places to 20...any hack / tips for this?
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Mark RMBC »

HI,

Just as a further check have you tried dividing something by the value? If it returns a div error then at least you know for sure the value is zero.

Also I presume you haven't manually changed the TM1RPTVIEW suppress zero parameter at all? If so did you rebuild the worksheet or just recalculate?

cheers, Mark
Willi
Regular Participant
Posts: 151
Joined: Mon Oct 07, 2013 11:51 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Willi »

Is the column in question the only column wich reference the TM1RPTVIEW-Cell? If not, all columns have to be zero that the line is suppressed.
Ashleigh W
Posts: 88
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Ashleigh W »

Mark RMBC wrote: Wed Mar 14, 2018 12:15 pm HI,

Just as a further check have you tried dividing something by the value? If it returns a div error then at least you know for sure the value is zero.

Also I presume you haven't manually changed the TM1RPTVIEW suppress zero parameter at all? If so did you rebuild the worksheet or just recalculate?

cheers, Mark
It already has suppress enabled. I could be missing something
=TM1RPTVIEW("server:dummyCubename:1", 1, TM1RPTTITLE("server:Version....
Ashleigh W
Posts: 88
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Ashleigh W »

Willi wrote: Thu Mar 15, 2018 9:24 am Is the column in question the only column wich reference the TM1RPTVIEW-Cell? If not, all columns have to be zero that the line is suppressed.
The Active form has only two columns and both are referenced to TM1RPTVIEW
Willi
Regular Participant
Posts: 151
Joined: Mon Oct 07, 2013 11:51 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Willi »

And are both colums pretended to be zero (or empty)? If one of them is not zero/empty the line is shown
Mark RMBC
Community Contributor
Posts: 292
Joined: Tue Sep 06, 2016 7:55 am
OLAP Product: TM1
Version: 10.1.1
Excel Version: Excel 2010

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Mark RMBC »

Hi,

Just want to know if when you open the active form in perspectives and if you do a alt-F9 to rebuild the sheet does it still show the zero values?

cheers, Mark
Ashleigh W
Posts: 88
Joined: Mon Oct 24, 2016 1:21 pm
OLAP Product: TM1
Version: TM1 Perspectives 10
Excel Version: Excel 2016

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Ashleigh W »

Yes I tried clearing the data and rebuilding it still the same. they are zeros. I tried division and it return #DIV/0! error.
I tried using public subset of level 0 for dimension still same result, tried MDX still the same.
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by declanr »

Apologies to ask a basic question but it is actually something Willi has asked twice in this thread and unless I am missing it I can't see an answer...
is the issue that a row containing all zeroes is still showing? Or are you expecting a column containing all zeroes to be hidden?
If its the latter, active forms can only suppress rows not columns.

Again apologies if you are already aware of this but its always worth going with an Occam's razor approach to problem solving.
Declan Rodger
Willi
Regular Participant
Posts: 151
Joined: Mon Oct 07, 2013 11:51 am
OLAP Product: TM1
Version: 9.5.2
Excel Version: 2010

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by Willi »

And additionally did you checked that there is no hidden column in the form wich also references to the TM1RPTVIEW?
UserFriendly
Posts: 5
Joined: Thu Dec 05, 2019 9:50 pm
OLAP Product: TM1 Planning Analytics
Version: 2.06
Excel Version: 2016

Re: TM1 Active Form is returning Zeros even when suppress zero is set to True/1. Please help

Post by UserFriendly »

I had this same issue and although I am not confident this is a secure response, in my investigation I had one row suppress and the other did not. One row was a STET and the other was a Continue cell on rules. I believe it may have something to do with how TM1 reads nulls to zeroes.

I might recommend trying inserting the UNDEFVALs; in your cube rules and see what happens then and compare to see if TM1 is identifying your row cells as nulls or zeroes.
Post Reply