Hello!

I'm developing a compound report in pdf that shows company, department, and sub-department totals in this order. For instance, under the dept 1 section, I have subdept 101, subdept 102, etc. The same is true for depts 2, 3, and 4. The current report uses WHERE clauses to create this structure; however, we are trying to avoid having to change the report every time a new department is created. Is there a way to create a loop based on the number of departments (saved in a & variable) that would dynamically add a new section to the compound pdf when a new dept is created? Thanks
Luiz

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

The answer is in your own posting, yes, there is "a way to create a loop based on the number of departments (saved in a & variable)" - here is example code (though I don't know how it's done in the GUI tools):

-SET &ECHO=ALL;

SET HOLDFORMAT = ALPHA
SET HOLDLIST = PRINTONLY
SET PAGE = NOLEAD
-RUN

TABLE FILE CAR
SUM COUNTRY
BY COUNTRY NOPRINT
ON TABLE HOLD AS CAR1
END
-RUN

-SET &COUNTRY_C = &LINES;

SET COMPOUND = OPEN
-RUN

TABLE FILE CAR
SUM COUNTRY
BY COUNTRY NOPRINT
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='Legal', SQUEEZE=ON, ORIENTATION=LANDSCAPE, $
TYPE=HEADING, SIZE=11, $
TYPE=REPORT, FONT='Arial', SIZE=9, COLOR=GREEN, $
END
-RUN

-REPEAT LOOP_END &COUNTRY_C TIMES;

-READ CAR1 NOCLOSE &COUNTRY.A10.

TABLE FILE CAR
SUM SALES
BY CAR
ACROSS MODEL
WHERE COUNTRY EQ '&COUNTRY'
HEADING
"Sales Report for <COUNTRY"
" "
ON TABLE PCHOLD FORMAT PDF NOBREAK
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='Legal', SQUEEZE=ON, ORIENTATION=LANDSCAPE, $
TYPE=HEADING, SIZE=11, COLOR=BLUE, $
TYPE=REPORT, FONT='Arial', SIZE=9, $
ENDSTYLE
END
-RUN

-LOOP_END

SET COMPOUND = CLOSE
-RUN

TABLE FILE CAR
PRINT CAR NOPRINT
WHERE RECORDLIMIT EQ 1
FOOTING
"END OF REPORT"
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET PAGE OFF
ON TABLE SET STYLE *
UNITS=IN, PAGESIZE='Legal', SQUEEZE=ON, ORIENTATION=LANDSCAPE, $
TYPE=FOOTING, SIZE=11, $
TYPE=REPORT, FONT='Arial', SIZE=9, COLOR=RED, $
ENDSTYLE
END
-RUN

Francis,
Thank you very much for your answer. I'll test that tonight and let you know if it works.
Luiz

Francis,

I've applied your suggestions to my code, and I'm almost there. I can get all the departments, but only the sub-department that are under the last one. Here's a sample of my code. Thanks

-*--------------------------------------------------------------------------------------
-* MTD - TOTAL REPORT
-*-------------------------------------------------------------------------------------
SET COMPOUND = OPEN

TABLE FILE MTDBYSD
HEADING
"Report Totals"
SUM
SALES_UNITS

BY PP_ID NOPRINT
BY PP_DESC AS 'Net $ Range'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
-INCLUDE STYLE.FEX
END
-RUN
-*----------------------------------------------------------------------------------------------------------------------
-* SAVE LIST OF DEPARTMENTS IN & VARIABLE (&DPTID_DESC)
-*----------------------------------------------------------------------------------------------------------------------
TABLE FILE MTDBYSD
SUM DPTID_DESC
BY DPTID_DESC NOPRINT
ON TABLE SAVE AS HOLDDPT
END
-RUN
-*--------------------------------------------------------------------------------------------
-* COUNT THE NUMBER OF DEPARTMENTS (&DPTDESC_C)
-*-------------------------------------------------------------------------------------------
-SET &DPTDESC_C = &LINES ;
-RUN
-REPEAT LOOP_END &DPTDESC_C TIMES ;
-RUN
-READ HOLDDPT NOCLOSE &DPTID_DESC.A40.
-RUN

-*-----------------------------------------------------------------------------------------------
-* MTD - DEPARTMENT
-*-----------------------------------------------------------------------------------------------

-RUN
TABLE FILE MTDBYSD
HEADING
"Department: SUM
SALES_UNITS
BY DPTID_DESC NOPRINT SUMMARIZE 'SALES_UNITS' AS 'Total'
BY PP_ID NOPRINT
BY PP_DESC AS 'Net $ Range'
WHERE DPTID_DESC EQ '&DPTID_DESC';
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
-INCLUDE STYLE.FEX
END
-RUN

-LOOP_END

-*-------------------------------------------------------------------------------------------------
-* DEPARTMENT AND SUBDEPARTMENTS
-*-------------------------------------------------------------------------------------------------
SET COMPOUND = CLOSE

TABLE FILE MTDBYSD
HEADING
"Sub-Department Totals"
SUM
SALES_UNITS

