Hello,
My team is switching from Hyperion ESSbase, and they are familiar with an ESSBase feature where the a cell with missing data (Null Data) can default to a replacement string - typically the value "0".
So in the excel PAX tool, on 10.2, unless they wrap the TM1 Function call in an IF or similar structure, they don't know how to automatically place 0's in the Excel Cells with the NULL Data.
Does anyone have a solution that would work directly with TM1 or with the excel PAX (formally CAFE) addin?
Thanks,
Steve
Missing Data showing as Blank in EXCEL. How to Default to "0"
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Missing Data showing as Blank in EXCEL. How to Default to "0"
In Excel you see a zero if the element type is numeric. It sounds like your trying to get string values to default to 0. Saying that I've really spent much time with PAX so it may behave differently,
Jim.
Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- Posts: 5
- Joined: Fri Apr 28, 2017 2:32 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Missing Data showing as Blank in EXCEL. How to Default to "0"
I am pretty Sure that the underlying element Type is a numeric value.
the Setting from essbase is here in case someone is familiar with both systems:
the Setting from essbase is here in case someone is familiar with both systems:
- Attachments
-
- EssBase.PNG (89.67 KiB) Viewed 6461 times
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Missing Data showing as Blank in EXCEL. How to Default to "0"
Looks like Excel formatting to me. If you notice the columns that display blanks are formatted differently than the others. Show me a a straight slice and I'll think otherwise.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- Posts: 5
- Joined: Fri Apr 28, 2017 2:32 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Missing Data showing as Blank in EXCEL. How to Default to "0"
I'll check back with the users. as to the example they sent me.
However, the original question is still there - Is the standard to place a <blank> in the cell if the underlying data is null? You indicated from your earlier post that you felt NULL values were populated with 0, if the underlying data was numeric.
Thanks,
Steve
However, the original question is still there - Is the standard to place a <blank> in the cell if the underlying data is null? You indicated from your earlier post that you felt NULL values were populated with 0, if the underlying data was numeric.
Thanks,
Steve
- jim wood
- Site Admin
- Posts: 3951
- Joined: Wed May 14, 2008 1:51 pm
- OLAP Product: TM1
- Version: PA 2.0.7
- Excel Version: Office 365
- Location: 37 East 18th Street New York
- Contact:
Re: Missing Data showing as Blank in EXCEL. How to Default to "0"
TM1 numeric elements always display as zero when not populated in the cube viewer and will display as zero when sliced in to Excel. What you do within Excel is something different. What I mentioned was String Elements. They display as blank when not populated with any data.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
-
- 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: Missing Data showing as Blank in EXCEL. How to Default to "0"
By default pax uses the formatting set within TM1; this is then just excel so it can be changed in excel.
To set the default in TM1 just use the "format" in the measures dimensions element attributes.
Again you enter this as you would excel formatting so choose custom and then:
This would format zeroes as blank due to the third part (aka after the second semicolon.)
But this will just be the default, users can then still change it to whatever they want in excel.
To set the default in TM1 just use the "format" in the measures dimensions element attributes.
Again you enter this as you would excel formatting so choose custom and then:
Code: Select all
#,##0.00;[red](#,##0.00);
But this will just be the default, users can then still change it to whatever they want in excel.
Declan Rodger
-
- Posts: 5
- Joined: Fri Apr 28, 2017 2:32 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2013
Re: Missing Data showing as Blank in EXCEL. How to Default to "0"
Thanks! This is exactly what I was looking for. Really appreciate the help.
I will forward all these comments to my TM1 Developer and TM1 support team to investigate and report back with more details on the outcome.
Steve
I will forward all these comments to my TM1 Developer and TM1 support team to investigate and report back with more details on the outcome.
Steve
-
- Community Contributor
- Posts: 103
- Joined: Mon Sep 05, 2011 11:04 pm
- OLAP Product: TM1
- Version: 10.2
- Excel Version: 2010
Re: Missing Data showing as Blank in EXCEL. How to Default to "0"
Another thing that was the case in 10.2, not sure since, is that if you have "undefvals" in your rule it tends to return blank vs zero in excel. For cells which have had a number, but it was then deleted, it will return 0. It then plays all kinds of havoc with zero suppression. Up to 10.2, performance modeler would automatically add undefvals to all cube rules. I don't think it does anymore, but I'm not completely sure. Removing the undefvals would make blanks default to zero in every case as per original tm1 behaviour in versions of old.
https://www.ibm.com/support/knowledgece ... fvals.html
https://www.ibm.com/support/knowledgece ... fvals.html