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,
Year(s) of experience in WebFOCUS: 5+. Using WebFOCUS 7.7.03 on Windows platform with Oracle/SQL Server.
January 10, 2011, 11:35 AM
Tom Flynn
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
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?
Year(s) of experience in WebFOCUS: 5+. Using WebFOCUS 7.7.03 on Windows platform with Oracle/SQL Server.
January 10, 2011, 08:02 PM
Dan Satchell
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,