Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] FML HELP! - Prevent specific column from being summed
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] FML HELP! - Prevent specific column from being summed
 Login/Join
 
Expert
posted
This is for FML reports - how do I prevent specific column from being summed?

I have data in the columns of some detail and summary rows. Because of the hierarchy in the FML report, the values in the columns of the detail rows get rolled up to the summary rows. But since I have data values for the summary rows, the data is getting doubled.

It's a little hard to explain :-(

This message has been edited. Last edited by: Francis Mariani,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
How are you specifying the rows ?
Also what makes the summary different from other records ?


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6118 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Member
posted Hide Post
With RECAP you can specifiy specific columns to be summed


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 22 | Registered: July 01, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
Waz, the Master has PROPERTY=PARENT_OF in one column that sets the relation between the columns.

Harry, I'll try the RECAP option... Thanks.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
I think RECAP is a good possibility.


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6118 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
Expert
posted Hide Post
I'll post sample code and scrubbed data. I can't seem to get RECAP to work...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
I apologize for the amount of stuff I've dumped here, but this is a cleansed version of a report I inherited. Code, metadata and data. If you create three files in baseapp with the content in this post, you should be able to run the report.

Eight rows have data in columns RBND1 and RBND2 (you can see that in the data by scrolling to the right). The rows are: Total Swizzles, Muckworm Swizzles, Jujubes Kickboxed Market Swizzles, Total Fixed Jackal, Pax Junky, Nominal Fixed Jackal, Jujubes Bubblegum Junky, Total Kiwi.

Total Swizzles and Total Fixed Jackal are not showing the values in the data row - it seems they're showing a sum of the descendant values and their own values. How do I use RECAP so that these values are not summed?

Code - fml1.fex
FILEDEF FML1 DISK baseapp/fml1.ftm

SET LINES=99999

TABLE FILE FML1
SUM
SALES

COMPUTE FOR_VALUE/A20 = FMLINFO('FORVALUE', 'A20'); NOPRINT

COMPUTE TOT_SL1/D20.2 = IF FOR_VALUE EQ '1000000000000' THEN SALES ELSE LAST TOT_SL1; NOPRINT
COMPUTE TOT_SL2/D20.2 = IF FOR_VALUE EQ '1020000000000' THEN SALES ELSE LAST TOT_SL2; NOPRINT

COMPUTE TOT_SL1_PER/P7.2B% MISSING ON = IF TOT_SL1 EQ 0 THEN 0 ELSE SALES / TOT_SL1 * 100;

COMPUTE TOT_SL2_PER/P7.2B% MISSING ON =
         IF FOR_VALUE EQ '1000000000000' THEN SALES / TOT_SL1 * 100
    ELSE IF FOR_VALUE EQ '1010000000000' THEN MISSING
    ELSE IF TOT_SL2 EQ 0 THEN MISSING
    ELSE SALES / TOT_SL2 * 100;

COMPUTE WGT1/D7.2B% MISSING ON =
    IF FOR_VALUE IN ('1020201000000', '1020101000000', '1020102010000', '1020203000000',
                   '1020202000000', '1020400000000', '1020100000000', '1020200000000') THEN WEIGHT1 ELSE MISSING;
COMPUTE WGT1_DIFF/D7.2B% MISSING ON=IF WGT1 IS MISSING THEN MISSING ELSE TOT_SL2_PER - WGT1;

COMPUTE WGT2/D7.2B% MISSING ON =
    IF FOR_VALUE IN ('1020201000000', '1020101000000', '1020102010000', '1020203000000',
                   '1020202000000', '1020400000000', '1020100000000', '1020200000000') THEN WEIGHT2 ELSE MISSING;
COMPUTE WGT2_DIFF/D7.2B% MISSING ON=IF WGT2 IS MISSING THEN MISSING ELSE TOT_SL2_PER - WGT2;

COMPUTE RBND1/D12.2B% MISSING ON =
    IF FOR_VALUE IN ('1020201000000', '1020101000000', '1020102010000', '1020203000000',
                   '1020202000000', '1020400000000', '1020100000000', '1020200000000') THEN BND1 ELSE MISSING;
COMPUTE RBND2/D12.2B% MISSING ON =
    IF FOR_VALUE IN ('1020201000000', '1020101000000', '1020102010000', '1020203000000',
                   '1020202000000', '1020400000000', '1020100000000', '1020200000000') THEN BND2 ELSE MISSING;

