Focal Point Banner


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.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] multi-line sort by AGAIN

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[CLOSED] multi-line sort by AGAIN
 Login/Join
 
Silver Member
posted
I know this has been discussed in a previous thread see below, but I've tried all of the options discussed and I have a unique situation. I have a report where I need to sort by a column that doesn't display and then subtotal on it. The by value that is being selected is rather short. I'm also not showing any sort bys - NOPRINT.

Is there a way around this without showing a sort by?

Previous Thread

Thanks,
shelzalee.

This message has been edited. Last edited by: <Kathryn Henning>,


7.7 Windows HTML, PDF, Excel
 
Posts: 46 | Registered: November 08, 2011Report This Post
Expert
posted Hide Post
Sure there is... For starters, someting for you to check out: BY ThisField NOPRINT ... oN ThisField SUBHEAD / "...



   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Current: WebFOCUS Administrator at FIS Worldpay | 8204, 8206
 
Posts: 3132 | Location: Tennessee, Nashville area | Registered: February 23, 2005Report This Post
Silver Member
posted Hide Post
I don't want to use a subhead or subfoot. I need totals to line up under the columns I'm totalling. Not sure if that is what you are referring to.


7.7 Windows HTML, PDF, Excel
 
Posts: 46 | Registered: November 08, 2011Report This Post
Member
posted Hide Post
Sample with BY field NOPRIT, ON field SUBTOTAL AS ''
TABLE FILE CAR
SUM 
     DEALER_COST
     RETAIL_COST
     SALES
BY  LOWEST COUNTRY NOPRINT 
BY  LOWEST CAR
BY  LOWEST MODEL     
ON COUNTRY SUBTOTAL AS ''
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
ENDSTYLE
END


Sample with subheading, aligned with the fields.
TABLE FILE CAR
SUM 
     DEALER_COST
     RETAIL_COST
     SALES
BY  LOWEST COUNTRY NOPRINT 
BY  LOWEST CAR NOPRINT 
BY  LOWEST MODEL NOPRINT 
     
ON CAR SUBFOOT
"<ST.DEALER_COST<ST.RETAIL_COST<ST.SALES"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=SUBFOOT,
     BY=2,
     HEADALIGN=BODY,
$
TYPE=SUBFOOT,
     BY=2,
     LINE=1,
     OBJECT=FIELD,
     ITEM=1,
     COLSPAN=1,
     JUSTIFY=RIGHT,
$
TYPE=SUBFOOT,
     BY=2,
     LINE=1,
     OBJECT=FIELD,
     ITEM=2,
     COLSPAN=1,
     JUSTIFY=RIGHT,
$
TYPE=SUBFOOT,
     BY=2,
     LINE=1,
     OBJECT=FIELD,
     ITEM=3,
     COLSPAN=1,
     JUSTIFY=RIGHT,
$
ENDSTYLE
END


Release: DevStudio 7703
OS: Win7
Outputs: HTML, PDF, EXCEL

 
Posts: 8 | Registered: November 29, 2011Report This Post
Virtuoso
posted Hide Post
Did you try something on the car database?

If you did, and country is the subtotal break, you can create a dummy car named total and add that with a hold file to your original dataset.

There are many examples for this available.

And of course there is mcguyver

See if this make sense




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Report This Post
Silver Member
posted Hide Post
In reference to DBADiaz examples, when I commented out the second and third by sorts on the first example, the total went to two lines again. When I added the word "total" in front of the subfooter lines on the second example, the totals no longer lined up which leads me to my original conclusion a subfooter will not work for me.

In reference to FrankDutch's comment, not sure where this is going. If I were to create a dummy record with a larger value for the first hidden sort, wouldn't this record show in my report? Seems a little ridiculous that I have to add in a dummy record to prevent the two line totals from displaying.

Maybe I'm missing something here, but I don't see a real solution to the issue. I've gotten the user to agree it wasn't a big deal, but I'm still not clear on a real workaround.

Thanks for replying.... maybe I'm just not understanding something here.

shelzalee.


7.7 Windows HTML, PDF, Excel
 
Posts: 46 | Registered: November 08, 2011Report This Post
Virtuoso
posted Hide Post
Try this:

SET HOLDLIST = PRINTONLY
SET ASNAMES  = ON
-*
TABLE FILE CAR
 SUM
  COMPUTE BLANK_COL/A1 = ''; AS ''
  DEALER_COST
  RETAIL_COST
  SALES
 BY LOWEST COUNTRY NOPRINT 
 BY LOWEST CAR     NOPRINT 
 BY LOWEST MODEL   NOPRINT 
 ON CAR SUBFOOT
  "Total<ST.DEALER_COST<ST.RETAIL_COST<ST.SALES"
 ON TABLE SET PAGE-NUM NOLEAD 
 ON TABLE NOTOTAL
 ON TABLE PCHOLD FORMAT HTML
 ON TABLE SET HTMLCSS ON
 ON TABLE SET STYLE *
  INCLUDE = endeflt ,$
  TYPE=SUBFOOT, HEADALIGN=BODY ,$
  TYPE=SUBFOOT, OBJECT=FIELD, JUSTIFY=RIGHT ,$
 ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,


