Simple parameter/report question

I'm looking for a simple way to produce a report that compares current YTD sales to the same period from the previous year.

I want to enter a year and month and have the procedure calculate the previous year and report data from a table that has a date field and dollar field. The report would be similar to the format below. Obviously I'm new to this - just out of training and didn't see anything in the documentation that would point me in the right direction.

Customer Sales Sales Pct
1-9/2007 1-9/2006 Change

Customer A 50 25 100%
Customer B 120 100 25%

Posts: 1 | Registered: September 05, 2007

Francis Mariani

Expert

posted

Hide Post

Something quickly thrown together to start you off:

-SET &ECHO=ALL;

-DEFAULT &ORDER_YY = '1997';
-DEFAULT &ORDER_MM = '03';

-SET &PORDER_YY = &ORDER_YY - 1;

-SET &DT1 = &ORDER_YY  | '/' | &ORDER_MM;
-SET &DT2 = &PORDER_YY | '/' | &ORDER_MM;

DEFINE FILE GGORDER
ORDER_DATE_YYMD/YYMD = ORDER_DATE;
ORDER_DATE_YYM/YYM   = ORDER_DATE;
QUANTITY1/D10 = IF ORDER_DATE_YYM EQ '&DT1' THEN QUANTITY ELSE 0;
QUANTITY2/D10 = IF ORDER_DATE_YYM EQ '&DT2' THEN QUANTITY ELSE 0;

END 

TABLE FILE GGORDER
SUM 
QUANTITY1 AS '&DT1'
QUANTITY2 AS '&DT2'
COMPUTE CHANGE/D10% = (QUANTITY1 - QUANTITY2) / QUANTITY2 * 100; AS 'Change'
BY VENDOR_NAME

WHERE ORDER_DATE_YYM EQ '&DT1' OR ORDER_DATE_YYM EQ '&DT2'
HEADING
" "
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLESHEET *
TYPE=REPORT, GRID=OFF,
FONT='ARIAL', SIZE=8, $
ENDSTYLE
END

GGORDER is a demo FOCUS DB that's supplied with WF and should be available in all installations.

I had to define a new date field because the date in GGORDER does not have the century.

One could go on for hours explaining each line, but I'll wait for questions you may have.

One thing to be careful of is that if you're reading a RDBMS table, DEFINE statements may bot be efficient. In that case, I usually summarize the measures by all the dimensions and create a HOLD file that I run the report off. This report could have probably been achieved with the use of ACROSS but then there could be a complication with computing the CHANGE column.

Francis

Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server

Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

Simple parameter/report question

Focal Point | Privacy Statement | Terms of Service