SUBTOTAL 1

[ozi2]

Hi,
Is there any way to prevent from focus to concatinate
my description to a SUBTOTAL field (AS ..) with the
field value.
For example: ON EMBASSY_REGION SUBTOTAL AS 'my desc'
result in "my desc EMBASSY_REGION ".
I would like to get only "my desc".

Regards,
OZ

 

[gizzy17]

You could use a subfoot as follows

ON EMBASSY_REGION SUBFOOT
&quot;My Desc <ST.FIELD1<ST.FIELD2 ...&quot;

 

[ozi2]

Thank's gizzy17.
However my report has across field and I need to display
the result in the appropriate places.
Is there a way to save my results (with the across and by and total and everything) in the memory(HOLD file
or something), and then edit values (in this case titles of
the SUBTOTAL rows) that applies to my conditions?
After editing the values I just need to print it from the
memory.


10'x a lot,
OZ

 

[gizzy17]

Not fun, not fun at all.

If there is an easier way to do this, I'm don't know about it and would love to here about it. I have solved this in the past by holding the file off and using the same technique I described above using the E references to the columns (E03, E04 etc). If you don't know how many columns are going to show for each request you can build a master for the fields by issuing a CHECK FILE MYHOLD HOLD or if you are using WebFOCUS you can do a table request against SYSTABLE where TABLE is your hold file and NAME is the field name of your across column. You would then need to read these results with dialog manager and build your subfoot line using dialog manager as well.

 

[MichaelHuber]

WHEW! ...This one actually WAS pretty fun!! 8^)

Basically, I gathered the detailed information in one request, and the subtotal information in another request. I added a NEWSORT field which is just the outer sort with an extra character concatenated to it. I used '{' and '}' because they probably wouldn't interfere with your values of OUTSORT. I then concatenated the two FOCUS HOLD files with USE (it has a similar effect to UNION in SQL). When you use USE, you have to make sure the two HOLD files have EXACTLY the same format (field names, order of fields, & field formats). By doing &quot;? HOLD FILENAME&quot;, I can see the master file descriptions created for each HOLD file (on the report output, if you do View->Source, you'll notice they're the same). In the subtotal request, you can change 'My Subtotal' to anything you want. If you need something larger than A16, you'll have to change the first hold file request a bit (BY CAR NOPRINT and somewhere do COMPUTE INSORT/A50 = CAR; ... you may even have to make BODYTYPE a NOPRINT just to keep the fields in the correct order). I've done USE on dissimilar FOCUS files, and I've gotten some crazy characters in the output!

I hope this points you down the right path.
-Michael

SET ASNAMES=ON
SET HOLDLIST=PRINTONLY
TABLE FILE CAR
SUM
COMPUTE NEWSORT/A11 = COUNTRY | '{';
BY COUNTRY AS OUTSORT
BY CAR AS INSORT
SUM
RETAIL_COST AS DATAVAL
BY COUNTRY AS OUTSORT
BY CAR AS INSORT
BY BODYTYPE AS ACRSORT
ON TABLE HOLD AS DETAILED FORMAT FOCUS
END
TABLE FILE CAR
SUM
COMPUTE INSORT/A16 = 'My Subtotal';
COMPUTE NEWSORT/A11 = COUNTRY | '}';
BY COUNTRY AS OUTSORT
SUM
RETAIL_COST AS DATAVAL
BY COUNTRY AS OUTSORT
BY BODYTYPE AS ACRSORT
ON TABLE HOLD AS SUMMARY FORMAT FOCUS
END
? HOLD DETAILED
? HOLD SUMMARY
USE
DETAILED.FOC AS DETAILED
SUMMARY.FOC AS DETAILED
END
TABLE FILE DETAILED
SUM DATAVAL AS ''
BY OUTSORT NOPRINT
BY NEWSORT NOPRINT
BY INSORT AS ''
ACROSS ACRSORT AS ''
ON OUTSORT SUBHEAD
&quot;Sum of Retail Cost for: <OUTSORT &quot;
END

 

[gizzy17]

So simple,

Take out the multiverb and it won't require as much sorting - thereby reducing runtime on a larger file.

Good job Michael

 

[MichaelHuber]

Ahh that's right...
I had the ACROSS in the HOLD files, and needed the multi-verb so I wouldn't have INSORT & NEWSORT repeated for each across value. Since I decided the ACROSS could be a BY in the HOLD files, we can take the multi-verb's out completely!

Good catch, gizzy!
Thanks,
Michael

ps - Here are the updated HOLD file requests, including allowing for larger subtotal descriptions (A20 instead of A16).

TABLE FILE CAR
SUM
BODYTYPE AS ACRSORT
COMPUTE INSORT/A20 = CAR;
COMPUTE NEWSORT/A11 = COUNTRY | '{';
RETAIL_COST AS DATAVAL
BY COUNTRY AS OUTSORT
BY CAR NOPRINT
BY BODYTYPE NOPRINT
ON TABLE HOLD AS DETAILED FORMAT FOCUS
END
TABLE FILE CAR
SUM
COMPUTE INSORT/A20 = 'My Subtotal';
COMPUTE NEWSORT/A11 = COUNTRY | '}';
RETAIL_COST AS DATAVAL
BY COUNTRY AS OUTSORT
BY BODYTYPE AS ACRSORT
ON TABLE HOLD AS SUMMARY FORMAT FOCUS
END

 

[ozi2]

10'x a lot guys.
I won't be able to check it until next week
but it seems like a very good idea.


Regards,
OZ

 

[MichaelHuber]

As usual, there are many ways to solve a problem.

If you include a WHERE TOTAL (even if it's always true), then your EMBASSY_REGION values won't display unless PRINTPLUS is ON. (It's OFF by default). This seems like a more direct approach to your problem... no HOLD files or anything. 8^)

SET PRINTPLUS=OFF
TABLE FILE CAR
SUM RETAIL_COST AS ''
BY COUNTRY NOPRINT
BY CAR AS ''
ACROSS BODYTYPE AS ''
WHERE TOTAL 1 EQ 1;
ON COUNTRY SUBTOTAL
RETAIL_COST
AS 'My Subtotal'
ON COUNTRY SUBHEAD
&quot;Sum of Retail Cost for: <COUNTRY &quot;
END

Good luck!
-Michael

 

[focwizard]

Michael,

Interesting. You took advantage of what I always thought of as a bug, to get what you wanted.

Here's how I'd do it. I'd DEFINE a field as ' ', and sort on that WITHIN the desired sort break field. Since the only time we'll break on the DEFINEd field (constant), is when a higher sort changes, a SUBTOTAL on the DEFINEd field gives what you want. Here's sample code to try:

DEFINE FILE CAR                    
BLANK/A1=' ';                      
END                                
TABLE FILE CAR                     
PRINT RCOST BY COUNTRY BY BLANK    
ON BLANK SUBTOTAL AS 'SUBTOTAL'    
END

You get the value with the literal 'SUBTOTAL', but, since it has a value of blank, it doesn't display.

 

[WFGuru]

Try this.

DEFINE FILE CAR
BOD2/A40 WITH BODY= 'Body Total:';
END
TABLE FILE CAR
PRINT RCOST
ACROSS COUNTRY
BY BODY
BY BOD2 NOPRINT SUBTOTAL AS ''
END