Hi,

My Report drills down 3 separate times. In the final drilldown (below), I'm trying to pass the values for Manager, New Contact Desc, Mthrange, and Unique records. However, I am getting all the records for the individual Manager, instead of just the CNT.J3.RRR.UNIQUE records that correspond to the Manager, New Contact Desc and Mthrange criteria.

BTW, I tried to pass CNT.J3.RRR.UNIQUE and name it as PEOPLE....did not work.

Below is Text Editor View with Tablenames masked/replaced.
----------------------------------------------------------


TABLE FILE PK
PRINT MANAGER
BY 'UNIQUE'
HEADING
""
FOOTING
""
WHERE MANAGER EQ '&MANAGER.(FIND MANAGER IN PK).Select a Manager.';
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS PCKHOLD FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
JOIN
LEFT_OUTER PCKHOLD.SEG01.UNIQUE IN PCKHOLD TO UNIQUE
RRR.RRR.UNIQUE IN ADR TAG J3 AS J3
END
JOIN
LEFT_OUTER J3.RRR.UNIQUE IN PCKHOLD TO UNIQUE
CS.CS.UNIQUE IN CS
TAG J1 AS J1
END
JOIN
LEFT_OUTER J1.CS.UNIQUE IN PCKHOLD TO UNIQUE
RT.RT.UNIQUE IN RT TAG J2 AS J2
END
DEFINE FILE PCKHOLD ADD
RIGHTNOW/HYYMDS=HGETC(8,RIGHTNOW);
LSTCONTMNHS/I4 MISSING ON NEEDS ALL DATA=IF CONTACT_DATE NE MISSING THEN HDIFF(RIGHTNOW, CONTACT_DATE, 'MONTH', LSTCONTMNHS) ELSE 9999;
MTHRANGE/A22=
IF LSTCONTMNHS EQ 9999 THEN 'Never' ELSE
IF LSTCONTMNHS GT 12 THEN 'Greater than 1 Year' ELSE
IF LSTCONTMNHS LE 12 THEN ' Within Last 12 Months' ELSE 'ERROR';
NEW_CONTACT_DESC/A30=
IF CONTACT_DESC IS MISSING THEN ' Not Contacted'
ELSE EDIT(CONTACT_DESC, '999999999999999999999999999999');
END
-*CHECK FILE PCKHOLD
-*?FF PCKHOLD
-*TABLE FILE PCKHOLD
-*PRINT
-* J1.CS.MTHRANGE
-* J1.CS.CONTACT_DESC AS ''
-*BY PCKHOLD.SEG01.UNIQUE
-*BY J3.RRR.UNIQUE
-*BY J1.CS.UNIQUE
-*BY J2.RT.UNIQUE
-*BY LOWEST J3.RRR.UNIQUE AS ''
-*ON TABLE NOTOTAL
-*END
-*-EXIT
TABLE FILE PCKHOLD
SUM
PCKHOLD.SEG01.MANAGER NOPRINT
CNT.J3.RRR.UNIQUE AS ''
BY J1.CS.MTHRANGE
ACROSS J1.CS.NEW_CONTACT_DESC AS ''
HEADING
"Constituent Contacts by Date Range for <+0>&MANAGER<+0> "
FOOTING
""
WHERE MANAGER EQ '&MANAGER.(FIND MANAGER IN PK).Select a Manager.';
ON TABLE SET PAGE-NUM OFF
ON TABLE ROW-TOTAL AS 'TOTAL'
ON TABLE COLUMN-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
UNITS=IN,
PAGESIZE='Letter',
LEFTMARGIN=0.500000,
RIGHTMARGIN=0.500000,
TOPMARGIN=0.500000,
BOTTOMMARGIN=0.500000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
FONT='ARIAL',
SIZE=9,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
TYPE=DATA,
COLUMN=ROWTOTAL(1),
TARGET='_blank',
FOCEXEC=NONE,
$
TYPE=DATA,
COLUMN=ROWTOTAL(2),
TARGET='_blank',
FOCEXEC=NONE,
$
TYPE=DATA,
COLUMN=N1,
TARGET='_blank',
FOCEXEC=NONE,
$
TYPE=DATA,
ACROSSCOLUMN=N1,
TARGET='_blank',
FOCEXEC=NONE,
$
TYPE=DATA,
ACROSSCOLUMN=N2,
TARGET='_blank',
FOCEXEC=app/ds_three1( \
NEW_CONTACT_DESC=A1 \
MONTH_RANGE=N1 \
MANAGER=N2 \
PEOPLE=N3 \
),
$
TYPE=ACROSSVALUE,
COLUMN=ROWTOTAL(1),
BACKCOLOR='WHITE',
TARGET='_blank',
FOCEXEC=NONE,
$
TYPE=REPORT,
COLUMN=N2,
WRAP=6.000000,
$
ENDSTYLE
END

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

