Hi,

My name is Kymberly. I am trying to create a compound report. I am getting the data to output to the PDF file however in the compound report I have 4 seperate files(queries). The first two of the queries,both seperate files in the compound report, I have formatted in the HEADER. This issue is that their is a big space between the HEADER info in the first file and the HEADER info in the second file.

1 - How do I get rid of this space between HEADERS in the first two files?

2- How do I merge HOLD files?

3- Can I call data from two different hold files in the same page? If so, how b/c TABLE FILE XXXXX ( hold file name ) only calls one hold file?


PLEASE HELP. I am stumped as of now.


Thanks,

Kym

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

One way to merge two files, but only if they have the exact same column/field names, is to use the MORE keyword. You might be able to merge the two header files in the same fashion.

TABLE FILE HOLD1
 PRINT col1
       col2
       col3
       .
       .
 BY sortcol1
 BY sortcol2
       .
       .
MORE
FILE HOLD2
END

Dan,

I am trying to do something like you suggested. In the queries i'm doing MY_NAME AS WPNAME and MY_NAME AS APPNAME. This is the only difference between column names. I tried your suggestion and got and error.

(FOC002) A WORD IS NOT RECOGNIZED: OWNER_ID
BYPASSING TO END OF COMMAND



TABLE FILE HEADER_1
PRINT

OWNER_ID NOPRINT
OWNER_ORG NOPRINT
ACTIVITY_TITLE NOPRINT
APPNAME NOPRINT
BY ACTIVITY_ID NOPRINT

MORE

FILE HEADER_2


OWNER_ID NOPRINT
OWNER_ORG NOPRINT
ACTIVITY_TITLE NOPRINT
WPNAM NOPRINT
BY ACTIVITY_ID NOPRINT



ON TABLE
HEADING
"Approving Mgr Name:<+0> Approving Mgr Org:<+0> """
WHERE ACTIVITY_ID EQ &ACTIVITY_ID ;


Thanks,

Kym

When using MORE, the column names are only mentioned once, for the first file. That is why the names must be the same in both files. Also, I don't understand why you are using NOPRINT with all of the columns. If one or more columns are not needed in the final output, then remove them from the query.

DEFINE FILE HEADER_1
 WPNAM/A?? = APPNAME ;
END
-*
TABLE FILE HEADER_1
 PRINT
  OWNER_ID
  OWNER_ORG
  ACTIVITY_TITLE
  WPNAM
 BY ACTIVITY_ID
 WHERE ACTIVITY_ID EQ &ACTIVITY_ID ;
 HEADING
  "Approving Mgr Name:<+0> Approving Mgr Org:<+0>

MORE 

FILE HEADER_2
END

Dan,

I want to print out all of these columns in the heading. If i print them they will be output in a report format. I just want them in the header.

The columns are the same that I'm using;however, in the 2 hold files I am merging I have a different person I need to grab.

For instance, In the first query I am doing a WHERE UPPER(a.work_planner) = UPPER(ep.SL_OPERID)

This will give me the WORK PLANNER NAME for SL_NAME

In the second query I am doing
UPPER(ap.APPROVAL_PERSON) = UPPER(ep.SL_OPERID)

This will give me the APPROVER NAME for SL_NAME.

Therefore, I am doing an AS in the first query

SL_NAME AS WPNAME

and in the 2nd query I am doing

SL_NAME AS APNAME



You said the columns have to be the same. How would I return two different names for my queries if the columns have to stay the same OR am I on the right track? The queries are the exact same except for the WHERE statement and the AS statement listed above.

Then the TABLE PRINT:



TABLE FILE HEADER_1
PRINT

ACTIVITY_ID NOPRINT
OWNER_ID NOPRINT
OWNER_ORG NOPRINT
ACTIVITY_TITLE NOPRINT
ACTIVITY_ORG NOPRINT
ACTIVITY_LOCATION NOPRINT
JSA_TITLE NOPRINT
WORK_PLANNER NOPRINT
WORK_PLANNER_ORG NOPRINT
WP_PHONE NOPRINT
SCOPE_DESC NOPRINT
REASON NOPRINT
ENVELOPE NOPRINT
APPROVAL_PERSON NOPRINT
APPROVAL_ORG NOPRINT
APPROVAL_ROLE NOPRINT
CREATED_DATE NOPRINT
APPNAME NOPRINT
BY ACTIVITY_ID NOPRINT

