I'm building a report that will accept a user prompt and hope to be able to provide the prompt as a drop down list as right now manually entering the value. It seems that the prompt is working as far as selecting the criteria in the WHERE clause, but when I tried to display the prompt on the report, I get an error.

(FOC282) RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD:
TERMYR

In Report Painter, when I hover the mouse over the External Variable, it looks, like it is being evaluated as a D12.2 even though the intent is to have it be A9 since the user enters 201130_40 into the prompt.

The other question I have, is once I get this working, will the information carry down to the drill down reports or will each report the user clicks on prompt again?

Also, if there is a better/proper way to assign/calculate the variables, would appreciate that too.

The user can choose a single TERM or a combined TERM as follows:
201130
201140
201130_40

If user chooses 201130:
In the WHERE clause, if they enter a term without the underscore, would evaluate to WHERE ACADEMIC_PERIOD = '201130'

If user chooses 201130_40:
If they enter a term with the underscore, would evaluate to WHERE ACADEMIC_PERIOD BETWEEN '201130' AND '201140', however, I couldn't get that to work so in the variable where clause I ended up with IN ('201130', '201140'). Perhaps EDIT() is not the right function to use?

Here is the code.

-*DEFAULT &TERM = ''
SET ASNAMES = ON
-SET &TERMSTART =  EDIT(&TERM, '999999$$');
-SET &TERMEND = IF EDIT(&TERM, '$$$$$$$99') EQ '40' THEN EDIT(&TERM,'9999$$$$$') || '40' ELSE '';
-SET &TERMWHERE = IF &TERMEND.LENGTH LT 3 THEN ' = ' || &TERMSTART  ELSE ' in (' || &TERMSTART || ', ' || &TERMEND || ')';

-*SET &TERMEND = '201140';
-*-SET &TERMWHERE = ' in (' || &TERM || ', ' || &TERMEND || ')';
-*SET &TERMWHERE = ' = ' || &TERMEND;
-*SET &TERMWHERE = ' BETWEEN ' || &TERM || ' AND ' || &TERMEND;
-*IF &TERMEND.LENGTH LT 5 THEN ' = ' || &TERM ELSE ' between ' || &TERM || ' and ' || &TERMEND;
ENGINE SQLORA SET DEFAULT_CONNECTION ODSP
SQL SQLORA PREPARE SQLOUT FOR
select distinct
        person_uid, academic_period, student_level,  stu_population, college,
               APPLIED,
               ACCEPTED,
               Deposit,
			   CASE WHEN Accepted = 1 AND No_Deposit = 1 and Cancelled = 0 THEN 1 ELSE 0 END AS No_Deposit,
			   Cancelled,
               Deferred,
               Denied,
               Pending,
               Withdrawn,
               Waitlist,
               Hold
       from slottoday a
       where SUBSTR(PROGRAM,1,2) <> 'ND'
	   AND college NOT IN ('00', 'LW')
       AND stu_population   not in ('J', 'S', 'V')
	   AND academic_period &TERMWHERE
-*       AND academic_period between '201130' and '201140'
-*	   AND trunc(date_updated) = trunc(sysdate)
;
END
TABLE FILE SQLOUT
PRINT
-*     COMPUTE TERMYR/A4 = EDIT(ACADEMIC_PERIOD, '9999$$');
     COMPUTE TERMYR/A9 = IF &TERMEND.LENGTH LT 3 THEN EDIT(&TERMSTART) ELSE EDIT(&TERMSTART) || '_40';
     COMPUTE STU_LEVEL/A2 = IF STUDENT_LEVEL EQ 'GR' OR STUDENT_LEVEL EQ 'DR' THEN 'GR' ELSE STUDENT_LEVEL;
     COMPUTE STU_POP/A1 = IF STU_POPULATION EQ 'G' OR STU_POPULATION EQ 'D' OR STU_POPULATION EQ 'Y' OR STU_POPULATION EQ 'Z' THEN 'G' ELSE STU_POPULATION;
     COLLEGE
     APPLIED
     ACCEPTED
     COMPUTE DEP/D12.2 = DEPOSIT;
	 NO_DEPOSIT
     CANCELLED
     DEFERRED
     DENIED
     PENDING
     WITHDRAWN
     WAITLIST
     COMPUTE HOLDS/D12.2 = HOLD;
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE HOLD AS STATSLOTDETAIL FORMAT FOCUS
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END
DEFINE FILE STATSLOTDETAIL ADD
Academic_Period/A9=TERMYR;
END
TABLE FILE STATSLOTDETAIL
SUM
     APPLIED/I5
     ACCEPTED/I5
     DEP/I5 AS 'DEPOSIT'
     NO_DEPOSIT/I5 AS 'NO DEPOSIT'
     CANCELLED/I5
     DEFERRED/I5
     DENIED/I5
     PENDING/I5
     WITHDRAWN/I5
     WAITLIST/I5
     HOLDS/I5 AS 'HOLD'
BY  LOWEST TERMYR
BY  HIGHEST STU_LEVEL AS 'STUDENT LEVEL'
BY  LOWEST STU_POP
BY  LOWEST COLLEGE

ON STU_LEVEL SUBTOTAL AS '*TOTAL'

ON STU_POP SUBTOTAL AS '*TOTAL STU_POP'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=DATA,
     COLUMN=N4,
     TARGET='_self',
     DRILLMENUITEM='By Gender',
          FOCEXEC=app/mainreport1,
     DRILLMENUITEM='By Campus',
          FOCEXEC=app/drilldownreportgender1,
     DRILLMENUITEM='By Domestic International',
          FOCEXEC=app/drilldownreportcampus1,
     DRILLMENUITEM='By GPA',
          FOCEXEC=app/xhgpe00g,
     DRILLMENUITEM='By School Type',
          FOCEXEC=app/hfg3x5d3,
$
TYPE=TITLE,
     COLUMN=N4,
     TARGET='_self',
     DRILLMENUITEM='By Gender',
          FOCEXEC=app/mainreport1,
     DRILLMENUITEM='By Campus',
          FOCEXEC=app/drilldownreportgender1,
     DRILLMENUITEM='By Domestic International',
          FOCEXEC=app/drilldownreportcampus1,
     DRILLMENUITEM='By GPA',
          FOCEXEC=app/xhgpe00g,
     DRILLMENUITEM='By School Type',
          FOCEXEC=app/hfg3x5d3,
$
TYPE=REPORT,
     COLUMN=N4,
     SQUEEZE=0.500000,
$
ENDSTYLE
END

 

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

Since &TERMSTART is in alpha format, by using EDIT(&TERMSTART) you are trying to put a numeric value into alpha field TERMYR. Instead, try this:

COMPUTE TERMYR/A9 = IF &TERMEND.LENGTH LT 3 THEN '&TERMSTART' ELSE '&TERMSTART' || '_40';

Thanks Dan. That solved the display issue.

Any thoughts about how do I pass down the variables to the Drill downs? I created the drill downs by copying and pasting them as separate reports that contain the same code as above, except that for each version of the drill down, the SQL statement contains the additional field(s) needed to display the data such as Gender, Campus, etc. The layout in the PRINT and SUM section are different in that they contain the additional cols that are for each level of detail added.