SET NODATA not working
SET NODATA not working
(OP)
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.
.....
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.
RE: SET NODATA not working
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.
RE: SET NODATA not working
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?
RE: SET NODATA not working
RE: SET NODATA not working
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)
RE: SET NODATA not working
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.
RE: SET NODATA not working
"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?
RE: SET NODATA not working
RE: SET NODATA not working
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:
CODE
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.
RE: SET NODATA not working
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.
RE: SET NODATA not working
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:
CODE