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

Post Reply
Ashleigh W
Posts: 50
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 » Wed Mar 14, 2018 9:11 am

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

User avatar
Alan Kirk
Site Admin
Posts: 5801
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: 9.5.2 64 bit moving to 10.2.2
Excel Version: 2010
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 » Wed Mar 14, 2018 9:25 am

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: 50
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 » Wed Mar 14, 2018 9:31 am

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
Posts: 145
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 » 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

Willi
Posts: 135
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 » 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.

Ashleigh W
Posts: 50
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 » Thu Mar 15, 2018 11:13 am

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: 50
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 » Thu Mar 15, 2018 11:14 am

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
Posts: 135
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 » Thu Mar 15, 2018 12:38 pm

And are both colums pretended to be zero (or empty)? If one of them is not zero/empty the line is shown

Mark RMBC
Posts: 145
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 » Thu Mar 15, 2018 12:58 pm

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: 50
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 » Fri Mar 16, 2018 6:03 am

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: 1584
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 » Fri Mar 16, 2018 8:37 pm

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.

Willi
Posts: 135
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 » Mon Mar 19, 2018 7:42 am

And additionally did you checked that there is no hidden column in the form wich also references to the TM1RPTVIEW?

Post Reply