multiple different results in the same report

March 26, 2007, 05:33 AM

Hi,

I want to generate a report with the first page showing all the parameters passed by the user and the subsequent pages showing the data fetched using these parameters.Moreover the first page is verticle i.e i'll display the selection parameters using OVER command.I am using 2 database stored procs for this.

1] EX FETCH_PARAM()
2] EX FETCH_DATA()

how do i develop such a report where two stored proc's needs to be executed (results fetched by these two stored proc's are different)and the data merged into the same file.

I am palnning to do something like the following.But it is not working -

-INCLUDE DB2_CONNECT

EX FETCH_PARAM(1111);

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TEMP1 FORMAT ALPHA
END

-INCLUDE DB2_CONNECT

EX FETCH_DATA(1111);

TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS TEMP2 FORMAT ALPHA
END

-HTMLFORM BEGIN
<html>
<body>
<br>
<div align="center">
  !IBI.FIL.TEMP1;
 </div>
  <div align="center">
  !IBI.FIL.TEMP2;
  </div>
</body>
</HTML>
-HTMLFORM END

Regards,
Aditya

March 26, 2007, 07:07 AM

Danny-SRL

Aditya,

Instead of FORMAT ALPHA use FORMAT HTMTABLE.
It should do the trick.

March 26, 2007, 07:19 AM

<JJI>

Danny,

Isn't there a -RUN needed after the EX FETCH_PARAM and FETCH_DATA? This way you are sure the proc has been executed before you do the TABLE FILE SQLOUT.

Just a thought.

March 26, 2007, 07:42 AM

Danny-SRL

JJI,
No, there shouldn't be a -RUN after the EX FETCH_PARAM because the TABLE FILE SQLOUT is part of the retrieving of the answer set.
However there probably should be a -RUN before the -HTMLFORM BEGIN

March 26, 2007, 07:46 AM

Aditya

Hi Danny,

I tried giving FORMAT HTMTABLE.But only the first proc's o/p is shown i.e TEMP1.

Regards,
Aditya

March 26, 2007, 08:08 AM

Danny-SRL

Aditya,
I suppose that in your -INCLUDE DB2_CONNECT you have an SQL DB2 statement.
Did you see if your FETCH_DATA sp retrieved records?

I ran this without any problem:

TABLE FILE CAR
SUM SALES BY COUNTRY
ON TABLE HOLD AS TEMP1 FORMAT HTMTABLE
END
-RUN

TABLE FILE CAR
SUM SALES BY BODYTYPE
ON TABLE HOLD AS TEMP2 FORMAT HTMTABLE
END
-RUN
-HTMLFORM BEGIN
<html>
<body>
<br>
<div align="center">
  !IBI.FIL.TEMP1;
 </div>
  <div align="center">
  !IBI.FIL.TEMP2;
  </div>
</body>
</HTML>
-HTMLFORM END