FOR_VALUE

FOR CLASSHIERARCHY
'1000000000000' WITH CHILDREN ALL ADD AS CAPTION LABEL R1

ON TABLE SET FORMULTIPLE ON
ON TABLE SET BLANKINDENT ON
END


Metadata - fml1.mas
FILENAME=FML1, SUFFIX=FIX     , IOTYPE=STREAM, $
  SEGMENT=FML1, SEGTYPE=S1, $
    FIELDNAME=CLASSNAME, ALIAS=E01, USAGE=A100, ACTUAL=A100,
      MISSING=ON,
      REFERENCE=CLASSHIERARCHY, PROPERTY=CAPTION,  $
    FIELDNAME=SALES, ALIAS=E02, USAGE=D20.2, ACTUAL=A20,
      MISSING=ON,$
    FIELDNAME=VALUEDATE, ALIAS=E03, USAGE=YYMD, ACTUAL=A08,
      MISSING=ON,$
    FIELDNAME=CLASSHIERARCHY, ALIAS=E04, USAGE=A20, ACTUAL=A20,
      MISSING=ON,$
    FIELDNAME=CLASSID, ALIAS=E05, USAGE=I11, ACTUAL=A11,
      MISSING=ON,$
    FIELDNAME=PARENTCLASSHIERARCHY, ALIAS=E06, USAGE=A20, ACTUAL=A20,
      MISSING=ON,$
      REFERENCE=CLASSHIERARCHY, PROPERTY=PARENT_OF,  $
    FIELDNAME=CLASSLEVEL, ALIAS=E07, USAGE=I9, ACTUAL=A09,
      MISSING=ON,$
    FIELDNAME=YYZ_CLASSNAME, ALIAS=E08, USAGE=A100, ACTUAL=A100,
      MISSING=ON, $
    FIELDNAME=WEIGHT1, ALIAS=E09, USAGE=D12.2, ACTUAL=A12,
      MISSING=ON, $
    FIELDNAME=WEIGHT2, ALIAS=E10, USAGE=D12.2, ACTUAL=A12,
      MISSING=ON, $
    FIELDNAME=BND1, ALIAS=E11, USAGE=D12.2, ACTUAL=A12,
      MISSING=ON, $
    FIELDNAME=BND2, ALIAS=E12, USAGE=D12.2, ACTUAL=A12,
      MISSING=ON, $


