Underlay crisis 2

[Kaithyn]

Greetings all!
I am having a problem getting my report to underlay properly. It is a rather extensive report with 8 groups for sorting. The problem is that groups 5, 6 and 8 will underlay, but when I try to get 7 to join the crowd it won't. I need them all to show on the same line, but again due to the sorting I am unable to have them in the same group. Quick fix anyone?

What I have and their groups:
Order(7) Item(5) Ship Cont(6) Loc(6) OrderType(8) PickQty(8)
ABCD Pallet A LWS 450
1234

What I need:
Order(7) Item(5) Ship Cont(6) Loc(6) OrderType(8) PickQty(8)
1234 ABCD Pallet A LWS 450

I can not alter the grouping is there a formula that I can put in the section expert to correct this?

 

[akabatman2]

Hi Kaithyn,

I don't know if I understand your question entirely.
As far as I know, from the top down, any report section that you set to "Underlay following Sections" will allow the section below it to underlay so that the section below will now appear on the same line as the section that is set.

If it is the order of the groups that is your issue, you can drag and drop each section to arrange the order by left clicking and holding the group section name in the gray area and moving them around until you have them in the order that you want them.

Hope this helps.

akabatman2

 

[Kaithyn]

Thanks, but I tried that. 8 will line up with 2 but not all three . Unless I am doing it wrong-highly possible. Sometimes when I drag and drop it throws them way off. Did I mention I got stuck with Crystal about a month ago? So I am new at this.

 

[akabatman2]

If that is the case, you might want to do this output dataset in SQL and bring it back the way you want it from SQL server and have all of your output fields on the detail line.

It is faster to have SQL server return the dataset the way you want it rather than have multiple group underlays.

 

[Kaithyn]

I have a procedure/isql for it, unfortunately due to the complexity of the report, the grouping that is in the procedure is returning different information than what I need for this added bit of grouping. It was an after thought on the customer's part and as you know those are always the most fun to change after everything has been completed. This procedure when printed out, is 5 pages long, I would hate to rewrite it just because the last two groups won't underlay. Any other thoughts or ideas to try?

 

[Kaithyn]

There are no record selections list in the report. There are two selection criteria-by appointment and by number. The customer wants to see anything on an order that is picked and ready to ship. If there is nothing picked of that item they want totals but no info for the order. (That was the latest change). The procedure is below if you think you might have an idea.



