Solved

Suppress duplicate detail records that are unsorted

Posted on 2006-07-19
6
388 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 101

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

729 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