MORE

FILE HEADER_2

ACTIVITY_ID NOPRINT
OWNER_ID NOPRINT
OWNER_ORG NOPRINT
ACTIVITY_TITLE NOPRINT
ACTIVITY_ORG NOPRINT
ACTIVITY_LOCATION NOPRINT
JSA_TITLE NOPRINT
WORK_PLANNER NOPRINT
WORK_PLANNER_ORG NOPRINT
WP_PHONE NOPRINT
SCOPE_DESC NOPRINT
REASON NOPRINT
ENVELOPE NOPRINT
APPROVAL_PERSON NOPRINT
APPROVAL_ORG NOPRINT
APPROVAL_ROLE NOPRINT
CREATED_DATE NOPRINT
WPNAME NOPRINT
BY ACTIVITY_ID NOPRINT



ON TABLE
HEADING
"Approving Mgr Name:<+0> Approving Mgr Org:<+0> "WorkPlanner: ""
WHERE ACTIVITY_ID EQ &ACTIVITY_ID ;

Thanks,

Jon

If you need the column names for the headers, can't you let WebFOCUS print them as column titles, or use HEADING and/or SUBHEAD statements to create the headers?

Are APPNAME and WPNAME two distinct columns in the final output? If so, then you cannot use MORE unless you DEFINE a dummy WPNAME for the first table and a dummy APPNAME for the second table. If APPNAME and WPNAME can appear in the same column, then simply PRINT SL_NAME without the "AS" or DEFINEs in both cases. I believe you can specify different WHERE clauses for the first and second files in a query using MORE. It might be easier to merge the data with MORE, create a HOLD file, and then use the HOLD file to create your final report.

DEFINE FILE <filename1>
 APPNAME/A?? = SL_NAME ;
 WPNAME/A??  = ' ';
END
-*
DEFINE FILE <filename2>
 APPNAME/A?? = ' ';
 WPNAME/A??  = SL_NAME ;
END
-*
TABLE FILE <filename1>
 PRINT
  ACTIVITY_ID
  OWNER_ID
  OWNER_ORG
  ACTIVITY_TITLE
  ACTIVITY_ORG
  ACTIVITY_LOCATION
  JSA_TITLE
  WORK_PLANNER
  WORK_PLANNER_ORG
  WP_PHONE
  SCOPE_DESC
  REASON
  ENVELOPE
  APPROVAL_PERSON
  APPROVAL_ORG
  APPROVAL_ROLE
  CREATED_DATE
  APPNAME
  WPNAME
 BY ACTIVITY_ID
 WHERE UPPER(a.work_planner) = UPPER(ep.SL_OPERID);
 ON TABLE HOLD
  
MORE
FILE <filename2>
 WHERE UPPER(ap.APPROVAL_PERSON) = UPPER(ep.SL_OPERID);
END
-*
TABLE FILE HOLD
 PRINT
 .
 .
 BY
 .
 HEADING
  "Approving Mgr Name:<+0> Approving Mgr Org:<+0> WHERE ACTIVITY_ID EQ &ACTIVITY_ID ;
END

This message has been edited. Last edited by: Dan Satchell,

I'm using SQL pass through. Does the MORE work with SQL pass through?

No. Save your output from the passthru requests into hold files then MORE the hold files.

I'm getting the error:

