Hi Everyone,

I am trying to create a report where the sort field "Sale,Date" which will be displayed in rows, displayed as a column.i.e. every date from 2017/12/21 to 2017/12/31 should be a column by itself and the sales amount should be displayed below that column for that product category. Please provide your inputs on this. My code is below the sample output.

Output:


Category Day-1 Day-2 Day-3 Day-4 Day-5 Day-6 Day-7 Day-8 Day-9 Day-10
============================================================================================================================================================================
Product Category 2017/12/21 2017/12/22 2017/12/23 2017/12/24 2017/12/25 2017/12/26 2017/12/27 2017/12/28 2017/12/29 2017/12/30
Accessories 97,437.41 109,396.85 94,362.15 96,498.17 106,360.90 99,167.13 110,453.19 115,469.72 105,335.96 100,250.92
Camcorder 108,666.71 114,820.06 122,896.41 120,109.49 128,110.02 114,679.75 143,665.80 128,111.64 103,544.92 101,506.84

 
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';
TABLE FILE wf_retail_lite
SUM WF_RETAIL_LITE.WF_RETAIL_SALES.REVENUE_US
BY WF_RETAIL_LITE.WF_RETAIL_PRODUCT.PRODUCT_CATEGORY
BY WF_RETAIL_LITE.WF_RETAIL_TIME_SALES.TIME_DATE
WHERE WF_RETAIL_LITE.WF_RETAIL_TIME_SALES.TIME_DATE GE '20171221';
WHERE WF_RETAIL_LITE.WF_RETAIL_GEOGRAPHY_STORE.COUNTRY_NAME EQ 'United States';
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET STYLE *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, TITLETEXT=&WF_TITLE.QUOTEDSTRING, SUMMARY=&WF_SUMMARY.QUOTEDSTRING, $
ENDSTYLE
END

-RUN


 

This message has been edited. Last edited by: FP Mod Chuck,

Um, er, change BY to ACROSS ?

Hi Waz,

I hope you are doing well. ACROSS will put the dates above the product category column, I need to display the dates beside the product category column. For ACROSS I need to specify a verb object, I am trying to create the report which might not have any measures.Is there a workaround to accomplish that?

Regards
BI DEV

This message has been edited. Last edited by: BI Dev,

I think you will need to do a lot of DEFINEs and/or COMPUTEs and HOLDs to create the data that is missing.

If I understand what you are saying you need 10 days showing as columns, and the colums will have different data, like numbers and dates.

Yes, you are right. That is what I am trying to do.

Then you will need to create 10 new columns and set the values for the respective rows. You will probably need to use dynamic formatting (Field/FormatField).

Another option may be to move the dates to the heading and align them with the columns or set them in the AS for the 10 columns.

Many possibilities, but you know what the inputs and outputs are to be.

BI,
Maybe use an alternate master or a dialog manager loop.
Could you post your data or the output of your program? I'll give it a try.

HI Danny,

Thank you very much, Just to give little overview of what I am trying to do, I am trying to create a report which will display the below with 1st column category, 2nd column average of sales for the last 8 days for the category, 3rd column sales of the current day, 4-10 column dates with their respective value. Please provide your inputs to create this.

 
Product Category Ave_of_last_8_days Today_Sales 2020/07/22	2020/07/21	2020/07/20	2020/07/19	2020/07/18	2020/07/17	2020/07/16	2020/07/15	


											
Accessories	 $97,437.41	$109,396.85	$94,362.15	$96,498.17	$106,360.90	$99,167.13	$110,453.19	$115,469.72	$105,335.96	$100,250.92	
Camcorder	$108,666.71	$114,820.06	$122,896.41	$120,109.49	$128,110.02	$114,679.75	$143,665.80	$128,111.64	$103,544.92	$101,506.84	
Computers	$123,934.51	$107,422.64	$130,106.65	$99,455.70	$117,270.45	$115,989.31	$124,525.25	$134,223.01	$128,089.10	$124,026.73	
Media Player    $188,698.98	$200,998.73	$194,083.48	$197,899.80	$189,501.94	$198,831.06	$179,852.65	$193,215.46	$213,440.46	$201,099.10	
Stereo Systems	$223,750.11	$225,912.42	$234,200.91	$246,688.65	$280,797.42	$241,041.37	$232,148.68	$226,435.88	$225,842.41	$239,194.94	
Televisions	$66,678.09	$56,759.42	$58,385.25	$59,555.37	$49,400.98	$68,829.14	$66,650.61	$54,159.46	$89,519.83	$58,711.70	
Video Pro	$41,804.77	$44,678.84	$52,672.94	$47,146.64	$49,815.76	$53,382.56	$44,935.07	$52,836.48	$45,362.03	$41,220.45	


 

