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.
I have an FML question to do with the syntax of FOR ... OVER ...<br /><br /> I've attached a fex to illustrate what I'd like to do. I'm reporting on RETAIL_COST and DEALER_COST. I would like to add another line to the FOR matrix that reports on two othr fields (not RETAIL_COST or DEALER_COST), SALES and SEATS for example. I would like to have a line after 'CONV+COUPE' that reads 'TEST' with the summed values of SALES and SEATS. <br /><br />I'm not sure what to use. DATA seems to be constant values and I'm not sure if I should be using PICKUP. Dev Studio 5.3.2 doesn't seem to make it easier to do. <br /><br />Please help!<br /><br /> Thanks.<br /><br /><br />
TABLE FILE CAR<br />SUM <br /> RETAIL_COST AS 'Retail'<br />
DEALER_COST AS 'Dealer'<br />
ACROSS <br /> COUNTRY AS '
'<br />FOR<br />
BODYTYPE<br />'CONVERTIBLE' AS
'CONVERTIBLE' LABEL R1 OVER
COUPE' AS 'COUPE' LABEL R2 OVER
<br />'HARDTOP' AS 'HARDTOP'
LABEL R3 OVER <br />'ROADSTER'
AS 'ROADSTER' LABEL R4 OVER
<br />'SEDAN' AS 'SEDAN' LABEL
R5 OVER <br />'SUV' AS 'SUV'
LABEL R6 OVER <br />'VAN' AS
'VAN' LABEL R7 OVER <br />
BAR AS '-' OVER <br />
RECAP R9=R1+R2;<br /> AS
'CONV+COUPE'<br />END
This message has been edited. Last edited by: <Mabel>,
Add the required fields as verb objects with noprints, then refer to them in your recap. If its the grand totals of these values, do a multi-verb request.
In the example I posted, there are two columns that are printed in the body of the report. For the line I want to add, I have to print two fields, how do I specify two fields in the RECAP statement?
I'm assuming that the recap line you need to print will include the total of sales and seats for the entire set of cars you've listed in the matrix.
While I don't have access to the 'CAR' file, I did do something like this using another table and if my assumptions regarding your requirements are correct, then the following should work for you.
TABLE FILE CAR SUM RETAIL_COST AS 'Retail' DEALER_COST AS 'Dealer' COMPUTE SALESSEATS/format = SALES + SEATS ; NOPRINT ACROSS COUNTRY AS ' ' FOR BODYTYPE 'CONVERTIBLE' AS 'CONVERTIBLE' LABEL R1 OVER 'COUPE' AS 'COUPE' LABEL R2 OVER 'HARDTOP' AS 'HARDTOP' LABEL R3 OVER 'ROADSTER' AS 'ROADSTER' LABEL R4 OVER 'SEDAN' AS 'SEDAN' LABEL R5 OVER 'SUV' AS 'SUV' LABEL R6 OVER 'VAN' AS 'VAN' LABEL R7 OVER BAR AS '-' OVER RECAP R9=R1+R2; AS 'CONV+COUPE' OVER RECAP R10(1,*,3) = R1(*+2) + R2(*+2) + R3(*+2) + ... R7(*+2) ; AS 'SALES+SEATS' END
If you compute a new field called SALESSEATS and NOPRINT it, you will be able to access it in the recap.
R10(1,*,3) means to produce the recap on the first column and then every third column thereafter. Since you have two display fields and one noprint field, the 'third' field applies.
Using *+2 will allow you access to the NOPRINTed field.
The problem with the FML recap syntax seems to be that you cannot specify more than one column for the row you're defining. What I'd like is two columns in the row, one for SEATS and one for SALES, going across the row.
Maybe I'm not seeing exactly what you're looking for.
I changed my query to look like the following:
TABLE FILE CAR SUM RETAIL_COST AS 'Retail' DEALER_COST AS 'Dealer' SALES NOPRINT SEATS NOPRINT ACROSS COUNTRY AS ' ' FOR BODYTYPE 'CONVERTIBLE' AS 'CONVERTIBLE' LABEL R1 OVER 'COUPE' AS 'COUPE' LABEL R2 OVER 'HARDTOP' AS 'HARDTOP' LABEL R3 OVER 'ROADSTER' AS 'ROADSTER' LABEL R4 OVER 'SEDAN' AS 'SEDAN' LABEL R5 OVER 'SUV' AS 'SUV' LABEL R6 OVER 'VAN' AS 'VAN' LABEL R7 OVER BAR AS '-' OVER RECAP R9=R1+R2; AS 'CONV+COUPE' OVER RECAP R10 = R1(*+2) + R2(*+2) + R3(*+2) + ... R7(*+2) ; AS 'SALES/SEATS' END
By doing this, you can get a recap line for the sales and seats in separate columns within the same row. The RECAP field R10 will accumulate all values for each BODYTYPE and will do it for each column, even the noprint columns - but the total will never appear for SALES and SEATS because they are in fact, noprinted.
The SALES number will appear under the RETAIL_COST column, SEATS will appear under the DEALER_COST column. Since both SALES and SEATS have been NOPRINTed, you won't see a RECAP column appearing for those columns. It should do this for every column in the across.
Think of it this way. R10(1) contains the SALES number, R10(2) contains the SEATS number, R10(3) contains the RETAIL_COST number and R10(4) contains the DEALER_COST number. The recap values for R10(3) and R10(4) will not appear in the RECAP line because of the NOPRINT on the SALES and SEATS columns.
Ken,<br /><br />Thank you for bearing with me, I think I'm getting the hang of FML.<br /><br />I've s implified the program to illustrate a problem that exists with the ACROSS: The RECAP does not seem to go across countries. I have removed the NOPRINT for illustrative purposes.<br /><br /> As well, I think I understand the syntax R10=R1(*+2). I imagine this refers to the 2nd column from the left. I'm not sure how FML figures out that SALES and SEATS are the 2nd columns.<br /> <br />
TABLE FILE CAR<br />
SUM<br />RETAIL_COST AS 'Retail'<br />DEALER_COST AS 'Dealer'<br />
SALES <br />SEATS <br />ACROSS
COUNTRY AS ' ' COLUMNS ENGLAND
AND ITALY<br />FOR BODYTYPE
<br />'CONVERTIBLE' AS
'CONVERTIBLE' LABEL R1 OVER<br />
'COUPE' AS 'COUPE' LABEL R2
OVER<br />'HARDTOP' AS 'HARDTOP'
LABEL R3 OVER<br />'ROADSTER'
AS 'ROADSTER' LABEL R4 OVER<br />
'SEDAN' AS 'SEDAN' LABEL R5 OVER
<br />RECAP R10 = R1(*+2) + R2(*+2) +
R3(*+2) + R4(*+2) + R5(*+2); AS
'SALES/SEATS'<br />END
Thank you.This message has been edited. Last edited by: <Mabel>,
You have it right. The syntax R10=R1(*+2) is just a displacement from the current column.
So, in the position under RETAIL_COST, the +2 means a displacement to the right -> 2 columns. Back in the SUM at the top of the request, the second column to the right from RETAIL_COST is SALES.
Then the second column to the right of DEALER_COST is SEATS. It's all about displacement. And yes, for the last 2 columns in the report (with the NOPRINT excluded) there are missing values because there are no columns 2 positions to the right of those....BUT, those are NOPRINTED in the other version anyway, so it's not a concern. I guess it depends on what you need the final report to look like.