(FOC1517) UNRECOGNIZED COMMAND ON TABLE
(FOC1517) UNRECOGNIZED COMMAND HEADING
(FOC1517) UNRECOGNIZED COMMAND ""
(FOC1517) UNRECOGNIZED COMMAND ON TABLE SET PAGE-NUM OFF
(FOC1517) UNRECOGNIZED COMMAND ON TABLE NOTOTAL
(FOC1517) UNRECOGNIZED COMMAND ON TABLE SET PAGE NOLEAD
(FOC1517) UNRECOGNIZED COMMAND ON TABLE PCHOLD FORMAT PDF
(FOC1517) UNRECOGNIZED COMMAND ON TABLE SET HTMLCSS ON
(FOC1517) UNRECOGNIZED COMMAND ON TABLE SET STYLE *
(FOC1517) UNRECOGNIZED COMMAND UNITS=IN,
(FOC1517) UNRECOGNIZED COMMAND SQUEEZE=ON,
(FOC1517) UNRECOGNIZED COMMAND ORIENTATION=PORTRAIT,
(FOC1517) UNRECOGNIZED COMMAND GRID=OFF,
(FOC1517) UNRECOGNIZED COMMAND LEFTMARGIN=0.250000,
(FOC1517) UNRECOGNIZED COMMAND RIGHTMARGIN=0.250000,
(FOC1517) UNRECOGNIZED COMMAND TOPMARGIN=0.250000,
(FOC1517) UNRECOGNIZED COMMAND BOTTOMMARGIN=0.250000,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=REPORT,
(FOC1517) UNRECOGNIZED COMMAND GRID=OFF,
(FOC1517) UNRECOGNIZED COMMAND FONT='ARIAL',
(FOC1517) UNRECOGNIZED COMMAND SIZE=10,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=TITLE,
(FOC1517) UNRECOGNIZED COMMAND STYLE=BOLD,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=TABHEADING,
(FOC1517) UNRECOGNIZED COMMAND SIZE=12,
(FOC1517) UNRECOGNIZED COMMAND STYLE=BOLD,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=TABFOOTING,
(FOC1517) UNRECOGNIZED COMMAND SIZE=12,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING, BOTTOMGAP=0.05, $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING, TOPGAP=0.05, $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING, SIZE=10 GRID=OFF,WIDTH=10.0000,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING, LINE=1, SIZE=10, STYLE=BOLD,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING, LINE=1,OBJECT=TEXT, COLOR='NAVY',
ITEM=1,WIDTH=1.5000, JUSTIFY=RIGHT,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING,
LINE=1,OBJECT=TEXT,ITEM=2,WIDTH=0.20000,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING, LINE=1,OBJECT=TEXT,ITEM=3,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING, LINE=1,OBJECT=TEXT,ITEM=4,
COLOR='NAVY',WIDTH=1.5000, JUSTIFY=RIGHT,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING,
LINE=1,OBJECT=TEXT,ITEM=5,WIDTH=0.20000,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND TYPE=HEADING, LINE=1,OBJECT=TEXT,ITEM=6,
(FOC1517) UNRECOGNIZED COMMAND $
(FOC1517) UNRECOGNIZED COMMAND ENDSTYLE


Here is the code I'm using.



-* jsa_header
ENGINE SQLORA SET DEFAULT_CONNECTION &MYSERV
SQL SQLORA PREPARE JSA_HEADER FOR
SELECT

a.ACTIVITY_ID ,
A.OWNER_ID ,
a.OWNER_ORG ,
a.ACTIVITY_TITLE ,
a.ACTIVITY_ORG ,
a.ACTIVITY_LOCATION ,
a.JSA_TITLE ,
a.WORK_PLANNER ,
a.WORK_PLANNER_ORG ,
a.WP_PHONE ,
a.SCOPE_DESC ,
a.REASON ,
a.ENVELOPE ,
ap.APPROVAL_PERSON ,
ap.APPROVAL_ORG ,
ap.APPROVAL_ROLE ,
ap.CREATED_DATE ,
ep.SL_NAME AS WPPERSON


FROM

ACT a ,
APP ap ,
per ep

WHERE
a.ACTIVITY_ID = ap.ACTIVITY_ID
AND UPPER(a.work_planner) = UPPER(ep.SL_OPERID)
AND a.ACTIVITY_ID = &ACTIVITY_ID;