Regards
BI DEV

This message has been edited. Last edited by: BI Dev,

Your requirements, if I understand them correctly:
1. Display average revenue for the last 8 days by Product Category.
2. Display revenue across the last 8 days by Product Category across date.

TABLE FILE wf_retail_lite
-*
SUM 
COMPUTE 
    AVG_8DAYS/D12.2CM = REVENUE_US / 8; AS '8 Day Avg,Revenue'  
BY  PRODUCT_CATEGORY
-*
SUM    REVENUE_US    
BY     PRODUCT_CATEGORY
ACROSS TIME_DATE AS 'Daily Revenue:' 
-*
WHERE TIME_DATE GT '20161024';
-*
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
  INCLUDE=qqdflt.sty, $
ENDSTYLE
END  

This message has been edited. Last edited by: David Briars,

Hi David,

Thanks for your response. I am trying to display the dates(last 8 days from current date) beside the Product category, Average sales and Today's sales like below. Sorry, the formatting got messed up in the previous post. If we divide REVENUE_US by 8, it works fine, But, if there is no value for a particular product category on a certain date then this might not give an exact average value. If I use ACROSS, It will display the dates above not beside.

 
Product Category Ave_of_last_8_days Today_Sales 2020/07/22	2020/07/21	2020/07/20	2020/07/19	2020/07/18	2020/07/17	2020/07/16	2020/07/15	


											
Accessories	 $97,437.41	$109,396.85	$94,362.15	$96,498.17	$106,360.90	$99,167.13	$110,453.19	$115,469.72	$105,335.96	$100,250.92	
Camcorder	$108,666.71	$114,820.06	$122,896.41	$120,109.49	$128,110.02	$114,679.75	$143,665.80	$128,111.64	$103,544.92	$101,506.84	
Computers	$123,934.51	$107,422.64	$130,106.65	$99,455.70	$117,270.45	$115,989.31	$124,525.25	$134,223.01	$128,089.10	$124,026.73	
Media Player    $188,698.98	$200,998.73	$194,083.48	$197,899.80	$189,501.94	$198,831.06	$179,852.65	$193,215.46	$213,440.46	$201,099.10	
Stereo Systems	$223,750.11	$225,912.42	$234,200.91	$246,688.65	$280,797.42	$241,041.37	$232,148.68	$226,435.88	$225,842.41	$239,194.94	
Televisions	$66,678.09	$56,759.42	$58,385.25	$59,555.37	$49,400.98	$68,829.14	$66,650.61	$54,159.46	$89,519.83	$58,711.70	
Video Pro	$41,804.77	$44,678.84	$52,672.94	$47,146.64	$49,815.76	$53,382.56	$44,935.07	$52,836.48	$45,362.03	$41,220.45	


 

Looking at your first post:

quote:

Product Category 2017/12/21 2017/12/22 2017/12/23 2017/12/24 2017/12/25 2017/12/26 2017/12/27 2017/12/28 2017/12/29 2017/12/30
Accessories 97,437.41 109,396.85 94,362.15 96,498.17 106,360.90 99,167.13 110,453.19 115,469.72 105,335.96 100,250.92
Camcorder 108,666.71 114,820.06 122,896.41 120,109.49 128,110.02 114,679.75 143,665.80 128,111.64 103,544.92 101,506.84

but using the data in your subsequent post

quote:

   
Accessories	 $97,437.41	$109,396.85	$94,362.15	$96,498.17	$106,360.90	$99,167.13	$110,453.19	$115,469.72	$105,335.96	$100,250.92	
Camcorder	$108,666.71	$114,820.06	$122,896.41	$120,109.49	$128,110.02	$114,679.75	$143,665.80	$128,111.64	$103,544.92	$101,506.84	
Computers	$123,934.51	$107,422.64	$130,106.65	$99,455.70	$117,270.45	$115,989.31	$124,525.25	$134,223.01	$128,089.10	$124,026.73	
Media Player    $188,698.98	$200,998.73	$194,083.48	$197,899.80	$189,501.94	$198,831.06	$179,852.65	$193,215.46	$213,440.46	$201,099.10	
Stereo Systems	$223,750.11	$225,912.42	$234,200.91	$246,688.65	$280,797.42	$241,041.37	$232,148.68	$226,435.88	$225,842.41	$239,194.94	
Televisions	$66,678.09	$56,759.42	$58,385.25	$59,555.37	$49,400.98	$68,829.14	$66,650.61	$54,159.46	$89,519.83	$58,711.70	
Video Pro	$41,804.77	$44,678.84	$52,672.94	$47,146.64	$49,815.76	$53,382.56	$44,935.07	$52,836.48	$45,362.03	$41,220.45	

