Crystal sorting problem - looking fof ideas

[bordway]

Hi,

I have been stumped on a Purchase Order sorting problem.
I'm looking for ideas.
Thanks ahead of time for proceeding...


I have a PO (Purchase Order) report, currently grouped and sorted like this:

Group 1 = PO Number
-- Group 2 = PO Line
----- Group 3 = PO Release

Each PO Number can have multiple lines and releases.
Releases have a "Ship" field, the value in the "Ship" field can vary for each release of a PO Number. "Ship" can be blank too.

I want to group based on this:
- POs with any release that has a value in "Ship"
- POs with no release that has a value in "Ship"
So, something like this:

- New Group - Evaluate Releases (2 possibilities, at least one Rel has a "Ship" value OR all releases have no "Ship" value)
--- Group 1 = PO Number
------ Group 2 = PO Line
--------- Group 3 = PO Release

The idea is that all the POs with a "Ship" value will print, then all the POs without any "Ship" values.

I've been experimenting with a bool variable that keeps track of Ship value as it reads the Releases & resets on the PO Number change.

BTW... The data for the report comes from and XML file generated by a program. There may be a way add custom programming before the XML is generated. Something to add a flag at the PO Number level. I was thinking that would be a last resort.

 

[lbass]

I don't know what kind of values the ship field can have, but create a formula like this:

//{@hasship}:
if isnull({table.ship}) or
trim({table.ship}) = "" or
{table.ship} <> "Ship" then //replace "ship" with the value meaning "ship"
0 else
1

Place this in the detail section and then right click on it and insert a maximum on it at the PONumber Group level. Then go to report->group sort and choose maximum of {@hasship} as the group sort field. This will cluster all POs with no "ship" together, and those with "ship" together--although it doesn't create an outer group per se. If you want to label the clusters to create the effect of a group, let me know.

-LB

 

[bordway]

Hi lb,

The formula worked.

>If you want to label the clusters
>to create the effect of a group
Yes, if you have time I am interested in this.

Thanks



But I screwed up yesterday, I didn't describe my groups correctly in the original post.
I forgot there is one more group involved.
(I removed that extra group to test your formula).


This is what the report groups really look like:

Group 0 = Vendor
- Group 1 = PO Number
--- Group 2 = PO Line
------ Group 3 = PO Release

And this is what I want to end up with:

- New Group - Evaluate Releases
---- Group 0 = Vendor
------ Group 1 = PO Number
-------- Group 2 = PO Line
----------- Group 3 = PO Release

I'd like to hear any thoughts you have on this now.
How to approach it, or if it is even possible.

Thanks again..

Bruce O

 

[lbass]

What is the logic of this? Are you saying you want all "ship" POs together regardless of vendor? If so, why is the vendor the outer group? You could as easily switch PO and vendor groups (since a PO will be unique to a vendor). It might help to see a mock sample of original data and then show how you would want it to sort.

-LB

 

[bordway]

Hi LB,

>What is the logic of this?
>Are you saying you want all "ship" POs
>together regardless of vendor?
Yes, it is messy.
I'll explain it the best I can below.

Thanks again for taking the time to look....


1 - PO Releases may/may not involve shipments.
2 - I want to the POs with any shipment, separate from POs with no shipments.
3 - Keep Vendors together as POs print (in the "Ship" group)

Ship - (new group/sort).
- Vendor (new group/sort)
---- PO Number (existing major group).
------- Group by PO lines (existing group)
---------- Group by PO releases (existing group)

***** Below is a simulated output ******

- **** Ship POs (at lease one release has a value in ship)
-- Vendor ABC
---- PO Number 999000 - Vendor ABC
------- Line 1, Rel 1, Shipment = AUG2211
------- Line 2, Rel 1, Shipment = AUG1911
------- Line 3, Rel 1, Shipment = AUG2611

---- PO Number 999003 - Vendor ABC
------ Line 1, Rel 1, Shipment = OCT2211
------ Line 1, Rel 2, Shipment = OCT1911

- Vendor DEF
---- PO Number 999001 - Vendor DEF
------ Line 1, Rel 1, Shipment = AUG2511
------ Line 2, Rel 1, Shipment =

- *** Non Ship POs (no releases can have a value in ship)
-- Vendor ABC
---- PO Number 999004 - Vendor ABC
------- Line 1, Rel 1, Shipment = ""
------- Line 2, Rel 1, Shipment = ""
------- Line 3, Rel 1, Shipment = ""

---- PO Number 999006 - Vendor ABC
------ Line 1, Rel 1, Shipment = ""
------ Line 2, Rel 1, Shipment = ""

- Vendor DEF
---- PO Number 999005 - Vendor DEF
------ Line 1, Rel 1, Shipment = ""
------ Line 1, Rel 2, Shipment = ""


Bruce O

 

[bordway]

P.S.

>If you want to label the clusters to create
>the effect of a group, let me know.
Related to your original reply involving only one sort for ship.
I'm really interested in how I might create this effect if you have time.

Thanks again


Bruce O

 

[lbass]

Okay, I tested this. Remove the Vendor group and replace your PO Number Group with a group#1 based on vendor and PO number, something like this:

{table.vendor}+" "+totext({table.PONumber},0,"")

If you want to, you can group on this formula, but remove the group name and use a different display, e.g., PO number + vendor name.

Then using the earlier formula and group sort technique, you would have shipped POs at the top, in order of the vendor name (Be sure to choose descending order for the maximum). Then add two additional GH#1 sections and move your group#1 name to GH1c.

In GH1a, add the following formula:

whileprintingrecords;
stringvar grpname;
numbervar curr;
numbervar prev := curr;
numbervar curr := maximum({@haship},{@Vendor-PO}); //{@Vendor-PO} = Group#1 field
if groupnumber = 1 then
grpname := "Shipped POs" else
grpname := "";
if prev <> 0 and
curr = 0 then
grpname := "Non-Shipped POs";
grpname

In the section expert, format GH1a and GH1b to "suppress blank section". Also format the higher order "Group Header #1" (above GH1a) to "Keep Together."

In GH1b, add the vendor name. To suppress all but the first instance of the vendor name, right click on the vendor name->format field->suppress->x+2 and enter:

whileprintingrecords;
numbervar prev;
numbervar curr;
curr = prev and
{table.vendor} = previous({table.vendor})

After doing the group sort, you can remove the maximum of {@hasship} from the report.

Both GH1a and GH1b will give the appearance of being separate groups, although they are not.

-LB

 

[bordway]

Thanks for your help LB

Bruce O