afd_ais
asked on
Suppress duplicate detail records that are unsorted
A stored proc is returning this result dataset, which is derived from multiple other tables. The Crystal Report is ordered by DC by Vendor by ItemNo.
SELECT DISTINCT
DC,
VENDOR,
ITEMNO,
PO,
ORDQTY,
.
.
.
CQTY,
CDATE
FROM #SS
ORDER BY DC, VENDOR, ItemNo, PO, CDate
Before suppressing duplicates within the detail section of the report I get a result set that looks similar to this...
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
A B C D E......02/04/06 40 123A 140
A B C D E......01/03/06 10 135B 60
A B C D E......02/04/06 40 135B 60
After suppressing duplicates within the detail section of the report I get a result set that looks similar to this...
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
02/04/06 40
01/03/06 10 135B 60
02/04/06 40
For this report, I need to also remove the duplicate details from CDate and CQty as well. I've attempted using the
(Tablename.column) = Previous((Tablename.column
I need to manage to get the report to resemble this:
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
02/04/06 40
135B 60
What would be the best way to go about accomplishing this task? Any assistance would be greatly appreciated! thank you
SELECT DISTINCT
DC,
VENDOR,
ITEMNO,
PO,
ORDQTY,
.
.
.
CQTY,
CDATE
FROM #SS
ORDER BY DC, VENDOR, ItemNo, PO, CDate
Before suppressing duplicates within the detail section of the report I get a result set that looks similar to this...
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
A B C D E......02/04/06 40 123A 140
A B C D E......01/03/06 10 135B 60
A B C D E......02/04/06 40 135B 60
After suppressing duplicates within the detail section of the report I get a result set that looks similar to this...
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
02/04/06 40
01/03/06 10 135B 60
02/04/06 40
For this report, I need to also remove the duplicate details from CDate and CQty as well. I've attempted using the
(Tablename.column) = Previous((Tablename.column
I need to manage to get the report to resemble this:
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
02/04/06 40
135B 60
What would be the best way to go about accomplishing this task? Any assistance would be greatly appreciated! thank you
If these are your only concerns with the DATA on this report:
firsr CREATE a GROUP by CDATE
then CREATE a another group by PO
then RIGHT CLICK on the gray area and SELECT ALL ITEMS, then RIGHTCLICK on one of the highlighted data elements and copy/past them all into the GROUP FOOTER for "PO".
SUPPRESS all sections except the GROUP FOOTER FOR PO.
I hope I'm understanding and I Hope it helps.
MikeV
firsr CREATE a GROUP by CDATE
then CREATE a another group by PO
then RIGHT CLICK on the gray area and SELECT ALL ITEMS, then RIGHTCLICK on one of the highlighted data elements and copy/past them all into the GROUP FOOTER for "PO".
SUPPRESS all sections except the GROUP FOOTER FOR PO.
I hope I'm understanding and I Hope it helps.
MikeV
You can also adjust your stored PROC to only return what you need...but...you might try the above .. first.
ASKER
Mlmcc,
I think I know where you were thrown off track. Since I need to group by DC by Vendor by Item...for each Item that exists, there are multiple rows of information similar to the first sample above that are returned to describe each item.
A B C D E... refers to information pertaining directly to the item.
CDate and CQty refers to shipment information. The date and number of cases that the items were shipped.
PO and OrdQty refers to purchase order information and how many cases were ordered
Thus, the first sample would suggest that there were shipments containing the Item FOO on both 01/03/06 and 02/04/06 containing 10 and 40 cases respectively. PO's 123A and 123B having 140 and 60 cases ordered respectively each have part of there FOO orders included in the two shipments.
The information displayed is accurate, even if none of the information is suppressed. however, yes it is a detail/appearance thing where the users of this report want only to see the two shipment dates and their quantities and the two POs and their quantities, because the inference is made that each of the POs are included in each of the shipment dates.
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
A B C D E......02/04/06 40 123A 140
A B C D E......01/03/06 10 135B 60
A B C D E......02/04/06 40 135B 60
CRXIuser2005 -- I see where you were trying to go with your suggestion, but that created a sample that looks like the following and nothing will suppress. The stored proc currently does return everything I need.
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
A B C D E......01/03/06 10 135B 60
A B C D E......02/04/06 40 123A 140
A B C D E......02/04/06 40 135B 60
I think I know where you were thrown off track. Since I need to group by DC by Vendor by Item...for each Item that exists, there are multiple rows of information similar to the first sample above that are returned to describe each item.
A B C D E... refers to information pertaining directly to the item.
CDate and CQty refers to shipment information. The date and number of cases that the items were shipped.
PO and OrdQty refers to purchase order information and how many cases were ordered
Thus, the first sample would suggest that there were shipments containing the Item FOO on both 01/03/06 and 02/04/06 containing 10 and 40 cases respectively. PO's 123A and 123B having 140 and 60 cases ordered respectively each have part of there FOO orders included in the two shipments.
The information displayed is accurate, even if none of the information is suppressed. however, yes it is a detail/appearance thing where the users of this report want only to see the two shipment dates and their quantities and the two POs and their quantities, because the inference is made that each of the POs are included in each of the shipment dates.
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
A B C D E......02/04/06 40 123A 140
A B C D E......01/03/06 10 135B 60
A B C D E......02/04/06 40 135B 60
CRXIuser2005 -- I see where you were trying to go with your suggestion, but that created a sample that looks like the following and nothing will suppress. The stored proc currently does return everything I need.
A B C D E...... CDate CQty PO OrdQty
--------------------------
A B C D E......01/03/06 10 123A 140
A B C D E......01/03/06 10 135B 60
A B C D E......02/04/06 40 123A 140
A B C D E......02/04/06 40 135B 60
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
CRXIuser2005 -- Turned out that what I needed to do was keep cols ABCDE, PO, and OrdQty in the detail section and create a subreport for the CDate and CQty and place it in the ItemGroup footer and then suppressing all dupes. What you were suggesting to do was fixing the CDate and CQty aspect of the data, but was messing up the PO and OrdQty. thanks for your efforts, however.
From your last sample I assume there are 2 orders fo2 2 dates
PO 123A on 01/03/06 and 02/04/06
PO 135B on 02/04/06
What happens with the )1/03/06 entries ofr PO 135B?
What is the driving force behind "eliminating" the information from the report? Were people having trouble reading it? If so perhaps a better idea would be to add some white space or blank lines between printed lines
mlmcc