Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Dyanmic columns and column names
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Dyanmic columns and column names
 Login/Join
 
Silver Member
posted
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,


Year(s) of experience in WebFOCUS: 5+. Using WebFOCUS 7.7.03 on Windows platform with Oracle/SQL Server.
 
Posts: 41 | Registered: September 08, 2008Reply With QuoteReport This Post
Expert
posted Hide Post
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


Tom Flynn
WebFOCUS 8.1.05 - PROD/QA
DB2 - AS400 - Mainframe
 
Posts: 1958 | Location: Centennial, CO | Registered: January 31, 2006Reply With QuoteReport This Post
Silver Member
posted Hide Post
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.
 
Posts: 41 | Registered: September 08, 2008Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Dyanmic columns and column names

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.