Data - fml1.ftm
Absolute Whammy Strategies                                                                                  353047947.74201511251020407000000             228451020400000000               4.                                                                                                   .           .           .           .           
Pax Junky                                                                                                 24294937784.19201511251020201000000             228401020200000000               4Pax Junky                                                                                                   8.64        8.98        6.64       10.99
Muckworm Swizzles                                                                                                   0.00201511251020101000000             228211020100000000               4Muckworm Swizzles                                                                                           9.11        7.32        7.00       11.11
Muckworm Government Junky                                                                                 14709194018.35201511251020202020000             228411020202000000               5.                                                                                                   .           .           .           .           
Kiwi                                                                                                       6657496916.50201511251020405010000             228541020405000000               5.                                                                                                   .           .           .           .           
Kiwi & Equivalents                                                                                                  0.00201511251020405000000             228881020400000000               4.                                                                                                   .           .           .           .           
Kiwi Equivalents                                                                                          32813198244.87201511251020405020000             228461020405000000               5.                                                                                                   .           .           .           .           
Gulf Juicy - CP                                                                                          -13332760849.44201511251020408000000             228701020400000000               4.                                                                                                   .           .           .           .           
Gulf Juicy - MTN                                                                                          -1002893388.77201511251020202070000             229091020202000000               5.                                                                                                   .           .           .           .           
Derivative Jambeaux Offset                                                                                -3307608505.63201511251020405050000             228471020405000000               5.                                                                                                   .           .           .           .           
Quizzing Market Swizzles                                                                                            0.00201511251020102020000             228601020102000000               5.                                                                                                   .           .           .           .           
Jambeaux Mix Reclassifications (Jumble)                                                                             0.00201511251020101030000             228301020101000000               5.                                                                                                   .           .           .           .           
Jambeaux Mix Reclassifications (Kiwi)                                                                               0.00201511251020412010000             228801020400000000               4.                                                                                                   .           .           .           .           
Jambeaux Mix Reclassifications (Music)                                                                              0.00201511251020102020300             228891020102020000               6.                                                                                                   .           .           .           .           
Jambeaux Mix Reclassifications (Klaxon)                                                                             0.00201511251020102010300             228171020102010000               6.                                                                                                   .           .           .           .           
Jambeaux Mix Reclassifications (Pi)                                                                                 0.00201511251020202080000             228481020202000000               5.                                                                                                   .           .           .           .           
Jambeaux Mix Reclassifications (Vex)                                                                                0.00201511251020203020000             228531020203000000               5.                                                                                                   .           .           .           .           
Jambeaux to Agriculture (Kiwi)                                                                              355744910.52201511251020412060000             228371020412010000               5.                                                                                                   .           .           .           .           
Jambeaux to Agriculture (Music)                                                                              17366817.30201511251020102020308             229011020102020300               7.                                                                                                   .           .           .           .           
Jambeaux to Agriculture (Klaxon)                                                                            160505637.96201511251020102010311             228991020102010300               7.                                                                                                   .           .           .           .           
Jambeaux to Agriculture (Vex)                                                                               355744910.52201511251020203021300             229001020203020000               6.                                                                                                   .           .           .           .           
Jambeaux to Direct Gimmick Gulf (Jumble)                                                                    251706707.38201511251020101030200             228611020101030000               6.                                                                                                   .           .           .           .           
Jambeaux to Direct Gimmick Gulf (Kiwi)                                                                     2607290373.99201511251020412080000             228141020412010000               5.                                                                                                   .           .           .           .           
Jambeaux to Direct Gimmick Gulf (Music)                                                                     255807333.84201511251020102020301             228901020102020300               7.                                                                                                   .           .           .           .           
Jambeaux to Direct Gimmick Gulf (Klaxon)                                                                   2409539855.39201511251020102010302             228191020102010300               7.                                                                                                   .           .           .           .           
Jambeaux to Direct Gimmick Gulf (Pi)                                                                        619187760.98201511251020202080400             228641020202080000               6.                                                                                                   .           .           .           .           
Jambeaux to Direct Gimmick Gulf (Vex)                                                                      2450550414.18201511251020203020300             228681020203020000               6.                                                                                                   .           .           .           .           
Jambeaux to Mix Icebox Quizzing Market Swizzles (Klaxon)                                                     72600286.84201511251020102010309             228071020102010300               7.                                                                                                   .           .           .           .           
Jambeaux to Mix Icebox Jujubes Kickboxed Market Swizzles (Kiwi)                                            1465603156.10201511251020412040000             228831020412010000               5.                                                                                                   .           .           .           .           
Jambeaux to Mix Icebox Jujubes Kickboxed Market Swizzles (Klaxon)                                          1257165873.74201511251020102010308             229081020102010300               7.                                                                                                   .           .           .           .           
Jambeaux to Mix Icebox Jujubes Kickboxed Market Swizzles (Vex)                                                      0.00201511251020203020900             228051020203020000               6.                                                                                                   .           .           .           .           
Jambeaux to Mix and Zed Pizza - Fixed Jackal (Jumble)                                                       109275949.08201511251020101030700             228971020101030000               6.                                                                                                   .           .           .           .           
Jambeaux to Mix and Zed Pizza - Fixed Jackal (Kiwi)                                                        2032371246.23201511251020412030000             228821020412010000               5.                                                                                                   .           .           .           .           
Jambeaux to Mix and Zed Pizza - Fixed Jackal (Music)                                                        255386075.28201511251020102020306             228081020102020300               7.                                                                                                   .           .           .           .           
Jambeaux to Mix and Zed Pizza - Fixed Jackal (Klaxon)                                                       743368672.01201511251020102010307             228711020102010300               7.                                                                                                   .           .           .           .           
Jambeaux to Mix and Zed Pizza - Fixed Jackal (Pi)                                                           253255892.07201511251020202081100             228721020202080000               6.                                                                                                   .           .           .           .           
Jambeaux to Mix and Zed Pizza - Fixed Jackal (Vex)                                                          857338933.49201511251020203020800             228731020203020000               6.                                                                                                   .           .           .           .           
Jambeaux to Mix and Zed Icebox Quizzing Market Swizzles (Kiwi)                                              772926229.33201511251020412050000             228841020412010000               5.                                                                                                   .           .           .           .           
Jambeaux to Mix and Zed Icebox Quizzing Market Swizzles (Music)                                             747659325.33201511251020102020307             228921020102020300               7.                                                                                                   .           .           .           .           
Jambeaux to Mix and Zed Icebox Quizzing Market Swizzles (Vex)                                                       0.00201511251020203021100             228101020203020000               6.                                                                                                   .           .           .           .           
Jambeaux to Infrastructure (Jumble)                                                                        1525171049.08201511251020101030600             228381020101030000               6.                                                                                                   .           .           .           .           
Jambeaux to Infrastructure (Kiwi)                                                                                   0.00201511251020412140000             228021020412010000               5.                                                                                                   .           .           .           .           
Jambeaux to Infrastructure (Klaxon)                                                                        9381684692.32201511251020102010306             228281020102010300               7.                                                                                                   .           .           .           .           
Jambeaux to Infrastructure (Pi)                                                                            2031553005.97201511251020202080800             228511020202080000               6.                                                                                                   .           .           .           .           
Jambeaux to Infrastructure (Vex)                                                                           1897409314.58201511251020203020700             228761020203020000               6.                                                                                                   .           .           .           .           
Jambeaux to Quaky Kiwi                                                                                      198995687.42201511251020412110000             228161020412010000               5.                                                                                                   .           .           .           .           
Jambeaux to Pincheck on Gimmick Real Maxims (Pi)                                                           -291438929.25201511251020202080700             229051020202080000               6.                                                                                                   .           .           .           .           
Jambeaux to Hiccupped on Gimmick Real Maxims (Jumble)                                                       -51430399.28201511251020101030500             229021020101030000               6.                                                                                                   .           .           .           .           
Jambeaux to Hiccupped on Gimmick Real Maxims (Kiwi)                                                                 0.00201511251020412130000             228011020412010000               5.                                                                                                   .           .           .           .           
Jambeaux to Hiccupped on Gimmick Real Maxims (Music)                                                       -167498484.40201511251020102020305             229041020102020300               7.                                                                                                   .           .           .           .           
Jambeaux to Hiccupped on Gimmick Real Maxims (Klaxon)                                                     -1312800802.08201511251020102010305             229061020102010300               7.                                                                                                   .           .           .           .           
Puppydom to Hiccupped on Gimmick Real Maxims (Vex)                                                        -8388362623.43201511251020203020600             229071020203020000               6.                                                                                                   .           .           .           .           
Puppydom to Gimmick Gulf Pizza (Music)                                                                      180327955.78201511251020102020302             228741020102020300               7.                                                                                                   .           .           .           .           
Puppydom to Gimmick Gulf Pizza (Pi)                                                                         180327955.78201511251020202080900             228091020202080000               6.                                                                                                   .           .           .           .           
Puppydom to Gimmick Brazen (Jumble)                                                                         112857579.80201511251020101030800             228041020101030000               6.                                                                                                   .           .           .           .           
Puppydom to Gimmick Brazen (Kiwi)                                                                          1262286369.20201511251020412070000             229111020412010000               5.                                                                                                   .           .           .           .           
Puppydom to Gimmick Brazen (Music)                                                                           35036012.66201511251020102020311             228361020102020300               7.                                                                                                   .           .           .           .           
Puppydom to Gimmick Brazen (Klaxon)                                                                        4223561704.48201511251020102010312             228781020102010300               7.                                                                                                   .           .           .           .           
Puppydom to Gimmick Brazen (Pi)                                                                             209592648.20201511251020202081300             228031020202080000               6.                                                                                                   .           .           .           .           
Puppydom to Gimmick Brazen (Vex)                                                                           2022353842.58201511251020203021400             228791020203020000               6.                                                                                                   .           .           .           .           
Waxwings to Gimmick Real Maxims (Jumble)                                                                    935976642.04201511251020101030400             228321020101030000               6.                                                                                                   .           .           .           .           
Waxwings to Gimmick Real Maxims (Kiwi)                                                                              0.00201511251020412120000             228001020412010000               5.                                                                                                   .           .           .           .           
Waxwings to Gimmick Real Maxims (Music)                                                                      15232061.30201511251020102020304             229031020102020300               7.                                                                                                   .           .           .           .           
Waxwings to Gimmick Real Maxims (Klaxon)                                                                  20552178964.09201511251020102010304             228631020102010300               7.                                                                                                   .           .           .           .           
Waxwings to Gimmick Real Maxims (Pi)                                                                       6275075605.97201511251020202080600             228501020202080000               6.                                                                                                   .           .           .           .           
Waxwings to Gimmick Real Maxims (Vex)                                                                     16901710590.81201511251020203020500             228751020203020000               6.                                                                                                   .           .           .           .           
Waxwings to Icebox Real Maxims (Music)                                                                       94584099.28201511251020102020309             228351020102020300               7.                                                                                                   .           .           .           .           
Waxwings to Icebox Real Maxims (Klaxon)                                                                     199822199.94201511251020102010313             228341020102010300               7.                                                                                                   .           .           .           .           
Waxwings to Icebox Real Maxims (Vex)                                                                        356785271.11201511251020203021500             228331020203020000               6.                                                                                                   .           .           .           .           
Waxwings to Real Maxims Gulf (Jumble)                                                                        19086157.16201511251020101030300             228771020101030000               6.                                                                                                   .           .           .           .           
Waxwings to Real Maxims Gulf (Kiwi)                                                                        1659777214.47201511251020412090000             228151020412010000               5.                                                                                                   .           .           .           .           
Waxwings to Real Maxims Gulf (Music)                                                                            22360.05201511251020102020303             228691020102020300               7.                                                                                                   .           .           .           .           
Jellying to Real Maxims Gulf (Klaxon)                                                                       839859677.25201511251020102010303             228291020102010300               7.                                                                                                   .           .           .           .           
Jellying to Real Maxims Gulf (Pi)                                                                                   0.00201511251020202080500             228851020202080000               6.                                                                                                   .           .           .           .           
Jellying to Real Maxims Gulf (Vex)                                                                         1480768398.94201511251020203020400             228861020203020000               6.                                                                                                   .           .           .           .           
Jellying to Upsized Flyspecks (Klaxon)                                                                      222886141.72201511251020102010314             229101020102010300               7.                                                                                                   .           .           .           .           
Jellying to Upsized Flyspecks (Pi)                                                                                  0.00201511251020202081200             228661020202080000               6.                                                                                                   .           .           .           .           
Jellying to Upsized Flyspecks (Vex)                                                                         843663075.45201511251020203021200             228651020203020000               6.                                                                                                   .           .           .           .           
Jujubes Kickboxed Market Swizzles                                                                                   0.00201511251020102010000             228241020102000000               5Jujubes Kickboxed Market Swizzles                                                                          58.61       61.14       55.40       60.61
Jujubes Bubblegum Junky                                                                                             0.00201511251020203000000             228521020200000000               4Jujubes Bubblegum Junky                                                                                    17.03       17.24       14.40       19.03
Jujubes Bubblegum Junky and Equiv                                                                         27896402417.72201511251020203010000             228671020203000000               5.                                                                                                   .           .           .           .           
Forward FX Contracts                                                                                         14099014.03201511251020405060000             228911020405000000               5.                                                                                                   .           .           .           .           
Global Swizzles                                                                                                     0.00201511251020102000000             228271020100000000               4.                                                                                                   .           .           .           .           
Gross-up on Muckworm Gimmick Swizzles (Jumble)                                                              805114846.47201511251020101030100             228311020101030000               6.                                                                                                   .           .           .           .           
Gross-up on Jujubes Gimmick Kickboxed Market Swizzles (Klaxon)                                            11928761490.02201511251020102010301             228181020102010300               7.                                                                                                   .           .           .           .           
Zed Active 70 Cell OW/UW                                                                                            0.00201511251020404000000             228061020400000000               4.                                                                                                   .           .           .           .           
Zed Distressed Pincheck Jingle Pizza                                                                           136381.88201511251020202060000             228621020202000000               5.                                                                                                   .           .           .           .           
Zed DEK Loan Puzzlement Pizza                                                                               170195195.72201511251020202050000             228421020202000000               5.                                                                                                   .           .           .           .           
Zed DEK and Distressed Pincheck Currency Pizza                                                                      0.00201511251020402000000             228441020400000000               4.                                                                                                   .           .           .           .           
Inflation-Sensitive Wizard                                                                                          0.00201511251040601000000             22887.                           4.                                                                                                   .           .           .           .           
Inflation-linked Junky                                                                                              0.00201511251020202040000             228551020202000000               5.                                                                                                   .           .           .           .           
Nominal Fixed Jackal                                                                                                0.00201511251020202000000             228391020200000000               4Nominal Fixed Jackal                                                                                       10.47        8.55        8.47       12.47
Offset of Gimmick Brazen Gross-up (Kiwi)                                                                 -12734502054.49201511251020412020000             228811020412010000               5.                                                                                                   .           .           .           .           
Offset of Gimmick Brazen Gross-up (Pi)                                                                              0.00201511251020202080300             228491020202080000               6.                                                                                                   .           .           .           .           
Gimmick Muckworm Swizzles                                                                                  2683716154.91201511251020101020000             228231020101000000               5.                                                                                                   .           .           .           .           
Gimmick Quizzing Market Swizzles                                                                           4891041232.14201511251020102020200             228571020102020000               6.                                                                                                   .           .           .           .           
Gimmick Jujubes Kickboxed Market Swizzles                                                                 39762538300.06201511251020102010200             228261020102010000               6.                                                                                                   .           .           .           .           
Icebox Muckworm Swizzles                                                                                  13420871080.41201511251020101010000             228221020101000000               5.                                                                                                   .           .           .           .           
Icebox Quizzing Market Swizzles                                                                            8690024763.45201511251020102020100             228561020102020000               6.                                                                                                   .           .           .           .           
Icebox Jujubes Kickboxed Market Swizzles                                                                  60032128865.77201511251020102010100             228251020102010000               6.                                                                                                   .           .           .           .           
Icebox Real Maxims - accruals (Pi)                                                                              11102.83201511251020202080100             228591020202080000               6.                                                                                                   .           .           .           .           
Zinky                                                                                                    -31925533906.70201511251020405030000             228431020405000000               5.                                                                                                   .           .           .           .           
Reverse Zinky                                                                                              2514246017.89201511251020405040000             228981020405000000               5.                                                                                                   .           .           .           .           
Total Kiwi                                                                                                          0.00201511251020400000000             228111020000000000               3Total Kiwi                                                                                                 -3.86       -3.23       -4.10       -1.86
Total Kiwi for Benefits Jellybean                                                                          2825972196.80201511251010000000000             228931000000000000               2.                                                                                                   .           .           .           .           
Total Consolidated Pax                                                                                              0.00201511251000000000000             22896.                           1.                                                                                                   .           .           .           .           
Total Swizzles                                                                                                      0.00201511251020100000000             228201020000000000               3Total Swizzles                                                                                             67.72       68.46       64.40       69.72
Total Fixed Jackal                                                                                                  0.00201511251020200000000             228951020000000000               3Total Fixed Jackal                                                                                         36.14       34.77       33.70       38.14
Total Puzzlement Jellybean                                                                                          0.00201511251020000000000             228941000000000000               2.                                                                                                   .           .           .           .           
Total Jellybean Currency Pizza                                                                             -140450760.92201511251020403000000             228581020400000000               4.                                                                                                   .           .           .           .           
Total Mezzotinting                                                                                                  0.00201511251020500000000             228121020000000000               3.                                                                                                   .           .           .           .           
Mezzotinting Infrastructure                                                                                         0.00201511251020501000000             228131020500000000               4.                                                                                                   .           .           .           .           


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Hi Francis, You can't traverse the entire hierarchy and put recaps in certain point in the hierarchy. What you can do is something like this:

