Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Strange behavior with FML when placing recap calc in one column

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Strange behavior with FML when placing recap calc in one column
 Login/Join
 
Silver Member
posted
Hello Focal Point Friends:

I'm encountering some strange behavior with FML reports when attempting to do a recap calculation for multiple column totals and restricting it to one column. So, essentially I am trying to create a grand total for all column totals. I'm not certain if using defines for the different columns might be complicating things, but any assistance is appreciated. Example code using Car file:
 SET ACROSSTITLE = SIDE
SET NODATA = 0
DEFINE FILE CAR
2_SEATS/I6=IF CAR.BODY.SEATS EQ 2 THEN CAR.BODY.SALES ELSE 0;
4_SEATS/I6=IF CAR.BODY.SEATS EQ 4 THEN CAR.BODY.SALES ELSE 0;
5_SEATS/I6=IF CAR.BODY.SEATS EQ 5 THEN CAR.BODY.SALES ELSE 0;
END
TABLE FILE CAR
SUM
     CAR.BODY.2_SEATS/I6CB
     CAR.BODY.4_SEATS/I6CB
     CAR.BODY.5_SEATS/I6CB
FOR
     CAR.ORIGIN.COUNTRY
"EUROPE" LABEL R1 OVER
'ENGLAND' AS 'ENGLAND' LABEL R2 OVER
'W GERMANY' AS 'W GERMANY' LABEL R3 OVER
'FRANCE' AS 'FRANCE' LABEL R4 OVER
'ITALY' AS 'ITALY' LABEL R5 OVER
BAR OVER
RECAP R7=R1 + R2 + R3 + R5;
 AS 'TOTAL EURO SALES' OVER
" " LABEL R8 OVER
"ASIA" LABEL R9 OVER
'JAPAN' AS 'JAPAN' LABEL R10 OVER
BAR OVER
RECAP R12=R10;
 AS 'TOTAL ASIA SALES' OVER
" " LABEL R13 OVER
"SALES SUMMARY" LABEL R14 OVER
RECAP R15=R12 + R7;
 AS 'TOTAL SALES' OVER
" " LABEL R16 OVER
RECAP R17(3)=R15(1)+R15(2)+R15(3);
 AS 'ALL SALES SUMMARY'
ON TABLE SUBHEAD
"CAR SALES FOR EUROPE AND ASIA"
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
TYPE=DATA,
     LABEL=TOTAL,
     COLUMN=N1,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=DATA,
     LABEL=R12ASALES,
     COLUMN=N1,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=DATA,
     LABEL=R7ROSALES,
     COLUMN=N1,
     BACKCOLOR='SILVER',
     STYLE=BOLD,
$
TYPE=DATA,
     LABEL=TOTAL,
     ACROSSCOLUMN=N1,
     BACKCOLOR='SILVER',
$
TYPE=DATA,
     LABEL=R12ASALES,
     ACROSSCOLUMN=N1,
     BACKCOLOR='SILVER',
$
TYPE=DATA,
     LABEL=R7ROSALES,
     ACROSSCOLUMN=N1,
     BACKCOLOR='SILVER',
$
TYPE=ACROSSVALUE,
     ACROSS=1,
     JUSTIFY=RIGHT,
$
TYPE=FREETEXT,
     LABEL=R9,
     STYLE=BOLD,
$
TYPE=FREETEXT,
     LABEL=R1,
     STYLE=BOLD,
$
TYPE=FREETEXT,
     LABEL=R10,
     STYLE=BOLD,
$
TYPE=FREETEXT,
     LABEL=R11,
     STYLE=BOLD,
$
ENDSTYLE
END 


The code gives me blank values for the "ALL SALES SUMMARY" (R17). If I remove the column reference R17(3), and use R17=R15(1)+R15(2)+R15(3) I end up with the same figure in all three columns that does not match the data.

On my actual report I'm having a similar anomaly. In this case I want the recap to be within the 4th column, like R17(4); so R17(4)=R15(1)+R15(2)+R15(3)+R15(4) and my recap total ends up in the second column on display instead of the 4th.

