Focal Point
BY DISPLAY For SUM OVER?

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3241014351

February 09, 2006, 06:04 PM
Matthew
BY DISPLAY For SUM OVER?
Is there a cammand that does the equivalent of a BYDISPLAY for multiple SUM fields with the OVER command? IF I code:
TABLE FILE QMR_REVENUE
SUM   REVENUE_AMOUNT AS 'REVENUE'
      OVER PRODUCT_FAMILY_BEGINNING_BALAN AS 'UNITS'
      OVER MOU_FAMILY AS 'MOUs'
BY   GRP_TITLE
BY   DATATYPE_PARENT
BY   EVP_MINUS_1
BY   ST_TITLE
BY   GRP_SUF

I get
					01/01/2005
Datatype Parent	Evp Minus 1	ST_TITLE	GRP_SUF		
ACTUALS	CONSUMER_ONLY	AZ	39682	REVENUE	135,843.12
				         UNITS	.00
				         MOUs	$.00
ACTUALS	CONSUMER_ONLY	AZ	ACCESS OTHER	REVENUE	47,932.88
				       UNITS	.00
				       MOUs	$.00


What I want is
					01/01/2005
Datatype Parent	Evp Minus 1	ST_TITLE	GRP_SUF		
ACTUALS	CONSUMER_ONLY	AZ	39682	REVENUE	135,843.12
ACTUALS	CONSUMER_ONLY	AZ	39682    UNITS	.00
ACTUALS	CONSUMER_ONLY	AZ	39682    MOUs	$.00
ACTUALS	CONSUMER_ONLY	AZ	ACCESS OTHER	REVENUE	47,932.88
ACTUALS	CONSUMER_ONLY	AZ	ACCESS OTHER      UNITS	.00
ACTUALS	CONSUMER_ONLY	AZ	ACCESS OTHER      MOUs	$.00


I have BYDISPLAY on and it does repeat the information except for the "SUM OVER" fields.

Thanks.

This message has been edited. Last edited by: Matthew,


PROD: WebFOCUS 7.1.4 on Win 2003/Microsoft-IIS 6.0/ServletExec 4.2/JAVA version 1.5.0_09
DEV: WebFOCUS 7.6.1 on Win 2003/Microsoft-IIS 6.0/ServletExec 4.2/JAVA version 1.5.0_09
LOCAL: WebFOCUS 7.6.2 on Win XP SP2/Apache Tomcat 5.5.17/JAVA version 1.5.0_09
February 09, 2006, 08:40 PM
susannah
hhmmm. i don't think so. good try, but BYDISPLAY wouldn't work with OVER, not designed for that. Its designed for BY where there are multiple records for each BY field...in your case, you really only have 1 record per BY, even tho you're making it sorta look like 3.
FML is probably the best way to do it for you;
or you could do it the hard way; make 3 separate files
DEFINE FILE ...
MYLABEL/A10='REVENUE';
MYDATA/D11.2=REVENUE_AMOUNT ;
END
TABLE FILE ..
PRINT GRP_TITLE DATATYPE_PARENT EVP_MINUS_1 ST_TITLE GRP_SUF MYLABEL MYDATA
ON TABLE HOLD AS H1
END
DEFINE FILE ...
MYLABEL/A10='UNITS;
MYDATA/D11.2=PRODUCT_FAMILY_BEGINNING_BALAN ;
END
TABLE FILE ..
PRINT GRP_TITLE DATATYPE_PARENT EVP_MINUS_1 ST_TITLE GRP_SUF MYLABEL MYDATA
ON TABLE HOLD AS H2
END
DEFINE FILE ...
MYLABEL/A10='MOUs';
MYDATA/D11.2=MOU_FAMILY ;
END
TABLE FILE ..
PRINT GRP_TITLE DATATYPE_PARENT EVP_MINUS_1 ST_TITLE GRP_SUF MYLABEL MYDATA
ON TABLE HOLD AS H3
END
-RUN
TABLE FILE H1
PRINT *
ON TABLE HOLD AS H4
MORE
FILE H2
MORE
FILE H3
END
-*
-* now do a straight print, or...
SET BYDISPLAY = ON
TABLE FILE H4
SUM MYDATA BY {all those fields}
BY MYFIELD
ON TABLE do whatever
...now you'll have to do some creative formatting to get the dollars to print with a $
Not at all an elegant solution, but you can clearly see the idea, yes?

This message has been edited. Last edited by: susannah,




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 10, 2006, 11:54 AM
Matthew
Thanks for the suggestion. I had this as three separate files and was trying to get it all into one. I'll keep the three files for now unless there is some way to accomplish getting the extra data labels to show on the SUM OVER fields.


PROD: WebFOCUS 7.1.4 on Win 2003/Microsoft-IIS 6.0/ServletExec 4.2/JAVA version 1.5.0_09
DEV: WebFOCUS 7.6.1 on Win 2003/Microsoft-IIS 6.0/ServletExec 4.2/JAVA version 1.5.0_09
LOCAL: WebFOCUS 7.6.2 on Win XP SP2/Apache Tomcat 5.5.17/JAVA version 1.5.0_09
February 10, 2006, 12:04 PM
susannah
ah, then you're ready for FML. Read up on it. You'll really like it. (doc serv will come along and point you.)




In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
February 11, 2006, 10:09 AM
<JG>
I prefer the easy method, use the following for HTML or modify for another format.

DEFINE FILE CAR
DUMMY/A1=' ';
END
TABLE FILE CAR
PRINT COUNTRY AS '' MODEL AS '' CAR AS '' DEALER_COST AS 'Dealer Cost'
OVER COUNTRY AS '' MODEL AS '' CAR AS '' RETAIL_COST AS 'Retail Cost'
BY DUMMY NOPRINT
BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
ON DUMMY SUBHEAD
"Country <+0>Model <+0>Car <+0>Costs"
ON TABLE SET STYLESHEET *
TYPE = REPORT, GRID = OFF, $
TYPE = SUBHEAD, HEADALIGN = INTERNAL, $
TYPE = SUBHEAD, LINE=1, ITEM=1,COLSPAN=1, STYLE = BOLD, JUSTIFY=LEFT, $
TYPE = SUBHEAD, LINE=1, ITEM=2,COLSPAN=1, STYLE = BOLD, JUSTIFY=LEFT, $
TYPE = SUBHEAD, LINE=1, ITEM=3,COLSPAN=1, STYLE = BOLD, JUSTIFY=LEFT, $
TYPE = SUBHEAD, LINE=1, ITEM=4,COLSPAN=1, STYLE = BOLD, JUSTIFY=LEFT, $
ENDSTYLE
END
-RUN
February 13, 2006, 08:07 AM
<DocServices>
Greetings,

For information on FML, please refer to the WebFOCUS Creating Financial Reports 7.1 (DN4500710.0505) manual.

You can buy, view, and/or download this manual from the i-Base: The Technical Documentation Library.

If you have an InfoResponse ID, log on to the Tech Support Web site and then access i-Base. By logging in first, you can download the PDF file and/or view the HTMLHelp version.

Hope this helps.

Regards,
Jennifer