 |
|||||
|
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
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 WebFOCUS/FOCUS Forum on Focal Point [CLOSED] multi-line sort by AGAIN
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] multi-line sort by AGAIN Read-Only TopicGo | Search | Notify | Tools |
| [CLOSED] multi-line sort by AGAIN | Login/Join |
| Silver Member |
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 | ||
|
| Expert |
Sure there is... For starters, someting for you to check out: BY ThisField NOPRINT ... oN ThisField SUBHEAD / "...
| |||||
|
| Silver Member |
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 | |||
|
| Member |
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 | |||
|
| Virtuoso |
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
| |||||||
|
| Silver Member |
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 | |||
|
| Virtuoso |
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 ENDThis message has been edited. Last edited by: Dan Satchell, WebFOCUS 7.7.05 | |||
|
| Silver Member |
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 | |||
|
| Master |
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 | |||
|
| Master |
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 | |||
|
| Silver Member |
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 | |||
|
| Master |
Shelzalee, I pulled this straight out of the help file
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 | |||
|
| Silver Member |
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 | |||
|
| Master |
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 | |||
|
| Master |
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 | |||
|
| Silver Member |
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. | |||
|
| Master |
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 | |||
|
| Silver Member |
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
| |||
|
| Master |
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.
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 | |||
|
| Member |
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 | |||
|
| Silver Member |
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 | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicFocal Point Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] multi-line sort by AGAIN
Focal Point Forums WebFOCUS/FOCUS Forum on Focal Point [CLOSED] multi-line sort by AGAINCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |