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,

Could you put the output in code tags to keep the formatting ?

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
   

Hello dbeagan,

Thank you for your inputs, I realized I did not mention my output format as PDF.

Best Regards

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 

Thank you dbeagan for taking time to provide your inputs.

Best Regards

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
-----------------------------------------------------------------------------------------
 

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,$

Thank you dbeagan for your inputs.