SET NODATA not working

[steve817]

How can I get the SET NODATA command to work for a "grouped by" report? Here's a snippet of my code:

.....
SET NODATA = 'N/A'
.....
TABLE FILE QSACUS
SUM
DESCRIPTION
CNT.counter AS 'COUNT'
PCT.CNT.CTRLNBR AS '%TOTAL'
COST/D12.2CM
BY TOTAL HIGHEST counter NOPRINT
BY &GROUPBY
....

To clarify, the DESCRIPTION field will display "N/A" if the table value is null, but the &GROUPBY isn't doing the same thing. It will just be a blank. The &GROUPBY field is being used as the link to another report. In HTML, the blank is fine - the link still works. But the PDF DrillThrough version of the report doesn't display anything for the link so users cannot drillthrough on the null values. Please excuse my ignorance as I am new to WebFOCUS and just about ready to throw in the towel.

 

[bigdogg019]

Steve,

I understand your issue and have encountered this issue myself. I have yet been able to create a work around for it, but I think I have an understanding.

I am not fully positive, and I am sure someone will correct me if I am wrong, but from what I have gathered, the SET NODATA command does not work on variables. I have created a report that if I used an &variable, the report displayed a blank, but if I set that &variable to a DEFINE variable, then it displayed my SET NODATA correctly.

 

[focwizard]

SET NODATA should work on ANY fields, whether real, DEFINEd, or provided by a Dialogue Manager Variable (by the time the reporting server sees it, the variable has had it's value replaced, so it's no longer a variable). Anywhere the NODATA characters would appear (the default is a dot '.'), this replaces the dot.

The first quesion to ask is "is the &GROUPBY REALLY missing?". For example, if this is an SQL table, the value would have to be declared as 'missible' in the original table, which would provide, in the MFD, a MISSING=ON declaration, allowing the field to get the NODATA characters. If not missable, then a MISSING value becomes a blank.

In your case, if you remove the SET NODATA line, what value shows? Is it a '.', or a blank? In the dtaa description, is that field described with MISSING=ON?

 

[steve817]

The &GROUPBY variable is returning a space. The ERP data being accessed is stored in a Progress database. Is it possible that the fields are null when the records are created if no value is entered upon creation, but then through maintenance of the record, the null value is lost and replaced with a non-value (neither a space nor null)?

 

[focwizard]

The fact that th &GROUPBY field displays as a SPACE, rather than the NODATA character can mean one of two things:

1. The value in the database is ACTUALLY a space (perhaps declared as not missable)

2. In the MFD, the MISSING attribute for that field is not specified, or specified as OFF

This is assuming we're dealing with a SINGLE table. If JOINs are in effect, then is it a one-to-one JOIN, or a one-to-many? In a one-to-one (UNIQUE) JOIN, if a record is NOT found, we make one up, with default values (again, this pre-supposes that MISSING=OFF in the MFD for that field)

 

[steve817]

We've queryied the database outside of WebFOCUS and have confirmed that the values in the tables are not null and not a space. The MISSING attributes for all fields are set to ON (the default). We are using multiple one-to-many joins (left-outer joins in most cases).

When the variable &GROUPBY is set to display the field value from the host table (ie: "CUS"), and if that value is empty - WebFOCUS is returning a space. If we use a join to look up the code's description in another table (ie: "Customer"), if the value was empty in the host table, the NODATA works because it can't find the description record (there is no " " value in the description table). I hope I explained myself well enough.

 

[focwizard]

You say:

"if that value is empty - WebFOCUS is returning a space"

What do you mean by 'empty'? If the value is "not null and not a space", what is it?

 

[steve817]

It seems to be returning a hexidecimal zero, but NODATA doesn't see this a a true null value. As I mentioned in the previous posting on Feb 27th, if a new record is created and no value is entered in a text field, NODATA sees it as a null. However, once the record is updated, NODATA isn't seeing that field as a null anymore (eventhough the field still has no value entered).

 

[focwizard]

But a hexidecimal ZERO isn't NULL, it's a non-printing character, which is why it displays as blank.

for starters, let's ensure that it is hexadecimal zero. Since the field is alpha, you can use the subroutine UFMT, which will display the value in HEX. If it TRULY is hex 0, then you can issue a DEFINE like the following, which makes a alpha field with hex 0 into a MISSING value:

DEFINE FILE xxx
field MISSING ON = IF UFMT(field,inlength,'Alength') eq '00000000...' THEN MISSING ELSE field;
END

Which says, if the value of the field is hex zeros, set it to MISSING, OTW leave it as is.

 

[steve817]

This link is to the ASCII chart. It says that a hexidecimal 0 is null:

http://www.ascii.ws/images/ascii-chart.gif

(unless, in database terms, it's something different). We did a SQL query "length(fieldname)" and it returned a zero length.

Currently, we are doing a work-around similar to what you provided. This really isn't an acceptable solution because it's a hardcoded-work-around that has to be done on each field that we're grouping by, plus it'll have to be done on each report that's generated. What we were hoping for is some kind of command that can be run before the NODATA statement that will treat all "empty" fields the same as true null values. I hope that makes sense.

 

[focwizard]

You said it returns a HEX 0, but that the length of the field is also zero. A HEX zero doesn't have a zero length; it has a length of 1; one byte to hold the HEX 0.

Earlier on, you said:

"The MISSING attributes for all fields are set to ON (the default)."

The default for fields in a MFD is MISSING=OFF. Can you post the MFD entry for the field in question? To get a MISSING value, it must EXPLICITLY say MISSING=ON for that field in the MFD. It should look like:

 FIELDNAME=xxx, ALIAS=yyy, USAGE=zzz, ACTUAL=www, [red]MISSING=ON[/red],$