If this fex the second drilldown ?

Are you able to post app/ds_three1.fex ?

Here's the app/ds_three1.fex
------------------------------------

JOIN
LEFT_OUTER RRR.RRR.UNIQUE IN RRR TO UNIQUE
GS.GS.UNIQUE IN GS TAG J0 AS J0
END
JOIN
LEFT_OUTER RRR.RRR.UNIQUE IN RRR TO UNIQUE
PK.PK.UNIQUE IN PK TAG J1 AS J1
END
JOIN
LEFT_OUTER J0.GS.UNIQUE IN RRR TO UNIQUE
CS.CS.PERSON_UID IN CS
TAG J2 AS J2
END
TABLE FILE RRR
PRINT
RRR.RRR.ADDRESS_NAME
RRR.RRR.DECEASED NOPRINT
J0.GS.LAST_GIFT_DT
J0.GS.LAST_GIFT_AMT
J0.GS.LAST_GIFT_DESIGNATIN_DESC
J0.GS.TOTAL_AMT
RRR.RRR.PREF_LINE_1
RRR.RRR.PREF_LINE_2
RRR.RRR.PREF_LINE_3
RRR.RRR.PREF_LINE_4
RRR.RRR.PREF_LINE_5
RRR.RRR.PREF_LINE_6
RRR.RRR.PREF_LINE_7
RRR.RRR.PREF_LINE_8
BY RRR.RRR.ID
BY J2.CS.CONTACT_DATE AS 'LAST_CONTACT_DATE'
BY LOWEST J1.PK.MANAGER
BY J1.PK.CAPACITY AS 'STAGE'
HEADING
""
FOOTING
""
WHERE (( DECEASED EQ MISSING ) OR ( DECEASED EQ 'N' ));
-* WHERE NEW_CONTACT_DESC EQ '&NEW_CONTACT_DESC';
WHERE MANAGER EQ '&MANAGER';
WHERE PEOPLE EQ RRR.UNIQUE;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N18,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N17,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N16,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N15,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N14,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N13,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N12,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N11,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N9,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N5,
WRAP=6.000000,
$
ENDSTYLE
END

I think that step one should be to verify that the right values are being passed to ds_three1.fex.

Can you add

-? &
-EXIT

To the start of ds_three1.fex, and check that &NEW_CONTACT_DESC, &MONTH_RANGE, &MANAGER and &PEOPLE have the expected values.

Next your where clause only has two &variables.

WHERE (( DECEASED EQ MISSING ) OR ( DECEASED EQ 'N' ));
-* WHERE NEW_CONTACT_DESC EQ '&NEW_CONTACT_DESC';
WHERE MANAGER EQ '&MANAGER';
WHERE PEOPLE EQ RRR.UNIQUE;

Should WHERE PEOPLE EQ RRR.UNIQUE be WHERE &PEOPLE EQ RRR.UNIQUE ?

Corrected my WHERE statements to include all 4 variables.

I added "-? & - EXIT" at the top like you suggested and it did pass all the values correctly.

I then tried to run the report again, without the but recevied and error:

ERROR AT OR NEAR LINE 39 IN PROCEDURE ds_three1FOCEXEC *
(FOC258) FIELDNAME OR COMPUTATIONAL ELEMENT NOT RECOGNIZED: NEW_CONTACT_DESC
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

Suggestions?

