[SOLVED] Display alternative columns in alternative rows for PDF Report
Hello Everyone,
I am trying to customize a report so that rows of specific columns will be displayed below the rows of specific columns. Please find the details below and provide if you have any inputs on this.
TABLE FILE wf_retail_tiny
SUM WF_RETAIL_TINY.WF_RETAIL_SALES.REVENUE_US
WF_RETAIL_TINY.WF_RETAIL_SALES.COGS_US
BY WF_RETAIL_TINY.WF_RETAIL_PRODUCT.PRODUCT_CATEGORY
BY WF_RETAIL_TINY.WF_RETAIL_PRODUCT.PRODUCT_SUBCATEG
BY WF_RETAIL_TINY.WF_RETAIL_PRODUCT.MODEL
BY WF_RETAIL_TINY.WF_RETAIL_GEOGRAPHY_CUSTOMER.BUSINESS_REGION
WHERE WF_RETAIL_TINY.WF_RETAIL_TIME_SALES.TIME_DATE GE '20161218';
WHERE WF_RETAIL_TINY.WF_RETAIL_TIME_SALES.TIME_DATE LE '20161231';
WHERE WF_RETAIL_TINY.WF_RETAIL_GEOGRAPHY_CUSTOMER.COUNTRY_NAME EQ 'United States';
WHERE WF_RETAIL_TINY.WF_RETAIL_PRODUCT.PRODUCT_SUBCATEG EQ 'Charger' OR 'Handheld' OR 'Smartphone' OR 'Blu Ray';
WHERE WF_RETAIL_TINY.WF_RETAIL_PRODUCT.MODEL EQ 'B00D7MOHDO' OR 'JVC GCFM2BUS' OR 'C6506B' OR 'JVC XV-BP1';
ON TABLE PCHOLD FORMAT PDF
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET AUTOFIT OFF
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
ENDSTYLE
END
-RUN
Current Output
Product Category Product Subcategory Model Customer Business Region Revenue Cost of Goods
Accessories Charger B00D7MOHDO North America $12,605.00 $6,049.00
Camcorder Handheld JVC GCFM2BUS North America $36,211.70 $17,220.00
Computers Smartphone C6506B North America $54,800.92 $48,723.00
Media Player Blu Ray JVC XV-BP1 North America $50,482.13 $35,910.00
Expected Output
Product Category Revenue Cost of Goods
Accessories $12,605.00 $6,049.00
Charger B00D7MOHDO North America
Camcorder $36,211.70 $17,220.00
Handheld JVC GCFM2BUS North America
Computers $54,800.92 $48,723.00
Smartphone C6506B North America
Media Player $50,482.13 $35,910.00
Blu Ray JVC XV-BP1 North America
This message has been edited. Last edited by: BI Dev,
Webfocus 8105/8202 Windows All Outputs
September 20, 2020, 06:33 PM
Waz
Could you put the output in code tags to keep the formatting ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
September 21, 2020, 09:27 AM
dbeagan
One approach would be to use OVER.
TABLE FILE wf_retail_tiny
SUM PRODUCT_CATEGORY AS ''
REVENUE_US AS ''
COGS_US AS '' OVER
PRODUCT_SUBCATEG AS ''
MODEL AS ''
BUSINESS_REGION AS ''
BY PRODUCT_CATEGORY NOPRINT
BY PRODUCT_SUBCATEG NOPRINT
BY MODEL NOPRINT
BY BUSINESS_REGION NOPRINT
WHERE TIME_DATE GE '20161218';
WHERE TIME_DATE LE '20161231';
WHERE COUNTRY_NAME EQ 'United States';
WHERE PRODUCT_SUBCATEG EQ 'Charger' OR
'Handheld' OR
'Smartphone' OR
'Blu Ray';
WHERE MODEL EQ 'B00D7MOHDO' OR
'JVC GCFM2BUS' OR
'C6506B' OR
'JVC XV-BP1';
ON TABLE SUBHEAD
"<+0>Product Category <+0>Revenue <+0>Cost of Goods"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET STYLE *
TYPE=REPORT, HEADALIGN=BODY,$
END
WebFOCUS 8.2.06
September 21, 2020, 12:38 PM
BI Dev
Hello dbeagan,
Thank you for your inputs, I realized I did not mention my output format as PDF.
Best Regards
Webfocus 8105/8202 Windows All Outputs
September 21, 2020, 04:35 PM
dbeagan
For PDF, add more code to the style sheet:
TABLE FILE ibisamp/wf_retail_tiny
SUM PRODUCT_CATEGORY AS ''
REVENUE_US AS ''
COGS_US AS '' OVER
PRODUCT_SUBCATEG AS ''
MODEL AS ''
BUSINESS_REGION AS ''
BY PRODUCT_CATEGORY NOPRINT
BY PRODUCT_SUBCATEG NOPRINT
BY MODEL NOPRINT
BY BUSINESS_REGION NOPRINT
WHERE TIME_DATE GE '20161218';
WHERE TIME_DATE LE '20161231';
WHERE COUNTRY_NAME EQ 'United States';
WHERE PRODUCT_SUBCATEG EQ 'Charger' OR
'Handheld' OR
'Smartphone' OR
'Blu Ray';
WHERE MODEL EQ 'B00D7MOHDO' OR
'JVC GCFM2BUS' OR
'C6506B' OR
'JVC XV-BP1';
ON TABLE SUBHEAD
"Product Category <+0>Revenue <+0>Cost of Goods"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET STYLE *
TYPE=REPORT, font=arial, SIZE=8, SQUEEZE=ON, JUSTIFY=LEFT, HEADALIGN=BODY,$
TYPE=REPORT, WRAP=1, COLUMN=PRODUCT_CATEGORY,$
TYPE=REPORT, WRAP=1, COLUMN=REVENUE_US,$
TYPE=REPORT, WRAP=1, COLUMN=COGS_US,$
TYPE=REPORT, WRAP=1, COLUMN=PRODUCT_SUBCATEG,$
TYPE=REPORT, WRAP=1, COLUMN=MODEL,$
TYPE=REPORT, WRAP=1, COLUMN=BUSINESS_REGION,$
TYPE=TABHEADING, STYLE=BOLD, COLOR=GRAY, BACKCOLOR=RGB(245 245 250),$
END
WebFOCUS 8.2.06
September 22, 2020, 08:48 AM
BI Dev
Thank you dbeagan for taking time to provide your inputs.
Best Regards
Webfocus 8105/8202 Windows All Outputs
September 24, 2020, 10:29 PM
BI Dev
Now, I was able to get the output like this below.
Product Category Revenue Cost of Goods
Accessories $12,605.00 $6,049.00
Charger B00D7MOHDO North America
Camcorder $36,211.70 $17,220.00
Handheld JVC GCFM2BUS North America
Computers $54,800.92 $48,723.00
Smartphone C6506B North America
Media Player $50,482.13 $35,910.00
Blu Ray JVC XV-BP1 North America
I am trying to customize the styling little more, like combining row 1 and row 2 as 1 block and row 3 and row 4 as another so on. I am also then trying to apply background color on it and separate these blocks with equal spaces. Please let me know if anybody has any inputs on this.
Expected output
Product Category Revenue Cost of Goods
---------------------------------------------------------------------------------------
Accessories $12,605.00 $6,049.00
Charger B00D7MOHDO North America
----------------------------------------------------------------------------------------
Camcorder $36,211.70 $17,220.00
Handheld JVC GCFM2BUS North America
----------------------------------------------------------------------------------------
Computers $54,800.92 $48,723.00
Smartphone C6506B North America
---------------------------------------------------------------------------------------
Media Player $50,482.13 $35,910.00
Blu Ray JVC XV-BP1 North America
-----------------------------------------------------------------------------------------