Wow - I just read through this and all I can say is "yuck". I am brand new on WebFocus 7.6.10 and I am struggling with the issue mentioned above. Has there been any easier resolution since the last post here in 2007? Thank you!

Do an Advanced Search and type in SUBTOTAL TWO LINES. There are two pages of posts and I know that solutions have been posted in the past.

Many solutions have been posted, but there isn't a definitive one yet.

They've given us interactive graphs, they've given us Adobe Flex, they've given us Accordion reports, they've given us Google Search, they've given us maps, but they haven't given us a subtotal that always stays on one line.

I think I'll get a tea now.

The traffic on this thread should automatically prompt a "New Feature Request".

This message has been edited. Last edited by: Francis Mariani,

Good discussions happening here. Thank you.

I see the frustration Francis has.

I have 2 subtotals both BY-fields are NOPRINT fields. The lower level with subhead where the subtotal text was suppressed therefore it looks good on a single line with the help of a blank character as subtotal. The high level subtotal is with page-break, the BY-field is displayed on the page heading, hence column is NOPRINT, but I like to print 'Total {by-field value}' on the subtotal and definitely gives me 2-line total. The uglier thing is the background color on both lines with different lengths, and couldn't figure out a way to fix it. (output to PDF)

Hua

i just figured out something...
i solved a 'TOTAL ON A SEPARATE LINE' problem today, quite by accident, and then realized why!
I had done everything normal i could do... and still got 2 lines...banging head against wall...

then...
i looked at my ON TABLE SUBHEAD
which was at the top of my fex (first mistake)
and in the first line of my TABHEADING i had a field reference, hence a left caret.
As soon as i took the caret off, the 2-line subtotal went to 1 line.
Hmmm...on to something.
So, leaving the caret in place, i moved the entire ON TABLE SUBHEAD section fromt the top of the fex down to the very bottom before the style section, and BINGO works like a charm! subtotal now on 1 line, and i still have my TABHEADING bits.

It all sounds familiar...remembering the sage advice of the BigGiantHead to put RECAPs and the like down at the bottom, after the reporting matrix data has been collected, ...
so i'm willing to believe that my accidental discovery actually makes sense...

susannah, nice of you to pop by!

Interesting reading this. I'll have to give it a try.

Cheers,

quote:

Originally posted by susannah:
i just figured out something...
i solved a 'TOTAL ON A SEPARATE LINE' problem today, quite by accident, and then realized why!
I had done everything normal i could do... and still got 2 lines...banging head against wall...

then...
i looked at my ON TABLE SUBHEAD
which was at the top of my fex (first mistake)
and in the first line of my TABHEADING i had a field reference, hence a left caret.
As soon as i took the caret off, the 2-line subtotal went to 1 line.
Hmmm...on to something.
So, leaving the caret in place, i moved the entire ON TABLE SUBHEAD section fromt the top of the fex down to the very bottom before the style section, and BINGO works like a charm! subtotal now on 1 line, and i still have my TABHEADING bits.

It all sounds familiar...remembering the sage advice of the BigGiantHead to put RECAPs and the like down at the bottom, after the reporting matrix data has been collected, ...
so i'm willing to believe that my accidental discovery actually makes sense...

Thanks! That's a very useful tip.

I will truly celebrate when this gets to 17,000 views!

I'm celebratin'!

17932 views. Hopefully susannah's post is the definitive solution!

mgrackin's CAT example works fine, but what if you do not want to print CAR on the report?

quote:

So, leaving the caret in place, i moved the entire ON TABLE SUBHEAD section fromt the top of the fex down to the very bottom before the style section, and BINGO works like a charm! subtotal now on 1 line, and i still have my TABHEADING bits.

I've tried susannah's solution, here is my code:

TABLE FILE DBHOLD
PRINT
PROJECTNAME/A40 AS ''
INTHOURS/D10 AS ''
INTSPENT/D10M AS ''
CONHOURS/D10 AS ''
CONSPENT/D10M AS ''
AMOUNT/D10M AS ''
COMPUTE TOTHOURS/D10=INTHOURS+CONHOURS; AS ''
COMPUTE TOTSPENT/D10M=INTSPENT+CONSPENT+AMOUNT; AS ''
ON TABLE SUBTOTAL AS 'ABC'
END

There is NO SORTING column here, NOR any styling. But the SUBTOTAL label 'ABC', or and empty label if I omit the "AS 'ABC'" part, is still one row over the subtotal data. Did I get it wrong?

This message has been edited. Last edited by: bug,

I always thought this problem occurred when the report had a BY ... NOPRINT, but it occurs even with this minimal code:

TABLE FILE CAR
PRINT
BODYTYPE
SALES
ON TABLE SUBTOTAL
END

I hope the experts (and Gerry) are watching.

This message has been edited. Last edited by: Francis Mariani,

This works correctly because, as previously stated, the problem has something to do with the number of characters of the column BODYTYPE and the total line label. In my example, there is no label, therefore the label can fit, but changing the code to ON TABLE SUBTOTAL AS 'T', the width of BODYTYPE + 'T' is too long to fit in the column BODYTYPE, therefore two lines. It still makes no sense to me.

