I have a chained dynamic drop down list for a parameterized report. The data source of this report is from a sql stored proc (which has four parameters - all which are on the launch page). I need to include the 'All' option which currently will not return all results. It worked when I removed the dynamic chain and hard coded some values to test. It appears to be an issue with the dynamic chain which I cannot figure out?

The error code I receive is:

(FOC1400) SQLCODE IS -1 (HEX: FFFFFFFF)
: Microsoft OLE DB Provider for SQL Server: [] Command text was not set fo
: r the command object.
(FOC1414) EXECUTE IMMEDIATE ERROR.
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: SQLOUT
BYPASSING TO END OF COMMAND

Any help that can provided is much appreciated.

Thanks!
Joe

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

Can you please explain this a little further:

quote:

The data source of this report is from a sql stored proc (which has four parameters - all which are on the launch page).

The four parameters are throwing me off. Are you saying the four parameters in your sql stored proc are in addtion to the dynamic chained parameters?

Thanks for your reply. The four sql stored proc parameters are the four parameters on the launch page. I just need to add 'All' to each control which it doesnt like. I've coded the sql stored proc to except 'All' and when I tested the launch page with static values which included 'All' it worked. Once I set the controls to chained dynamic, it blows up however, it doesnt blow up as long as I do not select 'All'.

Thanks,

Joe

i believe 'select all' basically removes that item from the chain (i.e. 'ALL' = no limiting WHERE statement). Or maybe it sends FOC_NONE. Can't recall off the top of my head.

Have you tried 'TYPE'ing or javascript alerting the field to see what will be passed.

- ABT

This has to be handled in the FEX sperately.

If your stored proc expects 'ALL' or an empty ' ', then FOC_NONE has to be decoded to any of these, and assigned to an & SET variable.

That has to be passed to the SP, instead of directly passing the param from front end.

Ram,

Not sure if I'm doing this right. I tried tinkering with the -set dialouge I saw on here and still get that error.

Code below:

 -* File pulsemonthlytots.fex


ENGINE SQLMSS SET DEFAULT_CONNECTION IVB
SQL SQLMSS
EX ORBIT.dbo.PulseMonthlyTotals '&dte','&edte','&div','&grp','&tmn','&role'
;



TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS SQLOUT
END
DEFINE FILE SQLOUT
NEWDATE/TMYY=HDATE(Month,'MDYY');

END

-SET &div = IF &div EQ '' THEN 'FOC_NONE' ELSE ÷
-SET &grp = IF &grp EQ '' THEN 'FOC_NONE' ELSE &grp;
-SET &tmn = IF &tmn EQ '' THEN 'FOC_NONE' ELSE &tmn;
-SET &role = IF &role EQ '' THEN 'FOC_NONE' ELSE &role;


TABLE FILE SQLOUT
SUM
     Result
     PercentRank AS '%,Rank'
BY EmpID
BY Division
BY Group
BY Team
BY FName
BY LName
BY Role
ACROSS HIGHEST DataType NOPRINT
ACROSS NEWDATE
HEADING
""
FOOTING
""
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE * 

Do you have any examples I can look at?

quote:

This has to be handled in the FEX sperately.

IF you proc is expecting "ALL", then you can change your dynamic properties, in your launch page, to send whatever you'd like: FOC_NONE (the default), ALL, or sometghing else... Try that, you may just like it...

Thanks for all the tips..I got it to work. I thought about it and figured that the foc_none had to be defined at the top to be read as 'All'. This worked.

 -DEFAULT &div='All';
-DEFAULT &grp='All';
-DEFAULT &tmn='All';
-DEFAULT &role='All';

-SET &div=IF &div EQ 'FOC_NONE' THEN 'All' ELSE ÷
-SET &grp=IF &grp EQ 'FOC_NONE' THEN 'All' ELSE &grp;
-SET &tmn=IF &tmn EQ 'FOC_NONE' THEN 'All' ELSE &tmn;
-SET &role=IF &role EQ 'FOC_NONE' THEN 'All' ELSE &role;