Hi,

I'm writing a program to retrieve a list of product categories dynamically, and based on that I would like to show the units sold in the various product categories grouped by the date. I'm able to do this with:

TABLE FILE SALES_TABLE
BY PRODUCT_CATEGORY
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
END

DEFINE FILE SALES_TABLE
-REPEAT #PRODCAT FOR &I FROM 1 TO &MAXPRODCAT;
-READ HOLD NOCLOSE &PRODCAT.255.
PRODCAT&I/P21.4 = IF PRODUCT_CATEGORY EQ '&PRODCAT' THEN UNITS_SOLD ELSE 0;
-#PRODCAT
TOTAL_PRICE/D12.2=UNIT_PRICE * UNITS_SOLD;
END

TABLE FILE SALES_TABLE
SUM
-REPEAT #COLS1 FOR &I FROM 1 TO &MAXPRODCAT;
     PRODCAT&I
-#COLS1
     UNITS_SOLD AS 'Total Units Sold'
     TOTAL_PRICE AS 'Total Price'
BY   DATE_COLUMN
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

However, the column headings are showing PRODCAT1, PRODCAT2, etc. Is there a way to show the corresponding product category as the column heading name? I do not know how many product categories there are and thus it has to be dynamic.

Thanks for your suggestions!

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

Try this:

TABLE FILE SALES_TABLE
BY PRODUCT_CATEGORY
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
END
-RUN
-SET &XLINES = &LINES;

DEFINE FILE SALES_TABLE
-REPEAT #PRODCAT &XLINES TIMES
-READ HOLD NOCLOSE &PRODCAT.255.
PRODCAT&I/P21.4 = IF PRODUCT_CATEGORY EQ '&PRODCAT' THEN UNITS_SOLD ELSE 0;
-SET &PRODDESC&I = ' AS ' | '''' || '&PRODCAT.EVAL' || '''';
-#PRODCAT
TOTAL_PRICE/D12.2=UNIT_PRICE * UNITS_SOLD;
END

TABLE FILE SALES_TABLE
SUM
-REPEAT #COLS1 &XLINES TIMES
     PRODCAT&I &PRODDESC&I
-#COLS1
     UNITS_SOLD AS 'Total Units Sold'
     TOTAL_PRICE AS 'Total Price'
BY   DATE_COLUMN
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END
-EXIT

hth

It seems like posting the question enabled me to solve the issue. The code is as follows:

TABLE FILE SALES_TABLE
BY PRODUCT_CATEGORY
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE HOLD FORMAT ALPHA
END

DEFINE FILE SALES_TABLE
-REPEAT #PRODCAT FOR &I FROM 1 TO &MAXPRODCAT;
-READ HOLD NOCLOSE &PRODCAT.255.
PRODCAT&I/P21.4 = IF PRODUCT_CATEGORY EQ '&PRODCAT' THEN UNITS_SOLD ELSE 0;
-SET &PCDESC&I.EVAL=&PRODCAT;
-#PRODCAT
TOTAL_PRICE/D12.2=UNIT_PRICE * UNITS_SOLD;
END

TABLE FILE SALES_TABLE
SUM
-REPEAT #COLS1 FOR &I FROM 1 TO &MAXPRODCAT;
     PRODCAT&I AS '&PCDESC&I.EVAL'
-#COLS1
     UNITS_SOLD AS 'Total Units Sold'
     TOTAL_PRICE AS 'Total Price'
BY   DATE_COLUMN
ON TABLE SET PAGE-NUM OFF 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END

Is there another way to do the above and allow the code to be opened in the GUI?

Have you considered using ACROSS:

TABLE FILE SALES_TABLE
 SUM UNITS_SOLD AS 'Total Units Sold'
 BY DATE_COLUMN
 ACROSS PRODUCT_CATEGORY AS ''
 ON TABLE SET PAGE-NUM OFF
 ON TABLE ROW-TOTAL
 ON TABLE NOTOTAL
 ON TABLE PCHOLD FORMAT HTML
END

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