CHAN_NAME NOPRINT
FOR
ACCOUNT_CODE
"Revenue" LABEL R1 OVER
2100 WITH CHILDREN 1 ADD AS CAPTION LABEL R2 OVER
2500 WITH CHILDREN 1 ADD AS CAPTION LABEL R3 OVER
BAR AS '-' OVER
RECAP R5=R2 + R3 ;
AS 'Total Revenue' OVER
" " LABEL R6 OVER
"Expenditures" LABEL R7 OVER
3100 WITH CHILDREN 1 ADD AS CAPTION LABEL R8 OVER
3200 WITH CHILDREN 1 ADD AS CAPTION LABEL R9 OVER
5200 WITH CHILDREN 1 ADD AS CAPTION LABEL R10 OVER
BAR AS '-' OVER
RECAP R12=R8 + R9 + R10;
AS 'Total Expenditures' OVER
" " LABEL R13 OVER
" " LABEL R14 OVER
RECAP R15=R5 - R12 ;
AS 'Excess Rev over Exp' OVER
BAR AS '='


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 22 | Registered: July 01, 2014Reply With QuoteReport This Post
Expert
posted Hide Post
Harry, thanks for your example. Unfortunately this type of FML report specifies the rows.

For the type of FML in this report, using
FOR CLASSHIERARCHY
'1000000000000' WITH CHILDREN ALL ADD AS CAPTION LABEL R1
automatically sets up the parents and descendants and automatically indents.

