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

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

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

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

T