Hello:

I have this define field which separates customers into two groups, External and Internal...

quote:

-* DEFINE BEGIN rpt_sect
rpt_sect/A30 = IF (SECTION_CD EQ '1' OR SECTION_CD EQ '2') THEN 'External Customers' ELSE IF SECTION_CD EQ '3' THEN 'Internal Customers';
-* DEFINE END rpt_sect

I'm trying to use this define value to drill down to another report.

quote:

TYPE=DATA, COLUMN=rpt_sect,
FOCEXEC=report2.fex(parmsec=rpt_sect \
parmgrp= '&parmgrp' parmdiv= '&parmdiv'),
$

In report2, I have the same define and a parameter, parmsec:

quote:

sect_opt/A30 = IF (SECTION_CD EQ '1' OR SECTION_CD EQ '2') THEN 'External Customers' ELSE IF SECTION_CD EQ '3' THEN 'Internal Customers';

Here is my Where Statement in report2...

quote:

WHERE (GROUP_NUM EQ '&parmgrp')
AND (DIV_NUM EQ '&parmdiv')
AND (sect_opt EQ '&parmsec');

I can't seem to get this to work. When I click on the drilldown for one of the values in report1, it just keeps looping and asking me for the parameters over and over again. Any ideas? Is this the right or best way to handle this?

Thanks,
Tim J

tj, if i read you correctly, you've got a blank space in the element you're trying to use as a parm. Not a good idea...too much trougle.
In this case, keep your drill down on the field that says 'Internal Customers' but pass thru the variable SECTION_CD which you bring in to your Report1 record as a NOPRINT
BY SECTION_CD NOPRINT BY sect_opt
so your dd says
TYPE=DATA, COLUMN=rpt_sect,
FOCEXEC=report2.fex(parmsec=SECTION_CD \
parmgrp= '&parmgrp' parmdiv= '&parmdiv'),
...
and then repeat your define from rep1 inside of rep2.
ok?

Susannah:

That is the normal route I take with drilldowns, but for this report, I am combining two values for External Customers (SECTION_CD = 1 or 2). If I use SECTION_CD as a NOPRINT it will give me two summary lines for External Customers on the report.
I just want one summary line for External Customers (value of 1 or 2) and one for Internal Customers (value of 3).

My report2 accepts the parameters "External Customers" or "Internal Customers" and displays what I need, I just need to figure out why those values are not making it from the drilldown.

You mentioned a space in my literal. What problems can that cause?

Thanks,
Tim J

The first thing to do is verify if the three parameters are actually being passed:

Add

-? &
-EXIT

at the top of the drilldown program.

Then, if the three parameters are passed successfully, add this before the TABLE FILE:

SET XRETRIEVAL=OFF
-RUN

to disable data extraction and to check the syntax of the code. If the WHERE statment looks alright, try to determine why the program "loops". If you're accessing SQL db tables, you should set the SQL traces on to verify that the SQL generated by the JOIN, DEFINE and TABLE is correct.

theres a space between External and Customers
Do a view source on your report before you attemp toclick thru on the down,,and look carefully at the full url created by the drilldown.
theres a world of difference between a WHERE statement
WHERE sect_opt EQ 'External Customers'
and the way such a variable with an internal black space would get inserted into a drilldown URL.
run this code:

-SET &DD0='TESTCAR';
-DEFAULT &MYCOUNTRY = 'ALL';
-SET &FILTERCOUNTRY = IF &MYCOUNTRY EQ 'ALL' THEN ' ' ELSE 'IF COUNTRY IS ' | &MYCOUNTRY ;
TABLE FILE CAR
SUM SALES BY COUNTRY BY CAR
&FILTERCOUNTRY
ON TABLE SET STYLE *
TYPE=DATA,COLUMN=COUNTRY,FOCEXEC=&DD0(MYCOUNTRY=COUNTRY),$
END
{/code]

It'll work for evertying but W GERMANY.
why?
Because W GERMANY had an embedded blank in it.
Here's what the view source url looks like
[code]&IBIF_ex=TESTCAR&CLICKED_ON=&MYCOUNTRY=W%20GERMANY">W GERMANY</A>

and..
here's the error message you get whenyou run it.

 0 ERROR AT OR NEAR LINE     16  IN PROCEDURE testcar FOCEXEC *
 (FOC002) A WORD IS NOT RECOGNIZED: GERMANY
 BYPASSING TO END OF COMMAND
 (FOC009) INCOMPLETE REQUEST STATEMENT

After the blank between W and GERMANY, the constructed dd url has no idea what you want it to do.
SO....
you have to do a lot more work preparing your variable to become an acceptable paramter do a drilldown. Contiguous string is the requirement..so inmho its alot easier if you want to link on a nice string, like for me 'BERGDORF GOODMAN' , the value i actually pass is B2793, the code for BERGDORF GOODMAN.
Getting the picture?