Missing Data showing as Blank in EXCEL. How to Default to "0"

[ssproul]

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

[jim wood]

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.

[ssproul]

I am pretty Sure that the underlying element Type is a numeric value.

Showing Blanks.PNG (132.46 KiB) Viewed 6461 times

the Setting from essbase is here in case someone is familiar with both systems:

[jim wood]

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.

[ssproul]

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

[jim wood]

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.

[declanr]

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:

Code: Select all

#,##0.00;[red](#,##0.00);

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.

[ssproul]

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

[whitej_d]

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