Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Suppress duplicate detail records that are unsorted

Posted on 2006-07-19
6
Medium Priority
?
390 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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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 1500 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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

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

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. …
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…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

721 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