TABLE FILE CAR
PRINT
BODYTYPE
SALES
ON TABLE SUBTOTAL AS ''
END

After having multiple clients that insisted reports that the first BY field had a NOPRINT attached, I have pretty much stopped using SUBTOTAL / COLUMN-TOTAL et al. in most cases.

SUBFOOT sna ST. prefixes hav ebecome a regular solution that will usually statisfy the most... picky client.

I recently ran into an old report that was/is in need of some additional styling. A portion of which is the Total being on 2 lines. Most of our newer reports we use the ON TABLE SUBTOTAL BY ... logic but this report is using the ON TABLE SUMMARIZE AS 'TOTAL'. Of course it is on 2 lines. If I make it AS '' then it appears on one line but I lose the ability to identify it as the total. This creates the TYPE=GRANDTOTAL for the style portion of the code. I have 2 dynamic BY fields that get passed to this report. It can be one BY or 2 BY fields and I am not certain how to address the 2 line TOTAL issue for this report.
my .02 cents worth...Any ideas for this scenario???

Erin

Erin,

Among the 45 posts to this thread are several tips. My only suggestion would be to try some of them.

It's an ugly problem and I can't believe it hasn't been solved yet.

Now I'm ready for a tea Francis...lol... I will figure out a way to "hack" it to one line so the end-users are happy again. Hopefully this is being viewed in Dallas this week.

Erin, one of the quick answers is to use SUBFOOT instead of SUBTOTAL. You'll need to use the ST (subtotal) prefix on the column name (e.g. <ST.SALES). Then you'll have to add styling (HEADALIGN=BODY) for the subfoot elements to align wit the the report columns...

I have the same problems.

There is no sort BY phrase and Total numbers are not one 1 line.

PRINT
PRINT_PERIOD AS 'For the month of'
FULFILLED_COUNT/I6 AS 'Fulfilled Volume'
MAILED_COUNT/I6 AS 'Mailed Volume'
ON TABLE SUBTOTAL FULFILLED_COUNT MAILED_COUNT

This issue can generally be solved by Subfoot.. I generally do it by calculating the Total line seperately.. I know this is a roundabout but is useful when you need to get a weighted average in total line ...

TABLE FILE CAR
PRINT
BODYTYPE
SALES
ON TABLE HOLD AS DETAIL
END

TABLE FILE CAR
SUM
SALES
ON TABLE HOLD AS TOTAL
END

DEFINE FILE TOTAL
BODYTYPE = 'TOTAL';
END

TABLE FILE DETAIL
PRINT *
MORE
FILE TOTAL
END

RECAP
that's just what it's designed for

A pearl of wisdom from the disappeared!

i've been packed off to business objects he11

I may be next

Any new developments on this since 2011?

I burned through these recommendations and couldn't get a fix for my code because I can't get around a NOPRINT first sort field. The output needs to properly fit into excel cells for the end user to make calculations off of elsewhere - so I can't fake it with a subfoot line.

It's a stupid problem in core FOCUS.

Posting the code here might help in receiving suggestions for your particular issue.

This is my current "Solution" in which I print the total with no title. Changing it to -AS '.'- is enough to trigger the line-skip.

If I could control which cells the text and numbers go into with a subhead I could work around this.

-----

TABLE FILE RECORDS
SUM
STUDENTID NOPRINT
SUM
STUDENTID
COMPUTE PERCENT/F6.2% = ( C2 / C1 ) * 100; AS "Percent"
RANKED BY TOTAL HIGHEST 10 STUDENTID NOPRINT
BY HS_DESC AS 'Highschool'
ON TABLE COLUMN-TOTAL AS ' '
ON TABLE PCHOLD FORMAT XLSX FORMULA
END

CA, you may be able to do it with a HOLD file. This suggestion might be a beginning to a solution:

SET HOLDLIST=PRINTONLY
TABLE FILE CAR
SUM
COMPUTE PERCENTAGE/F6.2% = ( WIDTH / LENGTH ) * 100; AS 'PERCENT'
RANKED BY HIGHEST CAR
BY COUNTRY
ON TABLE HOLD AS H001
END
TABLE FILE H001
SUM
PERCENTAGE
BY LOWEST 3 RANK 
BY COUNTRY
ON TABLE SUB-TOTAL AS 'TOTAL'
ON TABLE PCHOLD FORMAT XLSX FORMULA
END

I have seen this issue resurface on the forum for almost 15 years.
Almost all of these issues, with regards to not being able to create a report with totals on one line,
revolve around two issues:


• Either the report does not utilize sorting represented by utilizing the "ByFields" syntax or the report only utilizes sorting utilizing the "ByFields NoPrint" syntax.

• Therefore the solution would consist of the following: Always utilize sorting with the By Field syntax in your requests and Never solely utilize the By Field NoPrint syntax in your Table requests.

• As we know the BYFIELD syntax both sorts the request and displays the field in question, whereas the BY FIELD NOPRINT syntax sorts the request and doesn't display the field in question.

