Finding Duplicates

Posted on 2011-04-29
Last Modified: 2012-06-27
I have a report that lists, say, sales people.  The report is grouped on State.  I need to be able to highlight (change font colour to red) sales persons who may appear in different states.

It's easy when they are in the same state, since they are sorted on an ID number and so I can just do sometrhing like this:
    Previous ({SalesTable.SalesID}) = {SalesTable.SalesID}
    Next ({SalesTable.SalesID}) = {SalesTable.SalesID}

Open in new window

However, that doesn't work if the sales person is listed in CA and NY.

Any ideas?

Question by:Clif
    LVL 7

    Assisted Solution

    So basically you have
    select t1.SalesID, t1.State from SalesTable t1, t2
    where (t1.SalesID = t2.SalesID) AND ( t1.State != t2.State)

    LVL 100

    Accepted Solution

    If you must group by state there is no easy way to do this.

    You could use a subreport that selects all salesmen that aren't in that state then compare them to the list.

    LVL 34

    Assisted Solution

    FWIW, the most efficient thing would probably be if you could do this on the server.  For example, create a query that would get a count of the states for each salesperson.  Put that in something like a stored procedure or CR Command and use that for the datasource for the report.  Then the report could just check that count and if it's > 1, the salesperson is in more than one state.

     But doing it in the report could also be fine, especially if the report is not reading a large number of records/salespeople.

     One option might be a CR SQL Expression.  You may be able to create a query in the report in a SQL Expression that will, for example, get a count of the states for each salesperson.  Unfortunately, I may not be of much help with that.  I don't normally use them (almost all of my reports use stored procedures, and you can't use SQL Expressions with stored procedures).  I just did a little experimenting trying to do something similar here and couldn't get it to work, but I think I was probably doing something wrong.

     Otherwise, subreports may be the only option.  You could run a subreport for each salesperson in each state that would get a count of the states for that salesperson.  You could do something like mlmcc described, where you looked for states other than the current state or something like that, but if you just want to know if a salesperson is in more than one state, all that you really need is a state count for the salesperson.  Basically, the subreport would be run for each salesperson and put a count of the distinct state values for that salesperson in a shared variable, which the main report would use to set the color on the field in question.  Or, you could eliminate the shared variable and just have the subreport output the field in question, with the color set according to the count.

     However, running a subreport like that for each salesperson in each state could be very inefficient.  If you only have a few salespeople in a few states, then that may not matter.  Otherwise, it would be much more efficient if you could just run the subreport once and let it gather the state counts for all of the salespeople at the same time.  You could do that using arrays, although CR arrays are limited to 1,000 elements, so if you could have more that 1,000 salespeople on a single report, that would complicate things (but you could work around that, up to a point, if necessary).

     Basically, a subreport in the report header of the main report would read the data for all salespeople (not just one at a time) and save the salesperson ID's in one array, and a state count for each salesperson in a second array.  Then the main report would look up each ID in the first array and get the corresponding count from the second array.

     Since I'm throwing out several ideas, I decided not to get into too much detail.  If you'd like more details for any of these ideas, just ask.

    LVL 11

    Author Closing Comment

    It would appear that this is effectively impossible.

    Thanks for your help anyway.
    LVL 34

    Expert Comment

    I don't know where you get "effectively impossible".  Arguably, it might be a bit difficult, if you're not familiar with the techniques involved, but it certainly seems possible.  Of course if you don't want to pursue it (maybe the color change just isn't that important), that's your call.  But I wouldn't call it "impossible".


    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    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 …
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…

    745 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

    15 Experts available now in Live!

    Get 1:1 Help Now