-*PUT RESULTS OF QUERY INTO A WEBFOCUS HOLD FILE
TABLE
ON TABLE HOLD AS HEADER_1
END
-RUN
DEFINE FILE HEADER_1
AP_PERSON/A50V = ' ';
WP_PERSON/A50V = WPPERSON;
END
-RUN

ENGINE SQLORA SET DEFAULT_CONNECTION &MYSERV
SQL SQLORA PREPARE JSA_HEADER_II FOR
SELECT

a.ACTIVITY_ID ,
A.OWNER_ID ,
a.OWNER_ORG ,
a.ACTIVITY_TITLE ,
a.ACTIVITY_ORG ,
a.ACTIVITY_LOCATION ,
a.JSA_TITLE ,
a.WORK_PLANNER ,
a.WORK_PLANNER_ORG ,
a.WP_PHONE ,
a.SCOPE_DESC ,
a.REASON ,
a.ENVELOPE ,
ap.APPROVAL_PERSON ,
ap.APPROVAL_ORG ,
ap.APPROVAL_ROLE ,
ap.CREATED_DATE ,
ep.SL_NAME AS APNAME


FROM

ACT a ,
APP ap ,
per ep

WHERE
a.ACTIVITY_ID = ap.ACTIVITY_ID
AND UPPER(ap.APPROVAL_PERSON) = UPPER(ep.SL_OPERID)
AND a.ACTIVITY_ID = &ACTIVITY_ID;


TABLE
ON TABLE HOLD AS HEADER_2
END
-RUN

DEFINE FILE HEADER_2
AP_PERSON/A50V = APNAME;
WP_PERSON/A50V = ' ';
END
-RUN
-*PUT RESULTS OF QUERY INTO A WEBFOCUS HOLD FILE



TABLE FILE HEADER_1
PRINT

ACTIVITY_ID NOPRINT
OWNER_ID NOPRINT
OWNER_ORG NOPRINT
ACTIVITY_TITLE NOPRINT
ACTIVITY_ORG NOPRINT
ACTIVITY_LOCATION NOPRINT
JSA_TITLE NOPRINT
WORK_PLANNER NOPRINT
WORK_PLANNER_ORG NOPRINT
WP_PHONE NOPRINT
SCOPE_DESC NOPRINT
REASON NOPRINT
ENVELOPE NOPRINT
APPROVAL_PERSON NOPRINT
APPROVAL_ORG NOPRINT
APPROVAL_ROLE NOPRINT
CREATED_DATE NOPRINT
AP_PERSON NOPRINT
WP_PERSON NOPRINT
BY ACTIVITY_ID NOPRINT
ON TABLE HOLD AS NEWTABLE

MORE

FILE HEADER_2

END
-RUN


ON TABLE
HEADING
"Approving Mgr Name:<+0> Approving Mgr Org:<+0> "WorkPlanner: ""

quote:

ON TABLE
HEADING
"Approving Mgr Name:<+0> <+0> Approving Mgr Org:<+0>
"WorkPlanner:
""

You have an orphan ON TABLE keyword that does not belong to any request. It should be something like:

TABLE FILE NEWTABLE
SUM
   ...
ON TABLE
HEADING
"blah"
END

You can't just have the ON TABLE HEADING stuff hanging out there without being associated with a TABLE request. With the way you've got it constructed which looks fine by the way, after the last end, put this:

TABL FILE NEWTABLE
PRINT ...
BY ...
HEADING
"..."
ON TABLE PCHOLD FORMAT whatever
END

I think you are very close.

I get a value back for Work Planner but not Approver Person.

Is it even looking at the HEAD2 ? How would I get both Work Planner and Approver Person to Show up?


-* jsa_header
SET DEFAULT_CONNECTION &MYSERV
SET SQLENGINE=SQLORA
SQL SET SERVER &MYSERV
SQL
SELECT