I think the problem is in the data and perhaps the lines
COMPUTE WGT1/D7.2B% MISSING ON =
IF FOR_VALUE IN ('1020201000000', '1020101000000', '1020102010000', '1020203000000',
'1020202000000', '1020400000000', '1020100000000', '1020200000000') THEN WEIGHT1 ELSE MISSING;

I'm still debugging.

Regards,


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Member
posted Hide Post
Depending if you want to change columns dynamically, your choices are ether a COMPUTE using the FMLFOR family of functions or using DBA down to the value level.


WebFOCUS 7.6
Windows, All Outputs
 
Posts: 22 | Registered: July 01, 2014Reply With QuoteReport This Post
Gold member
posted Hide Post
Francis,

The values for the verb objects in the fml request are automatically aggregated,
both the parent records and all children. This way totals can be put into parent
records even if those records have no children. In your case, the bnd1 and bnd2
fields have values for parents (e.g. Total Fixed Jackal) in addition to children. You
can force a given parent level to have a zero value, if this is what you really want,
by defining the value at retrieval time:

  
DEFINE FILE FML1
LEV/I4 = CLASSLEVEL ;
MBND1/D7.2 = BND1;
NBND1/D7.2 = IF LEV EQ 3 THEN 0 ELSE BND1;
END
-RUN
TABLE FILE FML1
SUM MIN.LEV MBND1 NBND1
FOR CLASSHIER
'1000000000000' WITH CHILDREN ALL ADD AS CAPTION LABEL R1
ON TABLE SET FORMULTIPLE ON
ON TABLE SET BLANKINDENT ON
ON TABLE HOLD FORMAT WP AS FMLC
END
-RUN