WebFOCUS 7.7.05
 
Posts: 1213 | Location: Seattle, Washington - USA | Registered: October 22, 2007Report This Post
Silver Member
posted Hide Post
If you prefer to use subtotal (sub-total, summarize, etc), you can try "hiding" the column used for counts with styling the text to match the background color. Here is an example where the rows alternate background colors.

 -* A way to count rows; relies on terrible styling trick

-* For subtotals, summarize, recaps
-* you can't CNT. alpha data, but you can count fields containing numeric data
-* You also can't show info about hidden fields in the sub-total/summarize/recap lines

-* Usual workaround is to use a subfoot, and tally a hidden field, and then position items, however then you have to position them.

-* "hiding" a column of numbers to count may allow for more versatile reporting. Note--this example shows doing so when your rows
-* alternate background colors. If you don't  alternate, you only need one line in the STYLE, and you do not need to use an IF with the INC field.


TABLE FILE CAR
SUM 
COMPUTE INC/I1=IF LAST INC EQ 1 THEN 0 ELSE 1; AS ' '
     DEALER_COST
     RETAIL_COST
     SALES
BY  LOWEST COUNTRY NOPRINT 
BY  LOWEST CAR
BY  LOWEST MODEL     NOPRINT
ON COUNTRY SUBTOTAL CNT. INC SUM. DEALER_COST RETAIL_COST SALES AS ''
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=DATA, COLUMN=INC, COLOR=RGB(235 235 240), WHEN=INC EQ 0,$
TYPE=DATA, COLUMN=INC, COLOR=WHITE, WHEN=INC NE 0,$
ENDSTYLE
END
 


WF: WebFocus 7.7.03
Data: Oracle, MSSQL, DB2
OS: Windows
Output: HTML/AHTML,PDF,EXL2K FORMULA, COMT
 
Posts: 43 | Registered: November 21, 2011Report This Post
Master
posted Hide Post
Shelzalee,

Take a second look at what DBADiaz provided you. I think you will find that it is exactly what you need. Take a closer look at how he did the subheading example. When you look at the style sheet you will see that he has positioned the fields in the subfoot to align with specific columns according to by field 2. It is by controlling the alignment in the style sheet that will give you what you need. I made some slight adjustments to put the total in front. Look at how I adjusted it with a blank field to give space to the value total in the subfoot. I think if you play with this example a bit you will be able to achieve what you want. Also if you are still having trouble, it might be helpful to post your code so we can give better and more constructive examples to you.

DEFINE FILE CAR
DUMMY/A20='';
END
TABLE FILE CAR
SUM 
     DEALER_COST
     RETAIL_COST
     SALES
BY DUMMY AS ''
BY  LOWEST COUNTRY NOPRINT 
BY  LOWEST CAR NOPRINT 
BY  LOWEST MODEL NOPRINT 
     
ON CAR SUBFOOT
"<+0 Total <ST.DEALER_COST<ST.RETAIL_COST<ST.SALES"
ON TABLE SET PAGE-NUM NOLEAD 
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
     INCLUDE = endeflt,
$
TYPE=SUBFOOT,
     BY=3,
     HEADALIGN=BODY,
$
TYPE=SUBFOOT,
     BY=3,
     LINE=1,
     OBJECT=FIELD,
     ITEM=1,
     COLSPAN=1,
     JUSTIFY=RIGHT,
$
TYPE=SUBFOOT,
     BY=3,
     LINE=1,
     OBJECT=FIELD,
     ITEM=2,
     COLSPAN=1,
     JUSTIFY=RIGHT,
$
TYPE=SUBFOOT,
     BY=3,
     LINE=1,
     OBJECT=FIELD,
     ITEM=3,
     COLSPAN=1,
     JUSTIFY=RIGHT,
$
TYPE=SUBFOOT,
     BY=3,
     LINE=1,
     OBJECT=FIELD,
     ITEM=4,
     COLSPAN=1,
     JUSTIFY=RIGHT,
$
ENDSTYLE
END
  


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
Nice report Eric, but I don't see the value in all those NOPRINTS - the totals line up, but what they are actually totals of is unclear at best. (I do understand that you are just demonstrating a principle here of course).


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Silver Member
posted Hide Post
Eric,

I'm playing around with the code you posted. What does the BY do in the SUBFOOT style area? I haven't seen that before.

Thanks,
Shelly Lee


7.7 Windows HTML, PDF, Excel
 
Posts: 46 | Registered: November 08, 2011Report This Post
Master
posted Hide Post
Shelzalee,

I pulled this straight out of the help file

quote:
Syntax: How to Identify an Entire Total or Subtotal Row
TYPE=type, [BY=sortcolumn]
where:

type
Identifies a subtotal or total. Select from:

GRANDTOTAL which is a grand total (generated by COLUMN-TOTAL, SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE).

SUBTOTAL which is a subtotal (generated by SUBTOTAL, SUB-TOTAL, RECOMPUTE, or SUMMARIZE).

