I am making a Report with a WHERE condition on Start Month/Year and End Month Year, also I have put the AUTOMATIC DRILLDOWN option ON. It works fine without the WHERE condition but when I apply WHERE condition DRILLDOWN doesn't work.
PLS HELP ME AS I AM NEW TO WebFOCUS!!!!

Following is the piece of code:

TABLE FILE TEST
SUM
CNT.CASEREFNUMBER AS 'CNT,CASEREFNUMBER'
BY
STATE AS 'State'
BY
REGION AS 'Region'
BY
PARISH AS 'Parish'
ACROSS
MONTH AS 'Month'

ON STATE SUBTOTAL
CASEREFNUMBER AS '*State Total: '

ON REGION SUBTOTAL AS '*Region Total:'
WHERE HDATE(INTAKERECEIVEDDT, 'MYY') GE HDATE(HINPUT(8, '&startYear.(2003,2004,2005).Start Year.' | '&startMonth.(01,02,03,04,05,06,07,08,09,10,11,12).Start Month.' | '01' , 8, 'HYYMDS'),'MYY')
AND
HDATE(INTAKERECEIVEDDT, 'MYY') LE HDATE(HINPUT(8, '&endYear.(2003,2004,2005).End Year.' | '&endMonth.(01,02,03,04,05,06,07,08,09,10,11,12).End Month.' | '01' , 8, 'HYYMDS'), 'MYY');

Hi Rajiv,<br /><br />As I heard from one of the cases, filters and OLAP do not work together. <br /><br />Have you tried what Goldy suggested at the other thread that you have? https://forums.informationbuilders.com/eve/forums/a/...141078331#5141078331 <br />Is the suggestion helpful to you?<br /><br />You may also want to take a look at the following page.<br /><br />Display OLAP Dimension/Selection Values in DrillDown heading <br /> http://techsupport.informationbuilders.com/sps/91111037.html <br /><br />Hope this helps. <br /><br />Cheers,<br /><br />Kerry

This message has been edited. Last edited by: <Mabel>,

As a guess I would make all of the &'s into
double &&'s. eg &&STARTYEAR, &&STARTMONTH, etc.

This preserves their values thru all of the reports which are needed in subsequent drill-downs.

Thanks a lot for both the replies, the && thing is not helping me, it gives an error (may be there is a version problem I am using 5.2.3)

Solution by Kerry should help, but I am using WHERE clause right now and not the IF Clause. If I try to use the same condition in IF clause it gives me error that the field is not recognised, I think the functions for date which I am using (viz. HDATE, HINPUT etc.) doesn't work with IF or the problem is due to amper fields. I am using the following condition in WHERE Clause:

HDATE(INTAKE_RECEIVED_DATE, 'MYY') GE HDATE(HINPUT(8, '&startYear.(2003,2004,2005).Start Year.' | '&startMonth.(01,02,03,04,05,06,07,08,09,10,11,12).Start Month.' | '01' , 8, 'HYYMDS'),'MYY')
AND
HDATE(INTAKE_RECEIVED_DATE, 'MYY') LE HDATE(HINPUT(8, '&endYear.(2003,2004,2005).End Year.' | '&endMonth.(01,02,03,04,05,06,07,08,09,10,11,12).End Month.' | '29' , 8, 'HYYMDS'), 'MYY')


How should I use the same in IF Clause. Or otherwise can I use the same .js file with WHERE clause also (which I think is not possible).

To use IF you have to make
the screening condition a defined variable which your test compares to a constant:

DEFINE FILE xxx
Take/i1=
HDATE(INTAKE_RECEIVED_DATE, 'MYY') 
GE HDATE(HINPUT(8, '&startYear.(2003,2004,2005).Start 
Year.' | 
'&startMonth.(01,02,03,04,05,06,07,
08,09,10,11,12).Start Month.' | '01' , 8, 'HYYMDS'),'MYY')
ANDHDATE(INTAKE_RECEIVED_DATE, 'MYY') 
LE HDATE(HINPUT(8, '&endYear.(2003,2004,2005).End Year.' | '&endMonth.(01,02,03,04,05,06,07,
08,09,10,11,12).End Month.' | '29' , 8, 'HYYMDS'), 'MYY')
END TABLE FILE xxx
IF Take IS TRUE<br />END

This message has been edited. Last edited by: <Mabel>,

quote:

Originally posted by GeraldCohen:
[qb] As a guess I would make all of the &'s into
double &&'s. eg &&STARTYEAR, &&STARTMONTH, etc.

This preserves their values thru all of the reports which are needed in subsequent drill-downs. [/qb]

Global amper variables endure for the "session" (i.e., until disconnect), but then they are cleared so the next connect starts with a clean slate; besides which there is no guarantee that the drilldown will connect to the same agent.

For the drill-down report to inherit a parameter, you have to pass it to the drilldown with its content as the passed value:
( ... MyParm='&MyParm' ...)