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