Focal Point
Across and Subtotals with Computes
March 19, 2008, 04:13 PM
AnatessAcross and Subtotals with Computes
Hi all -
I don't even know what to search for to get what I need. So, I'm hoping it's a simple, "try this command" answer so I can do some researching. The problem seems simple enough, but the answer is eluding me. I need to create this report:
BYFIELD1 BYFIELD2 ACROSS_VALUE1 ACROSS_VALUE2 ACROSS_VALUE3 ... etc
XXXXXXXXXX YYYYYYYYYY 100.00 50.00 25.00
ZZZZZZZZZZ 100.00 50.00 50.00
SUBTOTAL FOR XXXXXXXXXX 200.00 100.00 75.00
COMPUTED FIELD FOR XXXXXXXXXX 4.00 2.00 1.50
AAAAAAAAAA YYYYYYYYYY 200.00 50.00 125.00
ZZZZZZZZZZ 50.00 150.00 50.00
SUBTOTAL FOR AAAAAAAAAAAA 250.00 200.00 175.00
COMPUTED FIELD FOR BYFIELD1 5.00 4.00 3.50
GRANDTOTAL 450.00 300.00 250.00
The Computed field is the Subtotal for each Across Value of the By field divided by 50. How do I make that Computed Subtotal appear on each Across Value? Without the across I can do a Recap on a Subfoot, but not quite sure how to make an across recap...
WF 8.1.05 Windows
March 19, 2008, 06:13 PM
GinnyJakesAnatess,
This is pretty hokey but maybe you can make something out of it.
SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
DEFINE FILE CAR
SORTFIELD1/I1=2;
QCAR/A16=' ';
END
TABLE FILE CAR
SUM
COMPUTE SALES50/I6=SALES/50; AS SALES
BY COUNTRY
BY SORTFIELD
BY QCAR AS CAR
ACROSS BODYTYPE
ON TABLE HOLD AS SUMCAR FORMAT ALPHA
END
? HOLD SUMCAR
DEFINE FILE CAR
SORTFIELD1/I1=1;
END
TABLE FILE CAR
SUM SALES
BY COUNTRY
BY SORTFIELD
BY CAR
ACROSS BODYTYPE
ON TABLE HOLD AS DTLCAR FORMAT ALPHA
END
? HOLD DTLCAR
TABLE FILE SUMCAR
PRINT E04 E05 E06 E07 E08
BY COUNTRY
BY SORTFIELD
BY CAR
ON SORTFIELD SUBTOTAL
WHEN SORTFIELD EQ 1
MORE
FILE DTLCAR
END
Ginny
---------------------------------
Prod: WF 7.7.01 Dev: WF 7.6.9-11
Admin, MRE,self-service; adapters: Teradata, DB2, Oracle, SQL Server, Essbase, ESRI, FlexEnable, Google
March 20, 2008, 11:34 AM
AnatessHi Ginny,
Man, I really thought it was just another keyword like ACROSS-CAP or something!
But, I see what you're trying to do. Brilliant! I should be able to implement something like it. Thanks.
WF 8.1.05 Windows
March 20, 2008, 02:08 PM
FrankDutchAnatess
It is a nice solution from Ginny, but if the number of across fields change you need something extra to find out home many columns you have.
That can be done by reading the holdfile. How to do that is somewhere on FP, but I can't remember the correct coding.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
March 21, 2008, 09:24 AM
AnatessHi Frank,
I actually posted a
question before on doing alternate colors on each across value that has another across within it. I got the logic to count how many "inside across" columns are within that first across field to determine how many columns to color a certain shade. So, I'm gonna use that same logic.
WF 8.1.05 Windows
March 21, 2008, 01:10 PM
susannahAnatess,
this looks to my eye like an FML application, straight away.
Does anyone else see it that way?
-s
| In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID |
March 21, 2008, 03:37 PM
FrankDutchSusannah
I thought of that too, but to be honest the FML might bring a lot of other problems. The hierarchy should be build careful but once you have done that it is a very strong method.
|
Frank |
| prod: WF 7.6.10 platform Windows, databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7 test: WF 7.6.10 on the same platform and databases,IE7 |
March 31, 2008, 12:00 AM
Anatess... cracking open an FML manual, reading like mad... wondering if there's ever a time that will come when I can say, I know everything there is to know about webfocus...
WF 8.1.05 Windows
March 31, 2008, 03:37 AM
Tony AAnatess,
Whilst this looks like a suitable candidate for FML I can see that you will have problems with your textual requirements on the "subheadings". This is because it spans two columns (the two "by" fields).
You will also have to use the "unusual" syntax of putting the FOR [fieldname] before the BY [fieldname].
Try this to see what I mean -
APP PREPENDPATH IBISAMP
TABLE FILE GGSALES
SUM DOLLARS AS ''
ACROSS PRODUCT AS ''
FOR REGION
'Northeast' LABEL NE OVER
'Southeast' LABEL SE OVER
'West' LABEL WE OVER
'Midwest' LABEL MW
BY ST AS ''
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF, SIZE=9, $
ENDSTYLE
END
TABLE FILE GGSALES
SUM DOLLARS AS ''
ACROSS PRODUCT AS ''
BY ST AS ''
FOR REGION
'Northeast' LABEL NE OVER
'Southeast' LABEL SE OVER
'West' LABEL WE OVER
'Midwest' LABEL MW
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
GRID=OFF, SIZE=9, $
ENDSTYLE
END
The second report shows what happens with "normal" syntax when you put the BY before the FOR.
T
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 | |
March 31, 2008, 03:50 AM
Tony ABTW, if you do this in the GUI make sure you set the FOR and BY fields correctly. If you try and move them afterwards you will find yourself with report 2 - always!! I've just tried this in the GUI and dragged the BY column (in Report and Source tabs) to where I wnated it, only to find the GUI moves the BY before the FOR
before running the report!!
T
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 | |
March 31, 2008, 08:36 AM
PBrightwellquote:
wondering if there's ever a time that will come when I can say, I know everything there is to know about webfocus...
I can save you some heart ache on that one, the answer is NO. I've been doing this for 18 years and I learn things from this forum almost every day. There will come a time when you get comfortable with it.
Pat
WF 7.6.8, AIX, AS400, NT
AS400 FOCUS, AIX FOCUS,
Oracle, DB2, JDE, Lotus Notes