RECAP which is a subtotal calculation (generated by ON sortfield RECAP or ON sortfield COMPUTE).

BY
When there are several subtotal commands, each associated with a different vertical sort (BY) column, this enables you to identify which of the subtotal commands you wish to format.

sortcolumn
Specifies the vertical sort (BY) column associated with one of the several subtotal in the report commands. Use the field name to identify the sort column.



basically, it says that this section of code specifically refers to the subfoot associated with the 3rd by column. In this case it is Car (this is why I adjusted it from 2 to 3 when I added the by field dummy). If you wanted it to apply to all subfoots, you wouldn't include that line.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Silver Member
posted Hide Post
I had missed the dummy BY. The dummy By option is not viable regardless of how well it is hidden. When exported to Excel which we do on every report. I don't want the first column to be an empty row that just contains the word "Total" for every line that totals.


7.7 Windows HTML, PDF, Excel
 
Posts: 46 | Registered: November 08, 2011Report This Post
Master
posted Hide Post
I'm not sure exactly what you're end goal is here, but if you don't want to have total as its own field, then you will have to concatenate it into the first value of your subtotal, or make it apart of another field. In that case you will have to probably hold your subtotals with sort values, use a more to join the two files together and then sort it accordingly.

Here is an example of how that would work
 DEFINE FILE CAR 
SORT/I8=1;
END
TABLE FILE CAR 
SUM DEALER_COST
	RETAIL_COST
BY COUNTRY
BY CAR
BY SORT 
BY MODEL 
ON TABLE HOLD AS DTLS
END
DEFINE FILE CAR
SORT/I8=2;
MODEL/A24='Total';
END
TABLE FILE CAR
SUM DEALER_COST
	RETAIL_COST
BY COUNTRY
BY CAR
BY SORT
BY MODEL
ON TABLE HOLD AS SBTTL
END

TABLE FILE DTLS
SUM DEALER_COST
	RETAIL_COST
BY COUNTRY
BY CAR
BY SORT 
BY MODEL
ON TABLE HOLD AS TGTHR
MORE
FILE SBTTL
END

TABLE FILE TGTHR
SUM DEALER_COST
	RETAIL_COST
BY COUNTRY
BY CAR
BY SORT 
BY MODEL 
END

 


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
George,

Ya, I just grabbed DBADiaz's code. I assumed this is how he interpreted what shelzalee was trying to accomplish.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Silver Member
posted Hide Post
I don't want to be asked to mark this as solved since I don’t believe there is a good solution.

The question was how to prevent the text or whatever wording selected for the “total” to not appear on a line above the line with the actual totals. I now understand WebFOCUS does this because it is trying to prevent the text from displaying over possible totals; however, this would not have been an issue in my case since no columns were totaled on the far left and there would have been plenty of space for whatever text I wanted without over-writing a total. Creating a blank BY printed may be a viable work around for some instances; however, when exported to Excel that blank column will show as the first column in the report and that is not what I want. It would be nice if there was some setting that allowed the report writer to control if the total appeared on the line above the totals or on the line with the totals.
 
Posts: 46 | Registered: November 08, 2011Report This Post
Master
posted Hide Post
Shelzalee,

We have shown you how to do this with a subfoot, we have shown you how to do this utilizing more, we have shown you how to do this creating a blank field, the post you reference explains exactly why this situation occurs (total length of field vs length of subtotal field). Please post either the code that you have that isn't working, OR recreate the situation with one of the sample tables. At this point you haven't described anything that hasn't been resolved in these posts. I understand that you may feel that your situation is unique, but I doubt that. We don't ask for sample code to be difficult, we ask for sample code so that we can better help you. If you are really serious about resolving your question, please post some sample code so that we can help you better.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Silver Member
posted Hide Post
If you really want to see the code here you go. I tried to cut it down by copying out the subtotal and the table file area starting with the define above the table file. I don't think this will help as it is not a simple car table file example, but even the simple examples involved a dummy BY which I do no want as it will create a blank column in excel with the text for the total area displaying in it only.

 

-SET &SORTBY1_TITLE 	= &SB1;
-SET &SB1      		= DECODE &SB1
-							('CUST' 	  	  'D_CUSTNUM'
-							 'BOL'			  'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER'
-							 'LOC'			  'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME'
-							 'XREF'		  	  'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM'
-							 'PART'		  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER'
-							 'CUSTPO'	  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER'
-							 'SHOP'           'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER'
-						 	 ELSE 'FOC_NONE');


