Hello, I have multiple TABLE requests and would like them all display in EXCEL. Right now, only the first TABLE request is displaying in EXCEL, what am I doing wrong?


TABLE FILE 201720_CENSUS_CAREER
HEADING CENTER
"FSU Census Headcount Validation - IPEDS"
"Snapshot Term Code: COUNT STUDENT_CID AS 'TALLY'
BY CAREER AS 'CAREER'
BY MAJOR_1_DEGREE_LEVEL AS 'DEGREE LEVEL'
BY CLASS_LEVEL AS 'CLASS LEVEL'
WHERE EXCLUDE_FROM_IPEDS_FLAG EQ 'N' AND PRIMARY_CAREER_FLAG EQ 'Y'
ON CAREER SUBTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END


DEFINE FILE 201720_CENSUS_CAREER
CLASSIFICATION/A20 = IF ((CAREER EQ 'Undergraduate') AND (CLASS_LEVEL EQ 'First Year Undergraduate') AND (ENROLLMENT_STATUS_CODE EQ '1')) THEN 'First-time Freshmen' ELSE
IF ((CAREER EQ 'Undergraduate') AND (CLASS_LEVEL EQ 'First Year Undergraduate') AND (ENROLLMENT_STATUS_CODE EQ '2' OR '3' OR '4' OR '5' OR '6' )) THEN 'Other Freshmen' ELSE ' ';
END


TABLE FILE 201720_CENSUS_CAREER
HEADING CENTER
"FSU Census Headcount Validation - IPEDS"
"Snapshot Term Code: COUNT STUDENT_CID AS 'TALLY'
BY CAREER AS 'CAREER'
BY CLASSIFICATION
BY CLASS_LEVEL AS 'CLASS LEVEL'
WHERE EXCLUDE_FROM_IPEDS_FLAG EQ 'N' AND PRIMARY_CAREER_FLAG EQ 'Y' AND CLASSIFICATION NE ' '
ON CAREER SUBTOTAL
ON TABLE PCHOLD FORMAT EXL2K
END
-EXIT

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

What's going on is that the first ON TABLE PCHOLD already sent the output to the client, so the second one came too late.

You need to combine both Excel sheets on the server side and then send it to the client.

I haven't done this with EXL2K, but you probably need to create a COMPOUND EXL2K document. Aside from the documentation, there are posts on these forums that explain how.

The other option applies if you can create both outputs in a single TABLE FILE request with a common sort field. You can create a BYTOC report, that uses the first BY field to split the Excel output across multiple sheets within the worksheet.

Have you tried OPEN NOBREAK CLOSE?

 TABLE FILE CAR
SUM SALES
BY CAR
HEADING
"Sales by  CAR"
ON TABLE PCHOLD FORMAT EXL2K OPEN NOBREAK
END


TABLE FILE CAR
SUM SALES
BY MODEL
HEADING
""
"Model by  CAR"
ON TABLE PCHOLD FORMAT EXL2K CLOSE
END 

If you are interested in manually coding the merging of the 2 Excel documents then as Rifaz mentions - use NOBREAK.

Here is a link to the documentation related to this syntax:
Creating a Compound Excel Report

Let us know if the advice provided helped resolve your compound question.

We do ask that you mark the subject line with the appropriate tag words found in Forum Guidelines

I will mark it [SOLVED] at this point.

Thank you for participating in the Focal Point Forum!
Tamra Colangelo
Focal Point Moderator
Information Builders