I originally thought column labels were complicating matters, so I reverted the code to have my column labels match the stock R(incremental#). The same behavior persisted.

It's strange as with a more simplified example in Car:
 DEFINE FILE CAR
CONVERTIBLE/I6=IF CAR.BODY.BODYTYPE EQ 'CONVERTIBLE' THEN CAR.BODY.SALES ELSE 0;
SEDAN/I6=IF CAR.BODY.BODYTYPE EQ 'SEDAN' THEN CAR.BODY.SALES ELSE 0;
COUPE/I6=IF CAR.BODY.BODYTYPE EQ 'COUPE' THEN CAR.BODY.SALES ELSE 0;
ROADSTER/I6=IF CAR.BODY.BODYTYPE EQ 'ROADSTER' THEN CAR.BODY.SALES ELSE 0;
HARDTOP/I6=IF CAR.BODY.BODYTYPE EQ 'HARDTOP' THEN CAR.BODY.SALES ELSE 0;
END
TABLE FILE CAR
SUM
     CAR.BODY.CONVERTIBLE
     CAR.BODY.SEDAN
     CAR.BODY.HARDTOP
     CAR.BODY.ROADSTER
     CAR.BODY.COUPE
FOR
     CAR.ORIGIN.COUNTRY
'ENGLAND' AS 'ENGLAND' LABEL R1 OVER
'FRANCE' AS 'FRANCE' LABEL R2 OVER
'ITALY' AS 'ITALY' LABEL R3 OVER
'JAPAN' AS 'JAPAN' LABEL R4 OVER
'W GERMANY' AS 'W GERMANY' LABEL R5 OVER
RECAP R6=R1+R2+R3+R4+R5;
 AS 'SALES' OVER
" " LABEL R7 OVER
RECAP R8(5)=R6(1)+R6(2)+R6(3)+R6(4)+R6(5);
 AS 'ALL SALES'
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE SET ASNAMES ON
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLEMBEDIMG ON
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
$
ENDSTYLE
END 


Having the recap in the one column R8(5) in this example works fine. Curious if anyone has encountered something similar. Advanced thanks for any input and apologies if I'm missing something obvious.

This message has been edited. Last edited by: Mike Williams,


WF Version 8105
 
Posts: 45 | Registered: October 07, 2015Report This Post
Gold member
posted Hide Post
Mike,

Your columns are mixed up because you re-defined your verb objects:

 
     CAR.BODY.2_SEATS/I6CB
     CAR.BODY.4_SEATS/I6CB
     CAR.BODY.5_SEATS/I6CB
 


This created 3 new columns, so you really have 6 columns (two for each verb object). The only visible columns are 2,4, and 6. Here is a simplified version of your original:

  
DEFINE FILE CAR
2_SEATS/I6=IF CAR.BODY.SEATS EQ 2 THEN CAR.BODY.SALES ELSE 0;
4_SEATS/I6=IF CAR.BODY.SEATS EQ 4 THEN CAR.BODY.SALES ELSE 0;
5_SEATS/I6=IF CAR.BODY.SEATS EQ 5 THEN CAR.BODY.SALES ELSE 0;
END
TABLE FILE CAR
SUM
     CAR.BODY.2_SEATS/I6CB
     CAR.BODY.4_SEATS/I6CB
     CAR.BODY.5_SEATS/I6CB
FOR
     CAR.ORIGIN.COUNTRY
"EUROPE"                              OVER
'ENGLAND' AS 'ENGLAND'     LABEL CA   OVER
'W GERMANY' AS 'W GERMANY' LABEL CB   OVER
'FRANCE' AS 'FRANCE'       LABEL CC   OVER
'ITALY' AS 'ITALY'         LABEL CD   OVER
BAR OVER
RECAP TA=CA + CB + CC + CD;
 AS 'TOTAL EURO SALES'     LABEL TA   OVER
" "                                   OVER
"ASIA"                                OVER
'JAPAN' AS 'JAPAN'         LABEL CE   OVER
BAR OVER
RECAP TB=CE;
 AS 'TOTAL ASIA SALES'     LABEL TB   OVER
" "                                   OVER
"SALES SUMMARY"                       OVER
RECAP TC=TA+TB ;
 AS 'TOTAL SALES'          LABEL TC   OVER
" "                                   OVER
BAR                                   OVER
RECAP
  MYTOT(6) = TC(1) + TC(3) + TC(6) ;
                                      OVER
BAR
ON TABLE SUBHEAD
"CAR SALES FOR EUROPE AND ASIA"
ON TABLE SET ASNAMES ON
END
-RUN


which gives:

 PAGE     1
 
 CAR SALES FOR EUROPE AND ASIA
                     2_SEATS    4_SEATS    5_SEATS                             
                     -------    -------    -------                             
 EUROPE
 ENGLAND                  0          0     12,000 
 W GERMANY                0      8,900     79,290 
 FRANCE                   0          0          0 
 ITALY               25,400      4,800          0 
                   ---------  ---------  ---------
 TOTAL EURO SALES    25,400     13,700     91,290 
  
 ASIA
 JAPAN                    0     78,030          0 
                   ---------  ---------  ---------
 TOTAL ASIA SALES         0     78,030          0 
  
 SALES SUMMARY
 TOTAL SALES         25,400     91,730     91,290 
  
                   ---------  ---------  ---------
 MYTOT                                    208,420 
                   ---------  ---------  ---------
 


IBI Development
 
Posts: 61 | Registered: November 15, 2005Report This Post
Silver Member
posted Hide Post
Appreciate the help, Ed.


WF Version 8105
 
Posts: 45 | Registered: October 07, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Strange behavior with FML when placing recap calc in one column

Copyright © 1996-2020 Information Builders