-SET &ST1      		= IF ('&ST1.EVAL' EQ 'Y' AND &SB1 NE FOC_NONE)
-						  	 THEN ' ON ' | &SB1 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL '  | &SORTBY1_TITLE | ''''
-						  	 ELSE '';
-SET &SB1    		= IF &SB1 NE FOC_NONE
-							 THEN 'BY ' | &SB1 | ' AS ' | &SB1 | ' NOPRINT'
-							 ELSE '';


-SET &SORTBY2_TITLE 	= &SB2;
-SET &SB2     		= DECODE &SB2
-							('CUST' 	  	  'D_CUSTNUM'
-							 'BOL'			  'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER'
-							 'LOC'			  'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME'
-							 'XREF'		  	  'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM'
-							 'PART'		  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER'
-							 'CUSTPO'	  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER'
-							 'SHOP'           'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER'
-							ELSE 'FOC_NONE');

-SET &ST2      		= IF ('&ST2.EVAL' EQ 'Y' AND &SB2 NE FOC_NONE)
-							 THEN 'ON ' | &SB2 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL '  | &SORTBY2_TITLE | ''''
-							 ELSE '';
-SET &SB2     		= IF &SB2 NE FOC_NONE
-							 THEN 'BY ' | &SB2 | ' NOPRINT'
-							 ELSE '';

-SET &SORTBY3_TITLE 	= &SB3;
-SET &SB3     		= DECODE &SB3
-							('CUST' 	  	  'D_CUSTNUM'
-							 'BOL'			  'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER'
-							 'LOC'			  'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME'
-							 'XREF'		  	  'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM'
-							 'PART'		  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER'
-							 'CUSTPO'	  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER'
-							 'SHOP'           'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER'
-						 	 ELSE 'FOC_NONE');

-SET &ST3      		= IF ('&ST3.EVAL' EQ 'Y' AND &SB3 NE FOC_NONE)
-							 THEN 'ON ' | &SB3 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL '  | &SORTBY3_TITLE | ''''
-							 ELSE '';
-SET &SB3    		= IF &SB3 NE FOC_NONE
-							  THEN 'BY ' | &SB3 | ' NOPRINT'
-						 	 ELSE '';

-TYPE &ST3
-TYPE &SB3

-SET &SORTBY4_TITLE 	= &SB4;
-SET &SB4     		= DECODE &SB4
-							('CUST' 	  	  'D_CUSTNUM'
-							 'BOL'			  'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER'
-							 'LOC'			  'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME'
-							 'XREF'		  	  'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM'
-							 'PART'		  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER'
-							 'CUSTPO'	  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER'
-							 'SHOP'           'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER'
-						 	 ELSE 'FOC_NONE');

-SET &ST4      		= IF ('&ST4.EVAL' EQ 'Y' AND &SB4 NE FOC_NONE)
-							 THEN 'ON ' | &SB4 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL '  | &SORTBY4_TITLE | ''''
-							 ELSE '';
-SET &SB4     		= IF &SB4 NE FOC_NONE
-							 THEN 'BY ' | &SB4 | ' NOPRINT'
-							 ELSE '';
-TYPE &SB4
-SET &SORTBY5_TITLE 	= &SB5;
-TYPE &SB5
-SET &SB5     		=  DECODE &SB5
-							('CUST' 	  	  'D_CUSTNUM'
-							 'BOL'			  'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER'
-							 'LOC'			  'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME'
-							 'XREF'		  	  'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM'
-							 'PART'		  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER'
-							 'CUSTPO'	  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER'
-							 'SHOP'           'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER'
-							 ELSE 'FOC_NONE');

-SET &ST5  		    = IF ('&ST5.EVAL' EQ 'Y' AND &SB5 NE FOC_NONE)
-						  	 THEN 'ON ' | &SB5 | ' ' | 'SUBTOTAL DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT ' | &SUBTOT_PAID | ' ' | &SUBTOT_INV | ' AS ' | '''' | '*TOTAL '  | &SORTBY5_TITLE | ''''
-						   	 ELSE '';

-SET &SB5     		= IF &SB5 NE FOC_NONE
-							 THEN 'BY ' | &SB5 | ' NOPRINT'
-							 ELSE '';


DEFINE FILE DM_CUSTOMER_INVOICE
-MRNOEDIT -INCLUDE common_report_export_icons.fex

