Hai

I am having problem with drill down.

In my first procedure I defined a new field saying 'None' if one field is blank else the same field.

FIELD1/A50 = IF FIELD2 EQ '' THEN 'NONE' ELSE FIELD2;

In the drill down, along with two other fields I am sending above FIELD1 to a different procedure. All option work when clicked on drill down except NONE. If I click NONE it gives 0 records from drill down procedure. But that field has NONE in the data in the drill down procedure as its same define field as above and complete same procedure. Its just the 1st procedure is graph and drill down gives data of the same. I don't know whats going on.
Value is coming as NONE into drill down procedure. Data does nto show up even I manually run the drill down procedure with NONE but field when displayed clearly says NONE..!!
Can someone help on this?

Thanks

This message has been edited. Last edited by: Kerry,

Please post your drilldown procedure. That way, it will be easy for us to suggest you something.

Good thing is that you already eliminated one possible source of the problem. It also does not work OK without the drill down.
Check to see if the WHERE test does not accidentally access the real field iso the Defined one. Otherwise, I agree with Kamesh - post (part of) your code.

Thanks for replies.

WHERE field accessess the defined field.

  
DEFINE FILE CHG
REASON/A50V=
IF RSN EQ ' ' THEN 'NONE' ELSE RSN;
END

TABLE FILE CHG
SUM
	 BUCKETS AS 'CNT'
	 BY BUCKET
	 BY BUCKETS
	 BY RSN
WHERE LOC_NAME EQ '&NAME';
WHERE BUCKET EQ '&BUCKET'
WHERE STATUS NE 'CNCL';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS REQ2 FORMAT FOCUS
ON TABLE SET HTMLCSS ON
END


SET NULL = ON
GRAPH FILE REQ2
SUM
CNT AS ' '
BY REASON
ON GRAPH SET STYLE *
TYPE=DATA, COLUMN=N2, FOCEXEC=app/3(LOCATION_NAME='&NAME' \ BUCKET='&BUCKET' \	BUCKETS='&BUCKETS' \ RSN=RSN), $
ENDSTYLE
END

this is the 1st procedure. drill down procedure is same and has these filters.

WHERE BUCKET EQ '&BUCKET';
WHERE RSN EQ '&RSN';

Thanks

In this fex, the graph does not even have the field REASON, because it is not referenced by the table request. Hold format focus is also not needed, although this may be because what is shown is just a part of the real process. But if format focus is needed, I would not work with variable length fields but use fixed length fields only. Furthermore, it could be that the value NONE is the result of a missing value. The define may translate this to spaces, but since the graph has null set to on, it will treat it as null, not as space, thereby effectively ignoring the define.

RSN in the table request is REASON actually. I tried removing SET NULL etc but no use.
any suggestions please?

I believe NONE might be reserved word in focus and can't be use as data (I am not sure, experts can answer on this).

To check this out, can you change the text from NONE to ZERO and also change that data in your drilldown procedure DEFINE field.

ex: Drill down procedure
RSN1/A6 = IF REASON EQ 'NONE' THEN 'ZERO' ELSE REASON;

TABLE FILE TAB1
PRINT XXX
XX
WHERE RSN1 EQ '&RSN'
END

Kamesh, I did that before..I used various options but same problem keeps coming..

I am sure, you might try this. If not, please try the following in your drill down procedure.

TABLE FILE TAB1
PRINT XXX
BY RSN
BY REASON NOPRINT
WHERE BUCKET EQ '&BUCKET';
WHERE REASON EQ '&RSN';
END

This message has been edited. Last edited by: Kamesh,

Change your second (drilldown) procedure to read:

DEFINE FILE CHG
SHOWVAL/A50 = IF RSN EQ ' ' THEN 'NONE' ELSE RSN;
END

TABLE FILE CHG
SUM   BUCKETS AS 'CNT'
BY    BUCKET
BY    BUCKETS
BY    RSN
BY    SHOWVAL
WHERE LOC_NAME EQ '&NAME';
WHERE BUCKET EQ '&BUCKET'
WHERE STATUS NE 'CNCL';
END

And check to see what the output is. If the showval column has NONE in it, then it must be possible to also do a select on that value.

quote:

Originally posted by GamP:
Change your second (drilldown) procedure to read:

DEFINE FILE CHG
SHOWVAL/A50 = IF RSN EQ ' ' THEN 'NONE' ELSE RSN;
END

TABLE FILE CHG
SUM   BUCKETS AS 'CNT'
BY    BUCKET
BY    BUCKETS
BY    RSN
BY    SHOWVAL
WHERE LOC_NAME EQ '&NAME';
WHERE BUCKET EQ '&BUCKET'
WHERE STATUS NE 'CNCL';
END

And check to see what the output is. If the showval column has NONE in it, then it must be possible to also do a select on that value.

I did this too. Value clearly says none for the SHOWVAL field. This is the reason I dont understand why the filter doesn't work..!!

If you are trying to tell the drilldown to bypass the selection of a particular field, ie take all, then use FOC_NONE.

@ RSquared: it's not selecting everything (what FOC_NONE means) but just a selection on empty fields.
Ok, next step is to add the WHERE SHOWVAL EQ 'NONE' selection. Also experiment with WHERE NE 'NONE'. Or do a WHERE TOTAL. Or do a WHERE RSN EQ ' ' and see what happens. If it concerns a relational table, do tracing to see what sql is generated.
One of these possibilities must give some hint as to what is going on.

GamP,

Then why not put the following in the second (drilldown) procedure to read:
-SET &REAS1=IF &RSN EQ 'NONE' THEN ' ' ELSE &RSN;

drill down procedure is same and has these filters.


WHERE BUCKET EQ '&BUCKET';
WHERE RSN EQ '&REAS1';

Tried this but no positive results..

Can you please post the message that you get?

It says 0 records. but where condition looks good as expected

Enigma006,

Please run your query with -SET &ECHO=ALL; and then print the WHERE clause in the second (drilldown) fex.
is it
WHERE RSN EQ '';
or WHERE RSN EQ ''''; ???