BY SUBDPTID_DESC NOPRINT SUMMARIZE 'SALES_UNITS' AS 'Total:'
BY PP_ID NOPRINT
BY PP_DESC AS 'Net $,Range'
WHERE DPTID_DESC EQ '&DPTID_DESC'
ON TABLE NOTOTAL
ON SUBDPTID_DESC SUBHEAD
""
"""
ON TABLE PCHOLD FORMAT PDF
-INCLUDE STYLE.FEX
END
-EXIT

You must move the Sub-Department report within the loop. To close the compound report, my example adds an "END OF REPORT" footing - yours could be a blank line.

Francis,

Do you have any suggestions on how I can nest the sub-department loop within the department? Thanks

L

Why would you need to "nest the sub-department loop within the department"?

Your code suggests all sub-departments within departments will be on the report - no need for a loop.

BY SUBDPTID_DESC NOPRINT SUMMARIZE
ON SUBDPTID_DESC SUBHEAD
WHERE DPTID_DESC EQ '&DPTID_DESC'

Wouldn't this give you each sub-department within a department?

TABLE FILE MTDBYSD
HEADING
"Sub-Department Totals"
SUM
SALES_UNITS

BY SUBDPTID_DESC NOPRINT SUMMARIZE 'SALES_UNITS'   AS 'Total:'
BY PP_ID NOPRINT
BY PP_DESC AS 'Net $,Range'
WHERE DPTID_DESC EQ '&DPTID_DESC'
ON TABLE NOTOTAL
ON SUBDPTID_DESC SUBHEAD
""
"<SUBDPTID_DESC"
""
ON TABLE PCHOLD FORMAT PDF
-INCLUDE STYLE.FEX
END

Francis,

I need a separate section for department and, before the next department, I need to show the sub-departments under the previous department. As it is, I'm getting all 4 departments and only sub-departments under department 4 (which is the last one). Here's what I'm looking for. Thank you very much.

SET COMPOUND = OPEN

REP1-TOTAL COMPANY
-RUN

(WITHIN THE LOOP)
REPT2-DEPT01
-RUN
REPT2.1- DEPT01/SUBDEPTs (101,102,103,104,etc)
-RUN
REPT3 - DEPT02
-RUN
REPT3.1- DEPT02/SUBDEPTS (201,202, 203, etc)
-RUN
.
.
.
(Where should my SET COMPOUND = CLOSE be for my last report combination(dept/subdept)?)
REPT6 - DEPT4
-RUN
REPT3.1- DEPT04/SUBDEPTS (401,402, 403, etc) => MY LAST REPORT

-END_LOOP

Something like this should do it:

-*------------------------------------------------------------------------------
-* MTD - TOTAL REPORT
-*------------------------------------------------------------------------------
SET COMPOUND = OPEN

TABLE FILE MTDBYSD
HEADING
"Report Totals"
SUM
SALES_UNITS

BY PP_ID NOPRINT
BY PP_DESC AS 'Net $ Range'
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
-INCLUDE STYLE.FEX
END
-RUN

-*------------------------------------------------------------------------------
-* SAVE LIST OF DEPARTMENTS IN & VARIABLE (&DPTID_DESC)
-*------------------------------------------------------------------------------
TABLE FILE MTDBYSD
SUM DPTID_DESC
BY DPTID_DESC NOPRINT
ON TABLE SAVE AS HOLDDPT
END
-RUN

-*------------------------------------------------------------------------------
-* COUNT THE NUMBER OF DEPARTMENTS (&DPTDESC_C)
-*------------------------------------------------------------------------------
-SET &DPTDESC_C = &LINES ;

-REPEAT LOOP_END &DPTDESC_C TIMES ;

-READ HOLDDPT NOCLOSE &DPTID_DESC.A40.

-*------------------------------------------------------------------------------
-* MTD - DEPARTMENT
-*------------------------------------------------------------------------------

-RUN
TABLE FILE MTDBYSD
HEADING
"Department: <DPTID_DESC"
SUM
SALES_UNITS
BY DPTID_DESC NOPRINT SUMMARIZE 'SALES_UNITS'   AS 'Total'
BY PP_ID NOPRINT
BY PP_DESC AS 'Net $ Range'
WHERE DPTID_DESC EQ '&DPTID_DESC';
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
-INCLUDE STYLE.FEX
END
-RUN

-*------------------------------------------------------------------------------
-* DEPARTMENT AND SUBDEPARTMENTS
-*------------------------------------------------------------------------------
TABLE FILE MTDBYSD
HEADING
"Sub-Department Totals"
SUM
SALES_UNITS

BY SUBDPTID_DESC NOPRINT SUMMARIZE 'SALES_UNITS'   AS 'Total:'
BY PP_ID NOPRINT
BY PP_DESC AS 'Net $,Range'
WHERE DPTID_DESC EQ '&DPTID_DESC'
ON TABLE NOTOTAL
ON SUBDPTID_DESC SUBHEAD
""
"<SUBDPTID_DESC"
""
ON TABLE PCHOLD FORMAT PDF
-INCLUDE STYLE.FEX
END
-RUN

-LOOP_END

SET COMPOUND = CLOSE

TABLE FILE MTDBYSD
SUM PP_ID NOPRINT
FOOTING
"END OF REPORT"
ON TABLE PCHOLD FORMAT PDF
-INCLUDE STYLE.FEX
END
-RUN

Francis,
I was able to tweak the code, and now I'm getting all the depts/subdepts to show on the reports. Thanks

This message has been edited. Last edited by: Luiz De Assis,