drop procedure sp_lodbyitm;
CREATE PROCEDURE sp_lodbyitm(Appointment CHAR(30), Order CHAR(15))
RETURNING
CHAR(15),
INTEGER,
CHAR(15),
CHAR(30),
INTEGER,
CHAR(10),
CHAR(4),
CHAR(20),
DECIMAL(12,2),
CHAR(1),
CHAR(1),
INTEGER,
CHAR(40),
CHAR(40),
DECIMAL(12,2),
CHAR(20),
CHAR(15),
CHAR(4),
DECIMAL(12,2),
CHAR(6),
CHAR(15),
INTEGER,
DECIMAL(12,2),
INTEGER,
INTEGER;
DEFINE l_status CHAR(15);
DEFINE l_stop_seq LIKE om_f.stop_seq;
DEFINE l_shipment LIKE om_f.shipment;
DEFINE l_appt LIKE om_f.appt;
dEFINE l_loc LIKE cn_f.loc;
DEFINE l_cntype LIKE cn_f.cntype;
DEFINE l_cont LIKE iv_f.cont;
DEFINE l_qty LIKE od_f.cmp_qty;
DEFINE l_xo_bol LIKE xo_f.on_bol;
DEFINE l_xd_bol LIKE xd_f.on_bol;
DEFINE l_xd_lno LIKE xd_f.ob_lno;
DEFINE l_xo_cmt LIKE xo_f.cmt;
DEFINE l_xd_cmt LIKE xd_f.cmt;
DEFINE l_ord_qty LIKE od_f.ord_qty;
DEFINE l_sku LIKE od_f.sku;
DEFINE l_iv_ob_oid LIKE iv_f.ob_oid;
DEFINE l_hold LIKE od_f.hold;
DEFINE l_rsn_qty LIKE od_f.rsn_qty;
DEFINE l_ob_type LIKE od_f.ob_type;
DEFINE l_ob_oid LIKE om_f.ob_oid;
DEFINE l_ob_lno LIKE od_f.ob_lno;
DEFINE l_pk_qty LIKE iv_f.qty;
DEFINE l_num1,l_num2 LIKE xo_f.num;
DEFINE err_num SMALLINT;
ON EXCEPTION IN (-958) SET err_num
IF err_num = -206 THEN
DROP TABLE loaditmtmp;
END IF;
END EXCEPTION WITH RESUME;
SET ISOLATION TO DIRTY READ;
IF Appointment != "%" THEN
SELECT "Picked " as status,
om_f.stop_seq,
om_f.shipment,
om_f.appt,
cn_f.loc,
cn_f.cntype,
iv_f.cont,
od_f.cmp_qty as qty,
xo_f.on_bol as xo_bol,
xd_f.on_bol as xd_bol,
xd_f.ob_lno as xd_lno,
xo_f.cmt as xo_cmt,
xd_f.cmt as xd_cmt,
od_f.ord_qty as ord_qty,
od_f.sku,
iv_f.ob_oid as iv_ob_oid,
od_f.hold,
od_f.rsn_qty as rsn_qty,
om_f.ob_type,
om_f.ob_oid,
od_f.ob_lno,
Sum(iv_f.qty) as pk_qty,
xo_f.num as num1,
xd_f.num as num2
FROM om_f, od_f,OUTER xo_f,iv_f,OUTER xd_f, cn_f
WHERE om_f.appt LIKE upper(Appointment)
AND om_f.ob_oid LIKE upper(Order)
AND om_f.ob_oid = od_f.ob_oid
AND om_f.ob_type = od_f.ob_type
AND om_f.ob_oid = xo_f.ob_oid
AND om_f.ob_type = xo_f.ob_type
AND od_f.ob_lno = xd_f.ob_lno
AND od_f.ob_oid = xd_f.ob_oid
AND od_f.ob_type = xd_f.ob_type
AND od_f.ob_oid = iv_f.ob_oid
AND od_f.ob_type = iv_f.ob_type
AND od_f.sku = iv_f.sku
AND iv_f.cont = cn_f.cont
AND iv_f.ob_lno = od_f.ob_lno
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24
INTO TEMP loaditmtmp WITH NO LOG;
INSERT INTO loaditmtmp
SELECT "Planned" as status,
om_f.stop_seq,
om_f.shipment,
om_f.appt,
" ",
" ",
iv_f.cont,
od_f.cmp_qty as qty,
xo_f.on_bol as xo_bol,
xd_f.on_bol as xd_bol,
xd_f.ob_lno as xd_lno,
xo_f.cmt as xo_cmt,
xd_f.cmt as xd_cmt,
od_f.ord_qty as ord_qty,
od_f.sku,
iv_f.ob_oid as iv_ob_oid,
od_f.hold,
od_f.rsn_qty as rsn_qty,
om_f.ob_type,
om_f.ob_oid,
od_f.ob_lno,
Sum(iv_f.qty) as pk_qty,
xo_f.num as num1,
xd_f.num as num2
FROM om_f, od_f,OUTER xo_f,iv_f,OUTER xd_f
WHERE om_f.appt LIKE upper(Appointment)
AND om_f.ob_oid LIKE upper(Order)
AND om_f.ob_oid = od_f.ob_oid
AND om_f.ob_type = od_f.ob_type
AND om_f.ob_oid = xo_f.ob_oid
AND om_f.ob_type = xo_f.ob_type
AND od_f.ob_lno = xd_f.ob_lno
AND od_f.ob_oid = xd_f.ob_oid
AND od_f.ob_type = xd_f.ob_type
AND od_f.ob_oid = iv_f.ob_oid
AND od_f.ob_type = iv_f.ob_type
AND od_f.sku = iv_f.sku
AND iv_f.ob_lno = od_f.ob_lno
AND (NOT EXISTS ( SELECT 1 FROM cn_f
WHERE cn_f.cont = iv_f.cont))
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24;
INSERT INTO loaditmtmp
SELECT "Unplanned" as status,
om_f.stop_seq,
om_f.shipment,
om_f.appt,
'' as loc,
'' as cntype,
'' as cont,
0 as qty,
xo_f.on_bol as xo_bol,
xd_f.on_bol as xd_bol,
xd_f.ob_lno as xd_lno,
xo_f.cmt as xo_cmt,
xd_f.cmt as xd_cmt,
od_f.ord_qty as ord_qty,
od_f.sku,
'' as iv_ob_oid,
od_f.hold,
od_f.rsn_qty as rsn_qty,
om_f.ob_type,
om_f.ob_oid,
od_f.ob_lno,
0 as pk_qty,
xo_f.num as num1,
xd_f.num as num2
FROM om_f, od_f, OUTER xd_f, OUTER xo_f
WHERE om_f.appt LIKE upper(Appointment)
AND om_f.ob_oid LIKE upper(Order)
and om_f.ob_oid = od_f.ob_oid
AND om_f.ob_type = od_f.ob_type
AND od_f.ob_oid = xd_f.ob_oid
AND od_f.ob_type = xd_f.ob_type
AND od_f.ob_lno = xd_f.ob_lno
AND od_f.ob_oid = xo_f.ob_oid
AND od_f.ob_type = xo_f.ob_type
AND (NOT EXISTS ( SELECT 1 FROM iv_f
WHERE od_f.ob_lno = iv_f.ob_lno
AND od_f.ob_oid = iv_f.ob_oid
AND od_f.ob_type = iv_f.ob_type))
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24;
ELSE
SELECT "Picked " as status,
om_f.stop_seq,
om_f.shipment,
om_f.appt,
cn_f.loc,
cn_f.cntype,
iv_f.cont,
od_f.cmp_qty as qty,
xo_f.on_bol as xo_bol,
xd_f.on_bol as xd_bol,
xd_f.ob_lno as xd_lno,
xo_f.cmt as xo_cmt,
xd_f.cmt as xd_cmt,
od_f.ord_qty as ord_qty,
od_f.sku,
iv_f.ob_oid as iv_ob_oid,
od_f.hold,
od_f.rsn_qty as rsn_qty,
om_f.ob_type,
om_f.ob_oid,
od_f.ob_lno,
Sum(iv_f.qty) as pk_qty,
xo_f.num as num1,
xd_f.num as num2
FROM om_f, od_f,OUTER xo_f,iv_f,OUTER xd_f, cn_f
WHERE om_f.ob_oid LIKE upper(Order)
AND om_f.ob_oid = od_f.ob_oid
AND om_f.ob_type = od_f.ob_type
AND om_f.ob_oid = xo_f.ob_oid
AND om_f.ob_type = xo_f.ob_type
AND od_f.ob_lno = xd_f.ob_lno
AND od_f.ob_oid = xd_f.ob_oid
AND od_f.ob_type = xd_f.ob_type
AND od_f.ob_oid = iv_f.ob_oid
AND od_f.ob_type = iv_f.ob_type
AND od_f.sku = iv_f.sku
AND iv_f.cont = cn_f.cont
AND iv_f.ob_lno = od_f.ob_lno
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24
INTO TEMP loaditmtmp WITH NO LOG;
INSERT INTO loaditmtmp
SELECT "Planned" as status,
om_f.stop_seq,
om_f.shipment,
om_f.appt,
" ",
" ",
iv_f.cont,
od_f.cmp_qty as qty,
xo_f.on_bol as xo_bol,
xd_f.on_bol as xd_bol,
xd_f.ob_lno as xd_lno,
xo_f.cmt as xo_cmt,
xd_f.cmt as xd_cmt,
od_f.ord_qty as ord_qty,
od_f.sku,
iv_f.ob_oid as iv_ob_oid,
od_f.hold,
od_f.rsn_qty as rsn_qty,
om_f.ob_type,
om_f.ob_oid,
od_f.ob_lno,
Sum(iv_f.qty) as pk_qty,
xo_f.num as num1,
xd_f.num as num2
FROM om_f, od_f,OUTER xo_f,iv_f,OUTER xd_f
WHERE om_f.ob_oid LIKE upper(Order)
AND om_f.ob_oid = od_f.ob_oid
AND om_f.ob_type = od_f.ob_type
AND om_f.ob_oid = xo_f.ob_oid
AND om_f.ob_type = xo_f.ob_type
AND od_f.ob_lno = xd_f.ob_lno
AND od_f.ob_oid = xd_f.ob_oid
AND od_f.ob_type = xd_f.ob_type
AND od_f.ob_oid = iv_f.ob_oid
AND od_f.ob_type = iv_f.ob_type
AND od_f.sku = iv_f.sku
AND iv_f.ob_lno = od_f.ob_lno
AND (NOT EXISTS ( SELECT 1 FROM cn_f
WHERE cn_f.cont = iv_f.cont))
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24;
INSERT INTO loaditmtmp
SELECT "Unplanned" as status,
om_f.stop_seq,
om_f.shipment,
om_f.appt,
'' as loc,
'' as cntype,
'' as cont,
0 as qty,
xo_f.on_bol as xo_bol,
xd_f.on_bol as xd_bol,
xd_f.ob_lno as xd_lno,
xo_f.cmt as xo_cmt,
xd_f.cmt as xd_cmt,
od_f.ord_qty as ord_qty,
od_f.sku,
'' as iv_ob_oid,
od_f.hold,
od_f.rsn_qty as rsn_qty,
om_f.ob_type,
om_f.ob_oid,
od_f.ob_lno,
0 as pk_qty,
xo_f.num as num1,
xd_f.num as num2
FROM om_f, od_f, OUTER xd_f, OUTER xo_f
WHERE om_f.ob_oid LIKE upper(Order)
and om_f.ob_oid = od_f.ob_oid
AND om_f.ob_type = od_f.ob_type
AND od_f.ob_oid = xd_f.ob_oid
AND od_f.ob_type = xd_f.ob_type
AND od_f.ob_lno = xd_f.ob_lno
AND od_f.ob_oid = xo_f.ob_oid
AND od_f.ob_type = xo_f.ob_type
AND (NOT EXISTS ( SELECT 1 FROM iv_f
WHERE od_f.ob_lno = iv_f.ob_lno
AND od_f.ob_oid = iv_f.ob_oid
AND od_f.ob_type = iv_f.ob_type))
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,23,24;
END IF;
FOREACH
SELECT status, stop_seq, shipment, appt,loc,cntype,cont,
qty,xo_bol,xd_bol,xd_lno, xo_cmt, xd_cmt, ord_qty,sku, iv_ob_oid,
hold, rsn_qty, ob_type, ob_oid, ob_lno,pk_qty,num1,num2
INTO l_status, l_stop_seq,l_shipment,l_appt,l_loc,
l_cntype,l_cont,l_qty,l_xo_bol,l_xd_bol,l_xd_lno,l_xo_cmt,l_xd_cmt,
l_ord_qty,l_sku,l_iv_ob_oid,l_hold,l_rsn_qty,l_ob_type,l_ob_oid,
l_ob_lno,l_pk_qty,l_num1,l_num2
FROM loaditmtmp
RETURN l_status, l_stop_seq,l_shipment,l_appt," ",l_loc,
l_cntype,l_cont,l_qty,l_xo_bol,l_xd_bol,l_xd_lno,l_xo_cmt,l_xd_cmt,
l_ord_qty,l_sku,l_iv_ob_oid,l_hold,l_rsn_qty,l_ob_type,l_ob_oid,
l_ob_lno,l_pk_qty,l_num1,l_num2
WITH RESUME;
END FOREACH;
DROP TABLE loaditmtmp;
END PROCEDURE;

 

[kenhamady]

Going back to the original post. My guess is that you might be trying to mix Group Headers and Group Footers, which won't underaly together.

Why not simply move the Group 7 field to the Group 8 section? All of the Group 8's will have the same group 7 value. You probably don't have to use Underlay at all if you put all of the fields in the last group section.



Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- TEK2(AT)kenhamady.com

 

[Kaithyn]

Thanks! The header/footer combo was the issue. I was also able to remove some of the grouping I was using to suppress certain issues by adding suppression formulas to each of the fields instead. It is one ugly report-but if it works and gives the customer what they want that is the whole point, eh? The testing group is currently trying to destroy it-so I have my fingers crossed.

I really appreciate the real time help you all provide. I hope in the future I will also be able to help newbies like myself Kudos to you all!!