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

Francis,

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.

Just a thought.

DHagen,

Thanks for the response.

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?

BTW, how's the weather in the 'burbs?

Francis,

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.

Does this help?

Ken

Ken, we're getting closer to what I need.

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.

RECAP R10(1,*,3) = R1(*+2) + R2(*+2) + R3(*+2) + ... R7(*+2) ; AS 'SALES+SEATS'

This command combines SALES and SEATS. I would like them as separate columns in the same row.

Any ideas?

Thank you.

Francis,

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.

Ken

Something I forgot to mention....

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

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

Francis,

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.

Enjoy the world of FML.

Ken

If the second column to the right from RETAIL_COST is SALES, then how does it work for SEATS?

As well, the RECAP only works for the first set of columns of the ACROSS statement.

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.

Ken

Ken, OK, thanks for the explanation.

Now, one last thing:

Why does this not work for the ACROSS?

This could be a bug. You're working in 5.3.2. I'm still working in 5.2.6 and am having no problems with the across at all.

Is anyone else having this problem in 5.3.2?

Ken