If you add -SET &ECHO=ALL; to the beginning of ds_three1, what is shown in the output ?

Is line 39, "-* WHERE NEW_CONTACT_DESC EQ '&NEW_CONTACT_DESC';"

This error usually means that a field doesn't exist, either as a database field or a defined or computed field.

So I will copy the text for the defined field from second drilldown and add to third drilldown.

added a define to third drilldown, and still getting same error.

Please add

-SET &ECHO=ALL;

to the beginning of ds_three1.fex

Change ON TABLE PCHOLD FORMAT HTML to ON TABLE HOLD FORMAT HTML.

Post the resulting output from the third drilldown.

Made changes suggested, but used:
ON TABLE NEWHOLD FORMA4T HTML

Same error at bottom.

--------------------------

JOIN
LEFT_OUTER RRR.RRR.UNIQUE IN RRR TO UNIQUE
GS.GS.UNIQUE IN GS TAG J0 AS J0
END
JOIN
LEFT_OUTER RRR.RRR.UNIQUE IN RRR TO UNIQUE
PK.PK.UNIQUE IN PK TAG J1 AS J1
END
JOIN
LEFT_OUTER J0.GS.UNIQUE IN RRR TO UNIQUE
CS.CS.UNIQUE IN CS
TAG J2 AS J2
END
DEFINE FILE NEWHOLD ADD
RIGHTNOW/HYYMDS=HGETC(8,RIGHTNOW);
LSTCONTMNHS/I4 MISSING ON NEEDS ALL DATA=IF CONTACT_DATE NE MISSING THEN HDIFF(RIGHTNOW, CONTACT_DATE, 'MONTH', LSTCONTMNHS) ELSE 9999;
MTHRANGE/A22=
IF LSTCONTMNHS EQ 9999 THEN 'Never' ELSE
IF LSTCONTMNHS GT 12 THEN 'Greater than 1 Year' ELSE
IF LSTCONTMNHS LE 12 THEN ' Within Last 12 Months' ELSE 'ERROR';
NEW_CONTACT_DESC/A30=
IF CONTACT_DESC IS MISSING THEN ' Not Contacted'
ELSE EDIT(CONTACT_DESC, '999999999999999999999999999999');
END
TABLE FILE RRR
PRINT
RRR.RRR.ADDRESS_NAME
RRR.RRR.DECEASED NOPRINT
J0.GS.LG_DT
J0.GS.LG_AMT
J0.GS.LG_DESIGNATIN_DESC
J0.GS.TOTAL_AMT
RRR.RRR.PL_FIELD_1
RRR.RRR.PL_FIELD_2
RRR.RRR.PL_FIELD_3
RRR.RRR.PL_FIELD_4
RRR.RRR.PL_FIELD_5
RRR.RRR.PL_FIELD_6
RRR.RRR.PL_FIELD_7
RRR.RRR.PL_FIELD_8
BY RRR.RRR.ID
BY J2.CS.CONTACT_DATE AS 'LAST_CONTACT_DATE'
BY LOWEST J1.PK.MANAGER
BY J1.PK.CAPACITY AS 'STAGE'
HEADING
""
FOOTING
""
WHERE (( DECEASED EQ MISSING ) OR ( DECEASED EQ 'N' ));
WHERE NEW_CONTACT_DESC EQ 'XXXX Visit';
WHERE MANAGER EQ Manager Name;
WHERE MTHRANGE EQ ' ';
WHERE PEOPLE EQ RRR.UNIQUE;
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE NEWHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-INCLUDE endeflt
type=report,
color=rgb(66 70 73),
font='Trebuchet MS',
size=9,
squeeze=on,
grid=off,
$
type=pagenum,
style=italic,
size=6,
justify=right,
$
type=tabheading,
style=bold+italic,
size=14,
justify=center,
$
type=tabfooting,
style=bold+italic,
size=12,
justify=center,
border-top=heavy,
border-top-color=rgb(52 55 58),
$
type=heading,
style=bold,
size=12,
justify=center,
$
type=footing,
style=bold,
size=10,
justify=center,
$
type=title,
backcolor=rgb(102 102 102),
color=rgb(255 255 255),
style=-underline+bold,
$
type=data,
backcolor=(rgb(255 255 255) rgb(235 235 240)),
$
type=acrosstitle,
border-top=light,
border-bottom=heavy,
border-top-color=rgb(52 55 58),
border-bottom-color=rgb(52 55 58),
size=9,
style=-underline+bold,
$
type=acrossvalue,
backcolor=rgb(218 225 232),
style=bold,
$
type=subhead,
size=10,
style=bold,border-top=heavy,
border-bottom=light,
border-top-color=rgb(52 55 58),
border-bottom-color=rgb(52 55 58),
$
type=subfoot,
size=10,
style=bold,
border-top=light,
border-bottom=medium,
border-top-color=rgb(52 55 58),
border-bottom-color=rgb(52 55 58),
$
type=subtotal,
backcolor=RGB(200 200 200),
style=bold,
border-top=light,
border-top-color=rgb(66 70 73),
$
type=grandtotal,
backcolor=rgb(66 70 73),
color=rgb(255 255 255),
style=bold,
border-top=light,
border-bottom=heavy,
border-top-color=rgb(66 70 73),
border-top-style=solid,
border-bottom-style=double,
$
$
TYPE=REPORT,
COLUMN=N4,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N18,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N17,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N16,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N15,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N14,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N13,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N12,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N11,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N9,
WRAP=6.000000,
$
TYPE=REPORT,
COLUMN=N5,
WRAP=6.000000,
$
ENDSTYLE
END
0 ERROR AT OR NEAR LINE 15 IN PROCEDURE ds_three1
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: NEWHOLD
0 ERROR AT OR NEAR LINE 57 IN PROCEDURE ds_three1
(FOC002) A WORD IS NOT RECOGNIZED: NEWHOLD
BYPASSING TO END OF COMMAND
(FOC009) INCOMPLETE REQUEST STATEMENT