and taking into account that you also want an "average" column, I thought of the following:

  
-SET &ECHO=ALL;
-* File bidev.fex
-* Creating data from your post
FILEDEF BIDEV DISK FOCALPOINT/BIDEV.TXT
DEFINE FILE BIDEV
-REPEAT #CVT FOR &I FROM 1 TO 11;
-SET &J=IF &I LT 10 THEN '0' | &I ELSE &I;
BSALES&J/D12.2=ATODBL(STRIP(12, STRIP(12, ASALES&J, '$', 'A12'), ',', 'A12'), '12', 'D12.2');
-#CVT
SDATE/YYMD='2020/07/01';
END
-RUN
TABLE FILE BIDEV
PRINT 
-REPEAT #DATA FOR &I FROM 4 TO 11;
-SET &J=IF &I LT 10 THEN '0' | &I ELSE &I;
COMPUTE DATE&J/DMYY=SDATE + &I;
BSALES&J
-#DATA
BY PROD
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS WF_RETAIL FORMAT ALPHA
END
-RUN
-* The WF_Retail file has for each Product 8 pairs of fields date/sales
-* Using the Altrnate Master schema one get for each product a repeating segment of pairs 
-* WF also allows numbering (the NUM field below)
EX -LINES 8 EDAPUT MASTER,WF_RETAIL,C,MEM 
FILENAME=WF_RETAIL    , SUFFIX=FIX
 SEGMENT=WF_RETAIL, SEGTYPE=S0
  FIELDNAME=PROD, ALIAS=E01, USAGE=A16, ACTUAL=A16, $
 SEGMENT=SALES, PARENT=WF_RETAIL, OCCURS=VARIABLE
  FIELDNAME=SDATE, ALIAS=E02, USAGE=DMYY, ACTUAL=A08, $
  FIELDNAME=BSALES, ALIAS=E03, USAGE=D12.2, ACTUAL=A12, $
  FIELDNAME=NUM, ALIAS=ORDER, USAGE=I2, ACTUAL=I4, $