Here is sample output:

      Total Fixed Jackal                         3     63.21     29.51
        Pax Junky                                4      6.64      6.64
        Nominal Fixed Jackal                     4      8.47      8.47
          Muckworm Government Junky              5       .00       .00
          Inflation-linked Junky                 5       .00       .00
          Zed DEK Loan Puzzlement Pizza          5       .00       .00
          Zed Distressed Pincheck Jingle         5       .00       .00
          Gulf Juicy - MTN                       5       .00       .00
          Jambeaux Mix Reclassifications         5       .00       .00
            Icebox Real Maxims - accruals        6       .00       .00
            Offset of Gimmick Brazen Gross       6       .00       .00
            Jambeaux to Direct Gimmick Gul       6       .00       .00
            Jellying to Real Maxims Gulf (       6       .00       .00
            Waxwings to Gimmick Real Maxim       6       .00       .00
            Jambeaux to Pincheck on Gimmic       6       .00       .00
            Jambeaux to Infrastructure (Pi       6       .00       .00
            Puppydom to Gimmick Gulf Pizza       6       .00       .00
            Jambeaux to Mix and Zed Pizza        6       .00       .00
            Jellying to Upsized Flyspecks        6       .00       .00
            Puppydom to Gimmick Brazen (Pi       6       .00       .00
        Jujubes Bubblegum Junky                  4     14.40     14.40
          Jujubes Bubblegum Junky and Eq         5       .00       .00


Is this sort of what you wanted to do?

This message has been edited. Last edited by: Edward Wolfgram,


IBI Development
 
Posts: 57 | Registered: November 15, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
Edward, yes, this is something like what I need to do. I don't know how many levels there are, so I'm not sure if DEFINE will help.

My "solution" is to convert the values to alpha - in this way they don't get summed!


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Reply With QuoteReport This Post
Expert
posted Hide Post
quote:
My "solution" is to convert the values to alpha - in this way they don't get summed!


Ah the old classic method. Remember it, just.....


Waz...

Prod:WebFOCUS 7.6.10/8.1.04Upgrade:WebFOCUS 8.2.06OS:LinuxOutputs:HTML, PDF, Excel, PPT
In Focus since 1984
Know The Code

 
Posts: 6118 | Location: 33.8688° S, 151.2093° E | Registered: October 31, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] FML HELP! - Prevent specific column from being summed

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.