Focal Point
[SOLVED] Display alternative columns in alternative rows for PDF Report

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/7407034496

September 20, 2020, 01:51 PM
BI Dev
[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.04Upgrade:WebFOCUS 8.2.07OS:LinuxOutputs: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
-----------------------------------------------------------------------------------------
 



Webfocus 8105/8202
Windows
All Outputs
September 25, 2020, 02:22 PM
dbeagan
You can add borders in the style sheet:
TYPE=DATA, BORDER-TOP=LIGHT, COLUMN=PRODUCT_CATEGORY,$
TYPE=DATA, BORDER-TOP=LIGHT, COLUMN=REVENUE_US,$
TYPE=DATA, BORDER-TOP=LIGHT, COLUMN=COGS_US,$



WebFOCUS 8.2.06
September 29, 2020, 09:26 PM
BI Dev
Thank you dbeagan for your inputs.


Webfocus 8105/8202
Windows
All Outputs