reporting question

I am new to WF and I have a user asking a report that spans 3 years and he wants 2 fields to be summed (ex. pounds, profit), however he would like to list all the pounds columns, then all the profit columns.
Sample report desired.
Product: ______Pounds______ ______Profit_______
Product 2005 2006 2007 2005 2006 2007

something like that. Any help would be appreciated!!

Thank you!

This message has been edited. Last edited by: <Riley>,

Jim_at_LM

Gold member

posted

Hide Post

Just DEFINE columns (with correct size/format) and use FY variables assiging unique names and
control selection. THis is basic financial
reporting that allows easy expansion for other
calculations (variances, percentages etc.)

-* SET YOUR DM FISCAL YEAR VARIABLES BASED ON CFY

-SET FY3 = &CFY – 2 ;
-SET &FY2= &CFY -1 ;
-SET &FY1 = &CFY ;

DEFINE FILE name
PND_&CFY2 / D12.2C = IF FY EQ ‘&CFY2’ THEN POUNDS ELSE 0 ;
PND_&CFY1 / D12.2C = IF FY EQ ‘&CFY1’ THEN POUNDS ELSE 0 ;
PND_&CFY / D12.2C = IF FY EQ ‘&CFY’ THEN POUNDS ELSE 0 ;

PFT_2005 / D12.2C = IF FY EQ ‘2005’ THEN PROFIT ELSE 0 ; PFT_2006 / D12.2C = IF FY EQ ‘2006’ THEN PROFIT ELSE 0 ; interpret, or if
PFT_2007 / D12.2C = IF FY EQ ‘2007’ THEN PROFIT ELSE 0 ; you hard code.

END

TABLE FILE name
SSUM PND_2005 AS ‘POUNDS, &FY3’
PND_2006 AS ‘POUNDS, &FY2’
PND_2007 AS ‘POUNDS, &FY1’

PFT_2005 AS ‘PROFIT, &FY3’
PFT_2006 …
PFT_2007

WHERE ..
END

WebFOCUS 7.6.11, WINDOWS, HTML, PDF, EXCEL

Posts: 77 | Location: Baltimore | Registered: May 31, 2006

Ignored post by Jim_at_LM posted

Show Post

susannah

Expert

posted

Hide Post

Jim's idea is the way to go, but here's an alternative.
DEFINE FILE thing
CONCEPT/A6= 'POUNDS' ;
AMOUNT/I8 = POUNDS ;
END
TABLE FILE thing
SUM AMOUNT BY CONCEPT BY YEAR BY SOMETHINGNICE
ON TABLE HOLD AS H1
END
DEFINE FILE thing
CONCEPT/A6= 'PROFIT' ;
AMOUNT/I8 = PROFIT ;
END
TABLE FILE thing
SUM AMOUNT BY CONCEPT BY YEAR BY SOMETHINGNICE
ON TABLE HOLD AS H2
MORE
FILE H1
END
TABLE FILE H2
SUM AMOUNT ACROSS CONCEPT ACROSS YEAR
BY SOMETHINGNICE
END

In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID

Posts: 3811 | Location: Manhattan | Registered: October 28, 2003

Ignored post by susannah posted

Show Post

Tony A

Expert

posted

Hide Post

There are many ways to skin a cat and here's another one using MATCH logic. There are problems with doing this method so it might be better to use Jim's method which is probably easier to understand. Of course you could always try all three to determine which is best in your environment.

DEFINE FILE GGSALES
  YEAR/YY     = DATE;
  TYPEA/A12   = 'Dollars';
  TYPEB/A12   = 'Budget';
END

MATCH FILE GGSALES
SUM DOLLARS AS VALUE
BY TYPEA AS TYPE
BY YEAR
BY PRODUCT
WHERE YEAR FROM 1996 TO 1997
RUN
FILE GGSALES
SUM BUDDOLLARS AS VALUE
BY TYPEB AS TYPE
BY YEAR
BY PRODUCT
WHERE YEAR FROM 1996 TO 1997
AFTER MATCH HOLD OLD-OR-NEW
END
-RUN

TABLE FILE HOLD
SUM COMPUTE OUT_VAL/D12.2c = IF TYPE EQ 'Budget' THEN E05 ELSE E04; AS ''
ACROSS TYPE AS ''
ACROSS YEAR AS ''
BY PRODUCT  AS ''
END
-RUN

In FOCUS since 1986	WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
	WebFOCUS App Studio 8.2.06 standalone on Windows 10

Posts: 5694 | Location: United Kingdom | Registered: April 08, 2004

Ignored post by Tony A posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

reporting question

Focal Point | Privacy Statement | Terms of Service