Hi all -

I don't even know what to search for to get what I need. So, I'm hoping it's a simple, "try this command" answer so I can do some researching. The problem seems simple enough, but the answer is eluding me. I need to create this report:

BYFIELD1       BYFIELD2      ACROSS_VALUE1      ACROSS_VALUE2       ACROSS_VALUE3  ... etc
XXXXXXXXXX     YYYYYYYYYY          100.00              50.00               25.00
               ZZZZZZZZZZ          100.00              50.00               50.00

SUBTOTAL FOR XXXXXXXXXX            200.00             100.00               75.00
COMPUTED FIELD FOR XXXXXXXXXX        4.00               2.00                1.50           

AAAAAAAAAA     YYYYYYYYYY          200.00              50.00              125.00
               ZZZZZZZZZZ           50.00             150.00               50.00

SUBTOTAL FOR AAAAAAAAAAAA          250.00             200.00              175.00
COMPUTED FIELD FOR BYFIELD1          5.00               4.00                3.50           

GRANDTOTAL                         450.00             300.00              250.00

The Computed field is the Subtotal for each Across Value of the By field divided by 50. How do I make that Computed Subtotal appear on each Across Value? Without the across I can do a Recap on a Subfoot, but not quite sure how to make an across recap...

Anatess,

This is pretty hokey but maybe you can make something out of it.

SET HOLDLIST=PRINTONLY
SET ASNAMES=ON
DEFINE FILE CAR
SORTFIELD1/I1=2;
QCAR/A16=' ';
END
TABLE FILE CAR
SUM 
COMPUTE SALES50/I6=SALES/50; AS SALES

BY COUNTRY
BY SORTFIELD
BY QCAR AS CAR
ACROSS BODYTYPE
ON TABLE HOLD AS SUMCAR FORMAT ALPHA

END
? HOLD SUMCAR
DEFINE FILE CAR
SORTFIELD1/I1=1;
END
TABLE FILE CAR
SUM SALES
BY COUNTRY 
BY SORTFIELD
BY CAR
ACROSS BODYTYPE
ON TABLE HOLD AS DTLCAR FORMAT ALPHA
END
? HOLD DTLCAR

TABLE FILE SUMCAR
PRINT E04 E05 E06 E07 E08
BY COUNTRY
BY SORTFIELD
BY CAR
ON SORTFIELD SUBTOTAL
WHEN SORTFIELD EQ 1
MORE 
FILE DTLCAR
END

Hi Ginny,
Man, I really thought it was just another keyword like ACROSS-CAP or something!
But, I see what you're trying to do. Brilliant! I should be able to implement something like it. Thanks.

Anatess

It is a nice solution from Ginny, but if the number of across fields change you need something extra to find out home many columns you have.

That can be done by reading the holdfile. How to do that is somewhere on FP, but I can't remember the correct coding.

Hi Frank,
I actually posted a question before on doing alternate colors on each across value that has another across within it. I got the logic to count how many "inside across" columns are within that first across field to determine how many columns to color a certain shade. So, I'm gonna use that same logic.

Anatess,
this looks to my eye like an FML application, straight away.
Does anyone else see it that way?
-s

Susannah

I thought of that too, but to be honest the FML might bring a lot of other problems. The hierarchy should be build careful but once you have done that it is a very strong method.

... cracking open an FML manual, reading like mad... wondering if there's ever a time that will come when I can say, I know everything there is to know about webfocus...

Anatess,

Whilst this looks like a suitable candidate for FML I can see that you will have problems with your textual requirements on the "subheadings". This is because it spans two columns (the two "by" fields).

You will also have to use the "unusual" syntax of putting the FOR [fieldname] before the BY [fieldname].

Try this to see what I mean -

APP PREPENDPATH IBISAMP
TABLE FILE GGSALES
SUM DOLLARS    AS ''
ACROSS PRODUCT AS ''
FOR REGION
'Northeast' LABEL NE OVER
'Southeast' LABEL SE OVER
'West'      LABEL WE OVER
'Midwest'   LABEL MW
BY ST          AS ''
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=9, $
ENDSTYLE
END

TABLE FILE GGSALES
SUM DOLLARS    AS ''
ACROSS PRODUCT AS ''
BY ST          AS ''
FOR REGION
'Northeast' LABEL NE OVER
'Southeast' LABEL SE OVER
'West'      LABEL WE OVER
'Midwest'   LABEL MW
ON TABLE SET HTMLCSS ON
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  GRID=OFF, SIZE=9, $
ENDSTYLE
END

The second report shows what happens with "normal" syntax when you put the BY before the FOR.

T

BTW, if you do this in the GUI make sure you set the FOR and BY fields correctly. If you try and move them afterwards you will find yourself with report 2 - always!! I've just tried this in the GUI and dragged the BY column (in Report and Source tabs) to where I wnated it, only to find the GUI moves the BY before the FOR before running the report!!

T

quote:

wondering if there's ever a time that will come when I can say, I know everything there is to know about webfocus...

I can save you some heart ache on that one, the answer is NO. I've been doing this for 18 years and I learn things from this forum almost every day. There will come a time when you get comfortable with it.