DISPALL/A500			= TRIM('T', '&HD_ALL', &HD_ALL.LENGTH , '; ', 2, DISPALL);
DISPCUST/A4100			= '' || '&HD_CUST' || '';
DISPXREF/A4100			= '' || '&HD_XREF' || '';
DISPPO/A200 			= '&HD_CUSTPO';
DISPPART/A20			= '&HD_PART';
DISPBOL/A200			= '&HD_BOL';
DISPLOC/A4100			= '&HD_LOC';
D_LOCNUM/A2	    	 	= TRIM('L', EDIT(DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNUMBER, '$$99'), 2, '0', 1, D_LOCNUM);
D_LOCNAME/A200			= TRIM('L', DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME, 200, ' ', 1, D_LOCNAME);
D_LOCNAMENUM/A500		= D_LOCNAME || '-' || D_LOCNUM;
D_CUSTNUM/A7   	 		= TRIM('L', EDIT(DM_CUSTOMER_INVOICE.DIM_CUSTOMER.CUSTOMERNUMBER), 7, '0', 1, D_CUSTNUM);
D_PRODNUM/A6   	 		= TRIM('L', EDIT(DM_CUSTOMER_INVOICE.DIM_PRODUCTION.PRODUCTIONNUMBER), 6, '0', 1, D_PRODNUM);
D_SHOPNUM/A6   	 		= TRIM('L', EDIT(DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER), 6, '0', 1, D_SHOPNUM);
D_GAUGERANGE/A15 		= PTOA(DM_CUSTOMER_INVOICE.DIM_SHOP.ACTUALGUAGE, '(P7.4)', 'A7') || '/' || PTOA (DM_CUSTOMER_INVOICE.DIM_SHOP.RANGE, '(P7.4)', 'A7');
D_ACTUALWIDTH/A9		= PTOA(DM_CUSTOMER_INVOICE.DIM_SHOP.ACTUALWIDTH, '(P9.4)', 'A9');
D_INVOICENUMBER/A8  	= TRIM('L', EDIT(DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICENUMBER), 8, '0', 1, D_INVOICENUMBER);
D_INVOICEAMOUNT/A13		= PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_FUNC, '(P13.2)', 'A13');
D_FREIGHT/A9			= PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.FREIGHTAMOUNT, '(P9.2)', 'A9');
D_PAIDAMOUNT/A11		= PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.PAIDAMOUNT_FUNC, '(P11.2)', 'A11');
D_PRICECWEIGHT/A12		= PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_FUNC/DM_CUSTOMER_INVOICE.FACT_INVOICE.SUMFGWGT*100, '(P11.2)', 'A11');
D_SURCHARGE/A13			= PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.SURCHARGEAMT, '(P11.2)', 'A13');
D_SALESCLASS/A2			= TRIM('T', PTOA(DM_CUSTOMER_INVOICE.DIM_SALES_CLASS.SALESCLASSIFICATION, '(P2.0)', 'A2'), 2, '.', 1, D_SALESCLASS);
D_XREFNUM/A7			= TRIM('T', PTOA(DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM, '(P7.0)', 'A7'), 7, '.', 1, D_XREFNUM);
D_HST/A13 				= PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.CADTAX_FUNC, '(P11.2)', 'A13');
D_MATERIALCOST/A13		= PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.MATERIALCOST, '(P11.2C)', 'A13');
D_LENGTH/A16			= PTOA(DM_CUSTOMER_INVOICE.FACT_INVOICE.LENGTH, '(P14.2)', 'A16');
BLANK/A200				=' ';
END
TABLE FILE DM_CUSTOMER_INVOICE
HEADING
"Steel Technologies "
"Customer Invoice &HD_VIEW Sales Report"
"&HD_RUNDT.EVAL"
"_____________________________________"
"For &HD_INVSDT - &HD_INVEDT"
&CMNT_ALL "Selected ALL for: <+0><DISPALL"
&CMNT_CUST "Customer(s): <+0><DISPCUST"
&CMNT_XREF "Master X-Ref(s): <+0><DISPXREF"
&CMNT_LOC "Location(s): <+0><DISPLOC"
&CMNT_CUSTPO "PO: <+0><DISPPO"
&CMNT_PART "Part: <+0><DISPPART"
&CMNT_BOL "BOL: <+0><DISPBOL"
SUM
	 COMPUTE C_COUNTER/I4 =
	 IF D_INVOICENUMBER NE LAST D_INVOICENUMBER THEN 0 ELSE 1;   						  NOPRINT
	 D_CUSTNUM													  			AS 'Cust#'
	 D_LOCNAMENUM															AS 'Loc#'
	 DM_CUSTOMER_INVOICE.DIM_CUSTOMER.SHIPTOCUSTNAME			  			AS 'Ship to Cust Name'
	 DM_CUSTOMER_INVOICE.DIM_CUSTOMER.SOLDTOCUSTNAME						AS 'Sold To Name'		 &NUCORHIDE
	 DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER				  			AS 'CustPO#'
	 D_PRODNUM													 			AS 'Prod#'			  	  &SUMMARYSHOW
	 D_SHOPNUM													  			AS 'Shop#'
	 DM_CUSTOMER_INVOICE.DIM_MATERIAL_TYPE.MATERIALTYPE 		  			AS 'Matl Type'
	 DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER					  			AS 'Part#'
	 D_GAUGERANGE												 		 	AS 'GAUGE/RANGE'		  &NUCORHIDE
	 D_ACTUALWIDTH												  			AS 'Width'			  	  &NUCORHIDE
	 D_LENGTH																AS 'Length'				 &SUMMARYSHOW
	 COMPUTE C_LENGTH/P14.2 =
	 IF C_COUNTER EQ 0 THEN
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.LENGTH
	 ELSE 0;																AS 'Length'				 &DETAILSHOW
	 DM_CUSTOMER_INVOICE.DIM_PRODSHOP.MACHINEPROCESSORDER 		  			AS 'Machine Codes'	  	  &NUCORSHOW
	 DM_CUSTOMER_INVOICE.DIM_MILLCOIL.MILLCOILNUMBER			  			AS 'Coil#'			  	  &SUMMARYHIDE
	 DM_CUSTOMER_INVOICE.DIM_FG_TAG.SERIALNUMBER				  			AS 'Tag#'			  	  &SUMMARYHIDE
	 DM_CUSTOMER_INVOICE.FACT_FG.WEIGHT/D15						  			AS 'Wgt'
	 DM_CUSTOMER_INVOICE.DIM_SHIPPEDDATE.FULL_DATE/MDYY		 	  			AS 'Shp Date'
     DM_CUSTOMER_INVOICE.DIM_INVOICEDATE.FULL_DATE/MDYY			  			AS 'Inv Date'
     DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER						  			AS 'BOL#'
	 D_INVOICENUMBER											  			AS 'Invc#'
	 D_MATERIALCOST															AS 'Material Cost'		 &SUMMARYSHOW
	 COMPUTE C_MATERIALCOST/P11.2C =
	 IF C_COUNTER EQ 0 THEN
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.MATERIALCOST
	 ELSE 0; 																AS 'Material Cost'		 &DETAILSHOW
	 COMPUTE C_INVAMT/D15.2 = IF C_COUNTER EQ 0 THEN
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_FUNC
	 ELSE 0; 													  			AS 'Inv Amt'			  &SUMMARYHIDE
	 COMPUTE C_INVAMTSUMMARY/D15.2 = EDIT(D_INVOICEAMOUNT);		  			AS 'Inv Amt'			  &SUMMARYSHOW
	 D_PRICECWEIGHT												  			AS 'Price (CWT)'		  &SUMMARYSHOW
	 COMPUTE C_PRICECWEIGHT/D11.2 =
	 IF C_COUNTER EQ 0 THEN
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_FUNC/
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.SUMFGWGT*100
	 ELSE 0;													  			AS 'Price (CWT)'		 &SUMMARYHIDE
	 COMPUTE C_FREIGHT/P9.2 = IF C_COUNTER EQ 0 THEN
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.FREIGHTAMOUNT
	 ELSE 0; 													  			AS 'Frght'			  	 &SUMMARYHIDE
	 D_FREIGHT													  			AS 'Frght'			  	 &SUMMARYSHOW
	 DM_CUSTOMER_INVOICE.FACT_FG.SCRAPWEIGHT 					  			AS 'Scrap Weight'		 &NUCORSHOW
	 COMPUTE C_TOTALSCRAP/P11.2 = IF C_COUNTER EQ 0 THEN
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.TOTALSCRAP ELSE 0;			  		AS 'Scrap Credit Amt'	 &NUCORSHOW
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.SURCHARGEAMT			 	  			AS 'Surcharge'		  	 &DETAILSHOW
	 D_SURCHARGE												  			AS 'Surcharge'		  	 &SUMMARYSHOW
	 DM_CUSTOMER_INVOICE.FACT_FG.PIECES/D9C					  				AS 'Pieces'			  	 &NUCORHIDE
	 COMPUTE C_PAIDAMOUNT/P11C =
	 IF C_COUNTER EQ 0 THEN
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.PAIDAMOUNT_FUNC
	 ELSE 0; 										  		 	 			AS 'Paid Amt'			 &SUMMARYHIDE
	 COMPUTE C_PAIDAMTSUMMARY/D11.2C = EDIT(D_PAIDAMOUNT);		 			AS 'Paid Amt'			 &SUMMARYSHOW
	 COMPUTE C_PAYMENTDATE/A8MDYY =
	 DM_CUSTOMER_INVOICE.DIM_PAIDDATE.FULL_DATE;										  			 NOPRINT
	 COMPUTE C_PAYMENTDATEALPHA/A10 =
	 EDIT(C_PAYMENTDATE, '99/99/9999'); 												  			 NOPRINT
	 COMPUTE C_PAYMENTDATEFINAL/A10 =
	 IF C_PAYMENTDATEALPHA EQ
	 '  /  /    ' THEN '##/##/####' ELSE
	 C_PAYMENTDATEALPHA;	 	                           		  			AS 'Paid Date';
	 COMPUTE C_PAYMENTSTATUS/A3 =
	 IF DM_CUSTOMER_INVOICE.FACT_INVOICE.PAYMENTSTATUS EQ
	 MISSING OR DM_CUSTOMER_INVOICE.FACT_INVOICE.PAYMENTSTATUS
	 EQ 'P' OR 'Z' THEN 'No'
	 ELSE 'Yes'; 												  			AS 'Opn'
	 COMPUTE C_HST/P11.2 =
	 IF C_COUNTER EQ 0 THEN
	 DM_CUSTOMER_INVOICE.FACT_INVOICE.CADTAX_FUNC
	 ELSE 0; 																AS 'HST'				 &DETAILSHOW
	 D_HST																	AS 'HST'				 &SUMMARYSHOW
	 D_XREFNUM																AS 'XREFNUM'			 &NUCORHIDE
	 D_SALESCLASS															AS 'Sales Class'		 &NUCORHIDE