-RUN
-* Redefinining dates and sales as alpha fields to have them aligned in columns
-* Defining additional fields for the values of "Product Category" and "Average Sales"
-* Adding sort fields S and T to place APROD before the PRODucts
DEFINE FILE WF_RETAIL
TDATE/DMYY=SDATE;
QDATE/A8DMYY=TDATE;
ADATE/A16=EDIT(QDATE, '99/99/9999');
ASALES/A16=FPRINT(BSALES, 'D12.2M', 'A16');
NUM/I2=NUM + 2;
APROD/A16='Product Category';
AVNAME/A16 WITH BSALES='Average Sales';
S/I1=1;
T/I1=0;
AV/I2=2;
END
-RUN
-* Extract the SALES and hold in BIDATA file
TABLE FILE WF_RETAIL
PRINT 
ASALES AS ADATA
BY S
BY PROD
BY NUM
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS BIDATA FORMAT ALPHA
END
-RUN
-* Redefine the BIDATA file in order to add records to it with APPEND
FILEDEF BIDATA DISK BIDATA.FTM (APPEND
-RUN
-* Append the average sales
TABLE FILE WF_RETAIL
SUM AVE.BSALES NOPRINT
COMPUTE AVSALES/A16=FPRINT(AVE.BSALES, 'D12.2M', 'A16');
BY S 
BY PROD
BY AV
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS BIDATA
END
-RUN
-* Append the dates but place them on the first line
TABLE FILE WF_RETAIL
SUM 
ADATE
BY T
BY APROD
BY NUM
ON TABLE SAVE AS BIDATA 
END
-RUN
-* Append the "Average Sales" "title" with the dates
TABLE FILE WF_RETAIL
SUM 
AVNAME
BY T
BY APROD
BY AV
ON TABLE SAVE AS BIDATA 
END
-RUN
-* Produce the report
TABLE FILE BIDATA
SUM ADATA
BY S NOPRINT
BY PROD AS ''
ACROSS NUM NOPRINT
END

This message has been edited. Last edited by: Danny-SRL,

I forgot to post the MASTER of the BIDEV.txt file:

  
FILENAME=BIDEV, SUFFIX=TAB
 SEGNAME=BIDEV, SEGTYPE=S0
  FIELDNAME=PROD, ALIAS=PROD, USAGE=A16, ACTUAL=A16, $
  FIELDNAME=ASALES01, ALIAS=ASALES01, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES02, ALIAS=ASALES02, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES03, ALIAS=ASALES03, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES04, ALIAS=ASALES04, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES05, ALIAS=ASALES05, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES06, ALIAS=ASALES06, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES07, ALIAS=ASALES07, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES08, ALIAS=ASALES08, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES09, ALIAS=ASALES09, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES10, ALIAS=ASALES10, USAGE=A12, ACTUAL=A12, $
  FIELDNAME=ASALES11, ALIAS=ASALES11, USAGE=A12, ACTUAL=A12, $

Hello Daniel,

Thank you very much for taking time to help me on this. I tried a different way to accomplish the required output. Having said that, I will try the way you mentioned as well. This is how I did it.

  
SET ASNAMES=ON
ENGINE INT CACHE SET ON
SET PAGE-NUM=NOLEAD
SET SQUEEZE=ON
-DEFAULTH &WF_HTMLENCODE=ON;
SET HTMLENCODE=&WF_HTMLENCODE

SET HTMLCSS=ON
-DEFAULTH &WF_EMPTYREPORT=ON;
SET EMPTYREPORT=&WF_EMPTYREPORT

-DEFAULTH &WF_SUMMARY='Summary';
-DEFAULTH &WF_TITLE='WebFOCUS Report';

TABLE FILE wf_retail_lite
SUM
REVENUE_US
BY PRODUCT_CATEGORY
BY TIME_DATE
-*-*WHERE TIME_DATE GE LAST_15_DAYS;
-*-*WHERE TIME_DATE LT TODAY;
ON TABLE HOLD AS MAIN_HOLD
END

TABLE FILE MAIN_HOLD
SUM
AVE.REVENUE_US
BY PRODUCT_CATEGORY
ON TABLE HOLD AS AVE_HOLD
END

TABLE FILE MAIN_HOLD
SUM
REVENUE_US
BY PRODUCT_CATEGORY
WHERE TIME_DATE EQ '2020-02-04'
ON TABLE HOLD AS SUM_HOLD
END

TABLE FILE MAIN_HOLD
SUM
REVENUE_US
BY PRODUCT_CATEGORY
WHERE TIME_DATE EQ '2020-02-04'
ON TABLE HOLD AS DAY1
END

TABLE FILE MAIN_HOLD
SUM
REVENUE_US
BY PRODUCT_CATEGORY
WHERE TIME_DATE EQ '2020-02-05'
ON TABLE HOLD AS DAY2
END

TABLE FILE MAIN_HOLD
SUM
REVENUE_US
BY PRODUCT_CATEGORY
WHERE TIME_DATE EQ '2020-02-06'
ON TABLE HOLD AS DAY3
END

TABLE FILE MAIN_HOLD
SUM
REVENUE_US
BY PRODUCT_CATEGORY
WHERE TIME_DATE EQ '2020-02-07'
ON TABLE HOLD AS DAY4
END

TABLE FILE MAIN_HOLD
SUM
REVENUE_US
BY PRODUCT_CATEGORY
WHERE TIME_DATE EQ '2020-02-10'
ON TABLE HOLD AS DAY5
END

TABLE FILE MAIN_HOLD
SUM
REVENUE_US
BY PRODUCT_CATEGORY
WHERE TIME_DATE EQ '2020-02-11'
ON TABLE HOLD AS DAY6
END

TABLE FILE MAIN_HOLD
SUM
REVENUE_US
BY PRODUCT_CATEGORY
WHERE TIME_DATE EQ '2020-02-13'
ON TABLE HOLD AS DAY7
END

TABLE FILE MAIN_HOLD
SUM
REVENUE_US
BY PRODUCT_CATEGORY
WHERE TIME_DATE EQ '2020-04-13'
ON TABLE HOLD AS DAY8
END
JOIN LEFT_OUTER PRODUCT_CATEGORY IN DAY1 TO PRODUCT_CATEGORY IN AVE_HOLD TAG AV AS AV
JOIN LEFT_OUTER PRODUCT_CATEGORY IN DAY1 TO PRODUCT_CATEGORY IN SUM_HOLD TAG SU AS SU
JOIN LEFT_OUTER PRODUCT_CATEGORY IN DAY1 TO PRODUCT_CATEGORY IN DAY2 TAG J1 AS J1
JOIN LEFT_OUTER PRODUCT_CATEGORY IN DAY1 TO PRODUCT_CATEGORY IN DAY3 TAG J2 AS J2
JOIN LEFT_OUTER PRODUCT_CATEGORY IN DAY1 TO PRODUCT_CATEGORY IN DAY4 TAG J3 AS J3
JOIN LEFT_OUTER PRODUCT_CATEGORY IN DAY1 TO PRODUCT_CATEGORY IN DAY5 TAG J4 AS J4
JOIN LEFT_OUTER PRODUCT_CATEGORY IN DAY1 TO PRODUCT_CATEGORY IN DAY6 TAG J5 AS J5
JOIN LEFT_OUTER PRODUCT_CATEGORY IN DAY1 TO PRODUCT_CATEGORY IN DAY7 TAG J6 AS J6
JOIN LEFT_OUTER PRODUCT_CATEGORY IN DAY1 TO PRODUCT_CATEGORY IN DAY8 TAG J7 AS J7

TABLE FILE DAY1
PRINT
AV.REVENUE_US AS 'Saturation Limit'
SU.REVENUE_US AS 'Revenue'
REVENUE_US AS '1 st Day'
J1.REVENUE_US AS '2 nd Day'
J2.REVENUE_US AS '3 rd Day'
J3.REVENUE_US AS '4 th Day '
J4.REVENUE_US AS '5 th Day'
J5.REVENUE_US AS '6 th Day'
J6.REVENUE_US AS '7 th Day'
J7.REVENUE_US AS '8 th Day'
BY PRODUCT_CATEGORY AS 'Product Name'
ON TABLE PCHOLD FORMAT HTML
ON TABLE NOTOTAL
ON TABLE SET CACHELINES 100
ON TABLE SET GRWIDTH 1
ON TABLE SET STYLESHEET *
INCLUDE=IBFS:/FILE/IBI_HTML_DIR/ibi_themes/Warm.sty,$
TYPE=REPORT, $
TYPE=DATA, COLUMN=SU.REVENUE_US, BACKCOLOR=GREEN, STYLE=BOLD, WHEN=SU.REVENUE_US GT AV.REVENUE_US , $
TYPE=DATA, COLUMN=SU.REVENUE_US, BACKCOLOR=RED, STYLE=BOLD, WHEN=SU.REVENUE_US LT AV.REVENUE_US , $
ENDSTYLE
END
-RUN

Your requirements, updated, if I understand them correctly:
1. Display average revenue by Product Category. Average only dates that have data.
2. Display revenue across the last 8 days by Product Category across date. WebFOCUS ACROSS display functionality is 'off the table', as it prints its across column headings (dates in this case) above the table column headings.

-*
-* Environmental Settings.  
-*
SET NODATA = ' '
SET ASNAMES = ON
-*
-* Pull data from SQL Server and summarize/sort.  
-*
DEFINE FILE wf_retail_lite
 TIME_DATE_MDYY/MDYY = TIME_DATE;
 TIME_DATE_ALPHA/A10 = FPRINT(TIME_DATE_MDYY, 'MDYY', 'A10');
END 
-*
TABLE FILE wf_retail_lite
-*
SUM     REVENUE_US AS 'TOTALREVENUE'
BY      PRODUCT_CATEGORY
-*
SUM     REVENUE_US AS ''
COMPUTE DAYCOUNTER/I9 = IF REVENUE_US IS MISSING THEN 0 ELSE 1;      
BY      PRODUCT_CATEGORY 
ACROSS  HIGHEST TIME_DATE_ALPHA AS 'Daily Revenue:'
-*
WHERE TIME_DATE GT '20161024';
-*
ON TABLE HOLD AS HLDDATA
END
-RUN
-*
-* Presentation to user.  
-*
DEFINE FILE HLDDATA
 NUMBEROFDAYS/I9 = E04 + E06 + E08 + E10 + E12 + E14 + E16 + E18;
END
-*
TABLE FILE HLDDATA
"Revenue Report"
PRINT  
 E01 AS 'Product Category'
 COMPUTE AVERAGE_REVENUE/D12.2C = E02 / NUMBEROFDAYS; 
     AS 'Average Revenue'
 E03
 E05
 E07
 E09
 E11
 E13
 E15
 E17
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET STYLE *
  INCLUDE=qqdflt.sty, $
ENDSTYLE
END  

This message has been edited. Last edited by: David Briars,

Thank you very much David