a.ACTIVITY_ID ,
A.OWNER_ID ,
a.OWNER_ORG ,
a.ACTIVITY_TITLE ,
a.ACTIVITY_ORG ,
a.ACTIVITY_LOCATION ,
a.JSA_TITLE ,
a.WORK_PLANNER ,
a.WORK_PLANNER_ORG ,
a.WP_PHONE ,
a.SCOPE_DESC ,
a.REASON ,
a.ENVELOPE ,
ap.APPROVAL_PERSON ,
ap.APPROVAL_ORG ,
ap.APPROVAL_ROLE ,
ap.CREATED_DATE ,
ep.SL_NAME AS WPPER


FROM

ACT a ,
APP ap ,
per ep

WHERE
a.ACTIVITY_ID = ap.ACTIVITY_ID
AND UPPER(a.work_planner) = UPPER(ep.SL_OPERID)
AND a.ACTIVITY_ID = '&ACTIVITY_ID.EVAL';


-*PUT RESULTS OF QUERY INTO A WEBFOCUS HOLD FILE
TABLE
ON TABLE HOLD AS HEAD1
END
-RUN
DEFINE FILE HEAD1
AP_PER/A50V = ' ';
WP_PER/A50V = WPPER;
END
-RUN

SET DEFAULT_CONNECTION &MYSERV
SET SQLENGINE=SQLORA
SQL SET SERVER &MYSERV
SQL
SELECT

a.ACTIVITY_ID ,
A.OWNER_ID ,
a.OWNER_ORG ,
a.ACTIVITY_TITLE ,
a.ACTIVITY_ORG ,
a.ACTIVITY_LOCATION ,
a.JSA_TITLE ,
a.WORK_PLANNER ,
a.WORK_PLANNER_ORG ,
a.WP_PHONE ,
a.SCOPE_DESC ,
a.REASON ,
a.ENVELOPE ,
ap.APPROVAL_PERSON ,
ap.APPROVAL_ORG ,
ap.APPROVAL_ROLE ,
ap.CREATED_DATE ,
ep.SL_NAME AS APNAME


FROM

ACT a ,
APP ap ,
per ep

WHERE
a.ACTIVITY_ID = ap.ACTIVITY_ID
AND UPPER(ap.APPROVAL_PERSON) = UPPER(ep.SL_OPERID)
AND a.ACTIVITY_ID = '&ACTIVITY_ID.EVAL';


-*PUT RESULTS OF QUERY INTO A WEBFOCUS HOLD FILE
TABLE
ON TABLE HOLD AS HEAD2
END
-RUN

DEFINE FILE HEAD2
AP_PER/A50V = APNAME;
WP_PER/A50V = ' ';
END
-RUN



TABLE FILE HEAD1
PRINT

ACTIVITY_ID NOPRINT
OWNER_ID NOPRINT
OWNER_ORG NOPRINT
ACTIVITY_TITLE NOPRINT
ACTIVITY_ORG NOPRINT
ACTIVITY_LOCATION NOPRINT
JSA_TITLE NOPRINT
WORK_PLANNER NOPRINT
WORK_PLANNER_ORG NOPRINT
WP_PHONE NOPRINT
SCOPE_DESC NOPRINT
REASON NOPRINT
ENVELOPE NOPRINT
APPROVAL_PERSON NOPRINT
APPROVAL_ORG NOPRINT
APPROVAL_ROLE NOPRINT
CREATED_DATE NOPRINT
AP_PER NOPRINT
WP_PER NOPRINT
BY ACTIVITY_ID NOPRINT

ON TABLE HOLD AS NEWTAB

MORE

FILE HEAD2

END

-RUN
TABLE FILE NEWTAB
PRINT
AP_PER NOPRINT
WP_PER NOPRINT
APPROVAL_ORG NOPRINT
ON TABLE
HEADING
"Approving Mgr Name:<+0> Approving Mgr Org:<+0> "WorkPlanner: ""

I ended up using the code below to join the HOLD files from SQL pass through. This worked great.

SET ALL=PASS
JOIN HEAD1.ACTIVITY_ID IN HEAD1 TO ALL HEAD2.ACTIVITY_ID IN HEAD2 AS JOIN1
END

KEY point: do a

TABLE FILE HEAD1
PRINT
col1
col2
....



Thanks for your assistance! You were a lot of help.


Thanks,

Kymber