&SB1
&SB2
&SB3
&SB4
&SB5
BY DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNUMBER NOPRINT
BY DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICENUMBER NOPRINT
&SUMMARYHIDECMT BY DM_CUSTOMER_INVOICE.DIM_FG_TAG.SERIALNUMBER NOPRINT
-*END BY COMMANDS
-*START ON SUBTOTAL COMMANDS
&ST1
&ST2
&ST3
&ST4
&ST5
-*END ON SUBTOTAL COMMANDS
-*START WHERE STATEMENTS
WHERE DM_CUSTOMER_INVOICE.FACT_INVOICE.INVOICEAMOUNT_TRAN NE 0
&CUST_WHERE
&XREF_WHERE
&LOC_WHERE
-*&CURR_WHERE
&CUSTPO_WHERE
&PART_WHERE
&BOL_WHERE
&INVSDT_WHERE
&INVEDT_WHERE
-*END WHERE STATEMENTS
ON TABLE PCHOLD FORMAT &WFFMT
-*ON TABLE NOTOTAL
ON TABLE COLUMN-TOTAL
ON TABLE SET PAGE-NUM OFF
ON TABLE SET BYDISPLAY &BYDISPLAY
ON TABLE SET LINES &RPTLINES
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
-MRNOEDIT -INCLUDE steeltech.sty
	PAGESIZE=LEGAL,
	ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,COLUMN=DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER,WIDTH=4,BACKCOLOR=YELLOW,$
