Hyperion Essbase Excel Add-in - returning text ("0") for null valu
 

Another Essbase Excel Add-in question.

When I retrieve values into Excel from our Essbase Cube (using Hyperon Excel
Add-In), null values return as text characters. Even if I format the cells,
these values appear as "0". The only way I can change the text to numbers is
through a VBA procedure that changes "0" to value 0 for all cells in the used
range.

Can anything be done (either on user end or Admin side) to get null to
return as number 0 without having to use VBA?

Thanks ...

bill morgan

Hyperion Essbase Excel Add-in - returning text ("0") for null valu
 

The short answer is: no.

Unless things have changed with their latest version, VBA is the only
practical solution.
***********
Regards,
Ron

XL2002, WinXP-Pro


"bill_morgan" wrote:

Another Essbase Excel Add-in question.

When I retrieve values into Excel from our Essbase Cube (using Hyperon Excel
Add-In), null values return as text characters. Even if I format the cells,
these values appear as "0". The only way I can change the text to numbers is
through a VBA procedure that changes "0" to value 0 for all cells in the used
range.

Can anything be done (either on user end or Admin side) to get null to
return as number 0 without having to use VBA?

Thanks ...

bill morgan

Hyperion Essbase Excel Add-in - returning text ("0") for null
 

Well ... at least I picked the right solution. Thanks for the confirmation.

Seems unusual that there isn't some function on the cube side like T-sql
convert() or cast() that could be applied to all data retrievals.

"Ron Coderre" wrote:

The short answer is: no.

Unless things have changed with their latest version, VBA is the only
practical solution.
***********
Regards,
Ron

XL2002, WinXP-Pro


"bill_morgan" wrote:

Another Essbase Excel Add-in question.

When I retrieve values into Excel from our Essbase Cube (using Hyperon Excel
Add-In), null values return as text characters. Even if I format the cells,
these values appear as "0". The only way I can change the text to numbers is
through a VBA procedure that changes "0" to value 0 for all cells in the used
range.

Can anything be done (either on user end or Admin side) to get null to
return as number 0 without having to use VBA?

Thanks ...

bill morgan

Hyperion Essbase Excel Add-in - returning text ("0") for null
 

In Essbase | Options | Display, use '=0' (without quotes) to replace
#MISSING. When you retrieve, 0 will be numeric.

"bill_morgan" wrote:

Well ... at least I picked the right solution. Thanks for the confirmation.

Seems unusual that there isn't some function on the cube side like T-sql
convert() or cast() that could be applied to all data retrievals.

"Ron Coderre" wrote:

The short answer is: no.

Unless things have changed with their latest version, VBA is the only
practical solution.
***********
Regards,
Ron

XL2002, WinXP-Pro


"bill_morgan" wrote:

Another Essbase Excel Add-in question.

When I retrieve values into Excel from our Essbase Cube (using Hyperon Excel
Add-In), null values return as text characters. Even if I format the cells,
these values appear as "0". The only way I can change the text to numbers is
through a VBA procedure that changes "0" to value 0 for all cells in the used
range.

Can anything be done (either on user end or Admin side) to get null to
return as number 0 without having to use VBA?

Thanks ...

bill morgan

Hyperion Essbase Excel Add-in - returning text ("0") for null
 

Which versions of Essbase/Excel does that work for?

After changing the #Missing Label to: =0
I get =0 as text in the cells, instead of the expected numeric zero.

That occurs regardless of the numeric format of the impacted cells.

***********
Regards,
Ron

XL2002, WinXP


"themediumtee" wrote:

In Essbase | Options | Display, use '=0' (without quotes) to replace
#MISSING. When you retrieve, 0 will be numeric.

"bill_morgan" wrote:

Well ... at least I picked the right solution. Thanks for the confirmation.

Seems unusual that there isn't some function on the cube side like T-sql
convert() or cast() that could be applied to all data retrievals.

"Ron Coderre" wrote:

The short answer is: no.

Unless things have changed with their latest version, VBA is the only
practical solution.
***********
Regards,
Ron

XL2002, WinXP-Pro


"bill_morgan" wrote:

Another Essbase Excel Add-in question.

When I retrieve values into Excel from our Essbase Cube (using Hyperon Excel
Add-In), null values return as text characters. Even if I format the cells,
these values appear as "0". The only way I can change the text to numbers is
through a VBA procedure that changes "0" to value 0 for all cells in the used
range.

Can anything be done (either on user end or Admin side) to get null to
return as number 0 without having to use VBA?

Thanks ...

bill morgan

Hyperion Essbase Excel Add-in - returning text ("0") for null
 

I'm currently using the 7.1 version of the Excel Add-In, and it works for us.

What version are you using, that it is not working?

"Ron Coderre" wrote:

Which versions of Essbase/Excel does that work for?

After changing the #Missing Label to: =0
I get =0 as text in the cells, instead of the expected numeric zero.

That occurs regardless of the numeric format of the impacted cells.

***********
Regards,
Ron

XL2002, WinXP


"themediumtee" wrote:

In Essbase | Options | Display, use '=0' (without quotes) to replace
#MISSING. When you retrieve, 0 will be numeric.

"bill_morgan" wrote:

Well ... at least I picked the right solution. Thanks for the confirmation.

Seems unusual that there isn't some function on the cube side like T-sql
convert() or cast() that could be applied to all data retrievals.

"Ron Coderre" wrote:

The short answer is: no.

Unless things have changed with their latest version, VBA is the only
practical solution.
***********
Regards,
Ron

XL2002, WinXP-Pro


"bill_morgan" wrote:

Another Essbase Excel Add-in question.

When I retrieve values into Excel from our Essbase Cube (using Hyperon Excel
Add-In), null values return as text characters. Even if I format the cells,
these values appear as "0". The only way I can change the text to numbers is
through a VBA procedure that changes "0" to value 0 for all cells in the used
range.

Can anything be done (either on user end or Admin side) to get null to
return as number 0 without having to use VBA?

Thanks ...

bill morgan

Try using "(0)" instead of "0". Excel and the Add-In should interpret it as a true zero.

HI all,

Thank you.. my version is 11.1.2 and it works!!!!

sparkeye