• The examples below suggest that when one thinks of having subtotals or totals in their WebFOCUS reports one should also highly consider sorting the report and displaying at least one of the sorted fields.

Please review the following examples of code that I have observed on the forum regarding this dilemma along with samples of the solutions below:

*************************************************
-*Example1
*************************************************
TABLE FILE CAR
"Report without a BY statement"
PRINT BODYTYPE SALES
ON TABLE SUBTOTAL
END
-RUN
TABLE FILE CAR
"Report with only a BY NOPRINT statement"
PRINT BODYTYPE SALES
BY BODYTYPE NOPRINT
ON TABLE SET BYDISPLAY ON
ON TABLE SUBTOTAL
END
-RUN


*************************************************
-*Example1 Solutions
*************************************************
TABLE FILE CAR
""Report with a BY statement"
PRINT SALES
BY BODYTYPE
ON TABLE SET BYDISPLAY ON
ON TABLE SUBTOTAL
END
-RUN
DEFINE FILE CAR
COUNTER/I9C WITH BODYTYPE = (COUNTER+1);
END
TABLE FILE CAR
"Report with BY statements, temporary field “
“SUM data, maintain all unique rows, original data order “
SUM SALES
BY COUNTER NOPRINT
BY BODYTYPE
BY SALES NOPRINT
ON TABLE SUBTOTAL
END
-RUN


*************************************************
-Example2
*************************************************
TABLE FILE CAR
“Report with distinct BY NOPRINT with multiple SUM statements “
SUM
CAR NOPRINT
SUM CAR
COMPUTE PERCENTAG/F6,2%=(WIDTH/LENGTH)*100; AS 'PERCENT'
RANKED BY TOTAL HIGHEST 3 CAR NOPRINT
BY COUNTRY AS 'NationState'
ON TABLE COLUMN-TOTAL AS 'Totals'
-*ON TABLE PCHOLD FORMAT XLSX FORMULA
END
-RUN
*************************************************
-*Example 2 Solutions
*************************************************
TABLE FILE CAR
“Report with one SUM statement and a BY Statement “
SUM CAR
COMPUTE PERCENTAG/F6.2%=(WIDTH/LENGTH)*100; AS 'PERCENT'
RANKED BY TOTAL HIGHEST 3 CAR NOPRINT
BY COUNTRY AS 'NationState'
ON TABLE COLUMN-TOTAL AS 'Totals'
-*ON TABLE PCHOLD FORMAT XLSX FORMULA
END
-RUN

*************************************************
-*Example3
*************************************************
TABLE FILE CAR
"Report without a BY statement"
PRINT *
ON TABLE SUMMARIZE
END
-RUN
TABLE FILE CAR
"Report with only a BY NOPRINT statement"
PRINT COUNTRY CAR MODEL BODYTYPE SEATS DEALER_COST RETAIL_COST SALES LENGTH WIDTH
HEIGHT WEIGHT WHEELBASE FUEL_CAP BHP RPM MPG ACCEL
BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
BY BODYTYPE NOPRINT
ON TABLE RECOMPUTE
ON TABLE SET BYDISPLAY ON
END
-RUN
*************************************************
-*Example 3 Solutions
*************************************************
TABLE FILE CAR
"Report with a BY statement”
PRINT COUNTRY CAR MODEL BODYTYPE SEATS DEALER_COST RETAIL_COST SALES LENGTH WIDTH
HEIGHT WEIGHT WHEELBASE FUEL_CAP BHP RPM MPG ACCEL
BY COUNTRY
BY CAR NOPRINT
BY MODEL NOPRINT
BY BODYTYPE NOPRINT
ON TABLE RECOMPUTE
ON TABLE SET BYDISPLAY ON
END
-RUN


DEFINE FILE CAR
COUNTER/19CWITH COUNTRY = (COUNTER+1);
END
TABLE FILE CAR
"Report with BY statements, temporary field “
“SUM data, maintain all unique rows, original data order “
SUM COUNTRY CAR MODEL BODYTYPE SEATS DEALER_COST RETAIL_COST SALES LENGTH WIDTH HEIGHT WEIGHT WHEELBASE FUEL_CAP BHP RPM MPG ACCEL
BY COUNTER NOPRINT
BY COUNTRY
BY CAR NOPRINT
BY MODEL NOPRINT
BY BODYTYPE NOPRINT
ON TABLE RECOMPUTE
ON TABLE SET BYDISPLAY ON
END
-RUN
TABLE FILE CAR
"Report with BY statements, temporary field “
“SUM data, maintain all unique rows, original data order “
“Alpha columns interspersed with Metric columns”
SUM COUNTRY CAR SEATS DEALER_COST RETAIL_COST SALES MODEL BODYTYPE LENGTH WIDTH HEIGHT WEIGHT WHEELBASE FUEL_CAP BHP RPM MPG ACCEL
BY COUNTER NOPRINT
BY COUNTRY
BY CAR NOPRINT
BY MODEL NOPRINT
BY BODYTYPE NOPRINT
ON TABLE RECOMPUTE
ON TABLE SET BYDISPLAY ON
END
-RUN
*************************************************