-IF &WFFMT NE 'HTML' THEN GOTO SKIPEXPORTOPTIONS ;
TYPE=HEADING,
     LINE=3,
     OBJECT=FIELD,
     ITEM=1,
     SIZE=8,
	 POSITION=3.0,
     TARGET='_blank',
     FOCEXEC=&FEXNAME( \
	 WFFMT='PDF' \
	 &LNK_CUSTTYP
	 &LNK_CUST
	 &LNK_XREF
	 &LNK_INVSDT
	 &LNK_INVEDT
	 &LNK_LOC
	 &LNK_CUSTPO
	 &LNK_PART
	 &LNK_BOL
	 &LNK_SORTBY1
	 &LNK_SORTBY2
	 &LNK_SORTBY3
	 &LNK_SORTBY4
	 &LNK_SORTBY5
	 &LNK_SUBON1
	 &LNK_SUBON2
	 &LNK_SUBON3
	 &LNK_SUBON4
	 &LNK_SUBON5
	 &LNK_VIEW
	 &LNK_HHMMSS
     ),
$
TYPE=HEADING,
     LINE=3,
     OBJECT=FIELD,
     ITEM=2,
     SIZE=8,
	 POSITION=0.1,
     TARGET='_blank',
     FOCEXEC=&FEXNAME( \
	 WFFMT='EXL2K'
	 &LNK_CUSTTYP
	 &LNK_CUST
	 &LNK_XREF
	 &LNK_INVSDT
	 &LNK_INVEDT
	 &LNK_LOC
	 &LNK_CUSTPO
	 &LNK_PART
	 &LNK_BOL
	 &LNK_SORTBY1
	 &LNK_SORTBY2
	 &LNK_SORTBY3
	 &LNK_SORTBY4
	 &LNK_SORTBY5
	 &LNK_SUBON1
	 &LNK_SUBON2
	 &LNK_SUBON3
	 &LNK_SUBON4
	 &LNK_SUBON5
	 &LNK_VIEW
	 &LNK_HHMMSS
),
$
TYPE=HEADING,
     LINE=3,
     OBJECT=FIELD,
     ITEM=3,
     SIZE=8,
	 POSITION=0.1,
     TARGET='_blank',
     FOCEXEC=&FEXNAME( \
	 WFFMT='PPT'
	 &LNK_CUSTTYP
	 &LNK_CUST
	 &LNK_XREF
	 &LNK_INVSDT
	 &LNK_INVEDT
	 &LNK_LOC
	 &LNK_CUSTPO
	 &LNK_PART
	 &LNK_BOL
	 &LNK_SORTBY1
	 &LNK_SORTBY2
	 &LNK_SORTBY3
	 &LNK_SORTBY4
	 &LNK_SORTBY5
	 &LNK_SUBON1
	 &LNK_SUBON2
	 &LNK_SUBON3
	 &LNK_SUBON4
	 &LNK_SUBON5
	 &LNK_VIEW
	 &LNK_HHMMSS
),
$
TYPE=HEADING,
     LINE=3,
     OBJECT=FIELD,
     ITEM=4,
     SIZE=8,
	 POSITION=0.1,
     TARGET='_blank',
     FOCEXEC=&FEXNAME( \
	 WFFMT='AHTML'
	 &LNK_CUSTTYP
	 &LNK_CUST
	 &LNK_XREF
	 &LNK_INVSDT
	 &LNK_INVEDT
	 &LNK_LOC
	 &LNK_CUSTPO
	 &LNK_PART
	 &LNK_BOL
	 &LNK_SORTBY1
	 &LNK_SORTBY2
	 &LNK_SORTBY3
	 &LNK_SORTBY4
	 &LNK_SORTBY5
	 &LNK_SUBON1
	 &LNK_SUBON2
	 &LNK_SUBON3
	 &LNK_SUBON4
	 &LNK_SUBON5
	 &LNK_VIEW
	 &LNK_HHMMSS
),
$
-SKIPEXPORTOPTIONS

