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.
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,
Webfocus 8105/8202 Windows All Outputs
July 21, 2020, 06:25 PM
Waz
Um, er, change BY to ACROSS ?
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!
July 22, 2020, 08:31 AM
BI Dev
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 DEVThis message has been edited. Last edited by: BI Dev,
Webfocus 8105/8202 Windows All Outputs
July 22, 2020, 04:38 PM
Waz
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.
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!
July 22, 2020, 05:53 PM
BI Dev
Yes, you are right. That is what I am trying to do.
Webfocus 8105/8202 Windows All Outputs
July 22, 2020, 06:17 PM
Waz
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.
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!
July 23, 2020, 05:12 AM
Danny-SRL
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.
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
July 23, 2020, 11:17 AM
BI Dev
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.
Regards BI DEVThis message has been edited. Last edited by: BI Dev,
Webfocus 8105/8202 Windows All Outputs
July 23, 2020, 05:32 PM
David Briars
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,
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
July 23, 2020, 07:01 PM
BI Dev
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.
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,
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
July 24, 2020, 06:47 AM
Danny-SRL
I forgot to post the MASTER of the BIDEV.txt file:
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
July 24, 2020, 12:11 PM
BI Dev
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
Webfocus 8105/8202 Windows All Outputs
July 24, 2020, 02:56 PM
David Briars
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,
Pilot: WebFOCUS 8.2.06 Test: WebFOCUS 8.1.05M Prod: WebFOCUS 8.1.05M Server: Windows Server 2016/Tomcat Standalone Workstation: Windows 10/IE11+Edge Database: Oracle 12c, Netezza, & MS SQL Server 2019 Output: AHTML/XLSX/HTML/PDF/JSCHART Tools: WFDS, Repository Content, BI Portal Designer & ReportCaster
This message has been edited. Last edited by: Danny-SRL, 