Solved

Suppress duplicate detail records that are unsorted

Posted on 2006-07-19
6
379 Views
Last Modified: 2012-06-27
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)) suppression formula to no avail for both cdate and cqty.

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





0
Comment
Question by:afd_ais
  • 3
  • 2
6 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 17142822
Perhaps I am dense but as you remove data/information from the report it becomes harder to determine what the orders are.

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
0
 
LVL 17

Expert Comment

by:MIKE
ID: 17143293
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
0
 
LVL 17

Expert Comment

by:MIKE
ID: 17143295
You can also adjust your stored PROC to only return what you need...but...you might try the above .. first.
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:afd_ais
ID: 17147520
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

0
 
LVL 17

Accepted Solution

by:
MIKE earned 500 total points
ID: 17147782
You need to work with the SUM by MAX...in order to get the data to appear correctly.

Right click on the CQty and PO and whatever else you need to appear...and set the SUM to MAX. It's hard to tell by your posts..but I'm certain it will work. You just need to adjust the SUMs by MAX that appear in the FOOTER for final presentation.

Hope it helps.
Mike V
0
 

Author Comment

by:afd_ais
ID: 17155929
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.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now