ENDSTYLE
END
 
Posts: 46 | Registered: November 08, 2011Report This Post
Master
posted Hide Post
This is actually very helpful. I have a few questions. What formats do you allow the user to chose from? And do you have this issue with all of the formats or just some of them?

When I look at what you posted, the thing that keeps popping out at me is what mgrackin writes about the way this works.

quote:
Output formats such as HTML and EXCEL, by their very nature, have dynamic cell widths and therefore the output from FOCUS will put the SUBTOTAL values on the SAME line as the "TOTAL" label. This is because HTML and EXCEL can adjust for the width of the "*TOTAL ..." label length.

Output formats such as HTML STYLEMODE FIXED or PDF determine their positioning by character length and do not contain CELLS to place values in, as do HTML and EXCEL. Therefore you experience the splitting of the SUBTOTAL line into two lines.

Try the following code. The length of the field COUNTRY is a maximum of 10 characters and the length of the "*TOTAL " is 7. I created a field called COUNTRYL with a length of A17 and then sorted by it. This created enough space in the COUNTRY sort column so that the "*TOTAL ..." could fit in that column and therefore the SUBTOTAL fits on one line.

TABLE FILE CAR
SUM RCOST DCOST SALES
COMPUTE COUNTRYL/A17=COUNTRY; NOPRINT
BY COUNTRY NOPRINT SUBTOTAL
BY COUNTRYL
ON TABLE SET STYLEMODE FIXED
END

The key here is that the BY fields THAT ARE VISIBLE determine how much space there is to put the "*TOTAL ..." label. Try the following and you will see that even though the output is HTML, the SUBTOTAL is split into two lines. This is because there are no VISIBLE sort field columns on the report.

TABLE FILE CAR
SUM RCOST DCOST SALES
BY COUNTRY NOPRINT SUBTOTAL
END

Add the BY CAR sort and it is back to one line because there is a VISIBLE sort field column to put the "*TOTAL ..." label in.

TABLE FILE CAR
SUM RCOST DCOST SALES
BY COUNTRY NOPRINT SUBTOTAL
BY CAR
END


Your visible sort by columns are a part of you &SB fields. My suggestion would be to either create define fields for each of those values which are 10 characters larger in size, or you may be able to do it on the fly just be putting /A100 at the end of each of those (I just picked an arbitrary field length).
so it would be something like
-SET &SB1      		= DECODE &SB1
-							('CUST' 	  	  'D_CUSTNUM/A100'
-							 'BOL'			  'DM_CUSTOMER_INVOICE.DIM_BOL.BOLNUMBER/A100'
-							 'LOC'			  'DM_CUSTOMER_INVOICE.DIM_LOCATION.LOCATIONNAME/A100'
-							 'XREF'		  	  'DM_CUSTOMER_INVOICE.DIM_CUSTOMER.XREFNUM/A100'
-							 'PART'		  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.PARTNUMBER/A100'
-							 'CUSTPO'	  	  'DM_CUSTOMER_INVOICE.DIM_SHOP.CUSTOMERPONUMBER/A100'
-							 'SHOP'           'DM_CUSTOMER_INVOICE.DIM_SHOP.SHOPORDERNUMBER/A100'
-						 	 ELSE 'FOC_NONE');

  


According to what mgrackin states, the issue is that your field already takes up the necessary 90 characters, so you need to add another 8 characters to make room for the '*Total ' to appear on the same line.

If you are having this issue in excel / html try setting the width of the field in the stylesheet. It may just be that excel / html isn't allowing enough room. You could specify the width you need with the squeeze / width commands.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Member
posted Hide Post
This issue is as old as FOCUS/WebFOCUS itself. Wouldn't life be so much simpler if once and for all developers get a much overdue SET TOTALLINE=NOSPLIT so they can control the behavior?

Ruhan
 
Posts: 21 | Location: South Africa | Registered: April 22, 2005Report This Post
Silver Member
posted Hide Post
Eric,

I have no visible sort by columns which is the issue. If you look they are all set to NOPRINT.


-SET &SB1    		= IF &SB1 NE FOC_NONE
-							 THEN 'BY ' | &SB1 | ' AS ' | &SB1 | ' NOPRINT'


I checked Excel and it does export fine there without the two line totals which is nice to know. Too bad HTML couldn't look that way. I'm okay on this issue. I was able to change the order of a column and make it a BY that showed on the report. Looks like a display value since I have BYDISPLAY = "ON".

However, I don't see a solution that doesn't create a leading blank column for a report that really has no displayed BYs. The dummy by isn't a good soltuion. You always end up with a column that just displays the text of the subtotal in a column by itself when it would be best to display starting in the first column under the data. I know it does this so that it doesn't overwrite a potential totaled column, but I think this should be up to the report writer.

I'm in favor of a new feature request like ruhan mentioned for TOTALLINE=NOSPLIT.

How can we make that happen?

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


7.7 Windows HTML, PDF, Excel
 
Posts: 46 | Registered: November 08, 2011Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] multi-line sort by AGAIN

Copyright © 1996-2020 Information Builders