A couple of things.

By changing PCHOLD to NEWHOLD, you have added an invalid command. ON TABLE HOLD will only hold the report in the agents temporary session.

You changed RRR in the DEFINE to NEWHOLD, this also caused an error.

The key part of the output is the WHERE causes.

Your drilldown parameters are:

TYPE=DATA,
ACROSSCOLUMN=N2,
TARGET='_blank',
FOCEXEC=app/ds_three1( \
NEW_CONTACT_DESC=A1 \
MONTH_RANGE=N1 \
MANAGER=N2 \
PEOPLE=N3 \

The assumption is that you will have four amper variables.
&NEW_CONTRACT_DESC
&MONTH_RANGE
&MANAGER
&PEOPLE


Your original source code was:

WHERE (( DECEASED EQ MISSING ) OR ( DECEASED EQ 'N' ));
-* WHERE NEW_CONTACT_DESC EQ '&NEW_CONTACT_DESC';
WHERE MANAGER EQ '&MANAGER';
WHERE PEOPLE EQ RRR.UNIQUE;

I think that the line WHERE PEOPLE EQ RRR.UNIQUE; should be WHERE '&PEOPLE' EQ RRR.UNIQUE;.

Your output shows:

WHERE (( DECEASED EQ MISSING ) OR ( DECEASED EQ 'N' ));
WHERE NEW_CONTACT_DESC EQ 'XXXX Visit';
WHERE MANAGER EQ Manager Name;
WHERE MTHRANGE EQ ' ';
WHERE PEOPLE EQ RRR.UNIQUE;

This indicates that you no longer have quotes around &MANAGER, it should be WHERE MANAGER EQ '&MANAGER';

I looked at the code and Manager is in quotes, but when I copied output to mask some of the tables/field names....I dropped the quotes- accident, but they are there.

Noticed that MTHRANGE is blank - see below:

MTHRANGE EQ ' ';

Is that correct?

The contents of the & variables will rely on the data in the parent report.

What is the value of J1.CS.MTHRANGE ?

It calculates number of months based on 3 values:

1. Within Last 12 Months
2. Greater Than 1 Year
3. Never

That info did get passed correctly when adding "-? & - EXIT"

Thats interesting...

The variable is passed correctly, but at the WHERE clause its blank ?