 |
|||||
|
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
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. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| Subtotal on Over Calculations | Login/Join |
| Gold member |
DEFINE FILE CAR CCOUNT/D6 = 1; END TABLE FILE CAR SUM CCOUNT OVER PCT.DEALER_COST WITHIN MODEL BY MODEL ACROSS COUNTRY ON TABLE SUBTOTAL CCOUNT ON TABLE SET NODATA '--' END how do i not display "dealer cost" in my subtotal at all? Thanks! ~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~ PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode/Self-Serve Apps Only (No App Server) TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode | ||
|
| Expert |
Bearing in mind that the subtotal line(s) is/are created as a result of the report contents, I don't think that you can using a straight forward report. If you were to use FML / FRL - call it what you will - then you have control over what is being printed but that would then require you to prepare the values for the report. One thing you will not be able to do is to use the syntax to control the ROWS (e.g. BY MODEL ROWS '100 LS 2 DOOR AUTO' OVER '2000 4 DOOR BERLINA' OVER '....... etc.) as the WITHIN syntax seems to be mutually exclusive to using ROWS - you get a FOC168 error. T
| |||||||||
|
| Expert |
Had to use some grey cells for this, but you can do it using FRL (etc. etc.) and a couple of passes of the data. This solution makes use of the "WHEN EXISTS" syntax of FRL and when building up the data I specifically do not create a value for the subtotal of the percentage field so that the FRL syntax omits the final line as required. First pass summates the CCOUNT values by MODEL and COUNTRY and also adds a couple of COMPUTED fields to help with positioning in the final report. The second pass provides the same but substitutes a single value so that the summation produces a single row of output for the subtotal. The third and final pass summate the PCT.DEALER_COST WITHIN MODEL. As the hold file used is set-up in append mode all the data is combined when held soo that it can easily be used in a single FRL report. APP FI TEMPHOLD DISK TEMPHOLD.FTM (APPEND
DEFINE FILE CAR
CCOUNT/D6 WITH MODEL = 1;
MODEL_ST/A24 = 'Subtotal';
END
TABLE FILE CAR
SUM CCOUNT AS 'Count'
COMPUTE ROW_NUM/I1 = 1;
SORT_ORD/I1 = 1;
BY COUNTRY
BY MODEL
ON TABLE HOLD AS TEMPHOLD
END
TABLE FILE CAR
SUM CCOUNT
COMPUTE ROW_NUM/I1 = 1;
SORT_ORD/I1 = 2;
BY COUNTRY
BY MODEL_ST
ON TABLE HOLD AS TEMPHOLD
END
TABLE FILE CAR
SUM PCT.DEALER_COST WITHIN MODEL
COMPUTE ROW_NUM/I1 = 2;
SORT_ORD/I1 = 1;
BY COUNTRY
BY MODEL
ON TABLE HOLD AS TEMPHOLD
END
TABLE FILE TEMPHOLD
SUM DEALER_COST
BY SORT_ORD NOPRINT
BY MODEL
ACROSS COUNTRY
FOR ROW_NUM
1 WHEN EXISTS AS '' OVER
2 WHEN EXISTS AS ''
ENDMessy, but I hope this helps T
| |||||||||
|
| Gold member |
Thanks! ~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~||~~~ PROD: WebFOCUS 7.1.3 on Win 2K/IIS 6/ISAPI Mode/Self-Serve Apps Only (No App Server) TEST: WebFOCUS 7.1.3 on Win 2K/IIS 6/Weblogic 8.1/Servlet Mode | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |