Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Outputting Null instead of zero in Excel Report

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] Outputting Null instead of zero in Excel Report
 Login/Join
 
manglum
Platinum Member
posted
I publish a report into an XLSX file and I noticed that in the cells that should be null, Webfocus has published a zero. This is curious because my base data contains null values, not zeros in these fields.

Is there a setting that will allow the nulls to be published as nulls?

Also, I'm not talking about a field mask (where a zero is published but it appears as a null. I need the actual cell values to be null so that Excel's mathematical functions (such as average) work the way they should and don't include zeros.

Is there something that I need to include in my .fex to make this happen?

Thanks so much for your help!

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
Hank W.
Platinum Member
posted Hide Post
Hum, so it is an integer... setting MISSING=ON... and... was it EMPTYCELLS=ON for excel... usually it is the other way around along with getting rid of that evil annoying period in html output.


Cheers,
H.
WebFOCUS 8.1.05M
Oracle 11g - DB2
RedHat
 
Posts: 115 | Location: Brighton UK | Registered: February 19, 2005Report This Post
manglum
Platinum Member
posted Hide Post
quote:
Originally posted by Hank W.:
Hum, so it is an integer... setting MISSING=ON... and... was it EMPTYCELLS=ON for excel... usually it is the other way around along with getting rid of that evil annoying period in html output.


Thanks Hank! Are these going in the Table File? Something like ON TABLE SET EMPTYCELLS=ON?


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
Hank W.
Platinum Member
posted Hide Post
I think they're the kind of sets you can put on in the beginning, or even in environment.


Cheers,
H.
WebFOCUS 8.1.05M
Oracle 11g - DB2
RedHat
 
Posts: 115 | Location: Brighton UK | Registered: February 19, 2005Report This Post
manglum
Platinum Member
posted Hide Post
quote:
Originally posted by Hank W.:
I think they're the kind of sets you can put on in the beginning, or even in environment.


Got it.
Its not seeming to work, but I'll keep exploring.

I WAS getting those evil little dots as an output. Put when you look at the actual value stored in the cell, it is zero. The '.' is just a format mask.

But I just want the cell to be blank. No value at all so that excel calculations work properly.


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
manglum
Platinum Member
posted Hide Post
quote:
Originally posted by Hank W.:
Hum, so it is an integer... setting MISSING=ON... and... was it EMPTYCELLS=ON for excel... usually it is the other way around along with getting rid of that evil annoying period in html output.


Whenever I search this topic in the forums it seems like webfocus publishes the blanks by default and people are trying to publish a value rather than the null value.

I seem to have the opposite problem for which no solution seems to be discussed...lol


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
manglum
Platinum Member
posted Hide Post
I believe I've discovered the issue.

The NODATA='' is being overridden by the inclusion of a .sty in the ON TABLE STYLE

How to I re-establish the NODATA-'' once this happens?

I cant see anything in the .sty that would be doing this, so I'm unclear how to "undo" this...lol


thanks.


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
manglum
Platinum Member
posted Hide Post
OK, so I explored the .sty and found that the offending code that is overriding my NODATA='' is the following:

 type=data,
backcolor=(rgb(255 255 255) rgb(235 235 240)),
$


Any idea if there is anything I can add to this statement to prevent or reverse the effects and allow the NODATA='' to actually output null values?


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
Doug
Expert
posted Hide Post
Matt,

Check out the difference using "color" instead of "backcolor" in the type=data line.




   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
manglum
Platinum Member
posted Hide Post
quote:
Originally posted by Doug:
Matt,

Check out the difference using "color" instead of "backcolor" in the type=data line.


-*When I use backcolor, the null values are displayed as zeros, but when I just use color the null values come through just fine (as blank cells)
type=data,backcolor=GREY,$
type=data,color=GREY,$


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
manglum
Platinum Member
posted Hide Post
quote:
Originally posted by manglum:
quote:
Originally posted by Doug:
Matt,

Check out the difference using "color" instead of "backcolor" in the type=data line.


-*When I use backcolor, the null values are displayed as zeros, but when I just use color the null values come through just fine (as blank cells)
-*type=data,backcolor=GREY,$
type=data,color=GREY,$


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
Doug
Expert
posted Hide Post
This sounds like a BUG to me...

What IBI sample file has both MISSING and NULL values in it? We can use that for simplicity.
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Hank W.
Platinum Member
posted Hide Post
quote:
-*When I use backcolor, the null values are displayed as zeros, but when I just use color the null values come through just fine (as blank cells)


Ummm... no they're not blank cells, they're white zeroes on a white background Razzer

I think the problem lies in the fact it is an integer. FOCUS don't like null integers. There is a format I think that allows them though...

Ah yes
S Zero suppress If the data value is zero, prints a blank in its place.

So like instead of I4 try I4S

This message has been edited. Last edited by: Hank W.,


Cheers,
H.
WebFOCUS 8.1.05M
Oracle 11g - DB2
RedHat
 
Posts: 115 | Location: Brighton UK | Registered: February 19, 2005Report This Post
manglum
Platinum Member
posted Hide Post
quote:
Originally posted by Hank W.:
quote:
-*When I use backcolor, the null values are displayed as zeros, but when I just use color the null values come through just fine (as blank cells)


Ummm... no they're not blank cells, they're white zeroes on a white background Razzer

I think the problem lies in the fact it is an integer. FOCUS don't like null integers. There is a format I think that allows them though...

Ah yes
S Zero suppress If the data value is zero, prints a blank in its place.

So like instead of I4 try I4S


Hank, the cell value in Excel is actually null, not a white zero on a white background.


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
manglum
Platinum Member
posted Hide Post
quote:

Ah yes
S Zero suppress If the data value is zero, prints a blank in its place.

So like instead of I4 try I4S


I tried the S suppression before. It doesn't work because this glitch in the style sheet overrides it. If you remove this particular line from the style sheet then the cell values are blank as expected. Nothing that you do above the style sheet will fix this error.


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
manglum
Platinum Member
posted Hide Post
quote:
Originally posted by manglum:
quote:

Ah yes
S Zero suppress If the data value is zero, prints a blank in its place.

So like instead of I4 try I4S


The values are not integers. They are decimals formatted D10CBM.


I tried the S suppression before. It doesn't work because this glitch in the style sheet overrides it. If you remove this particular line from the style sheet then the cell values are blank as expected. Nothing that you do above the style sheet will fix this error.


WebFOCUS 8
Windows, All Outputs
 
Posts: 111 | Registered: July 23, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Outputting Null instead of zero in Excel Report

Copyright © 1996-2020 Information Builders