Finding Duplicates

Posted on 2011-04-29
Medium Priority
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

Assisted Solution

printnix63 earned 400 total points
ID: 35494900
So basically you have
select t1.SalesID, t1.State from SalesTable t1, t2
where (t1.SalesID = t2.SalesID) AND ( t1.State != t2.State)

LVL 101

Accepted Solution

mlmcc earned 800 total points
ID: 35494965
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 35

Assisted Solution

James0628 earned 800 total points
ID: 35496747
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 10

Author Closing Comment

ID: 35513853
It would appear that this is effectively impossible.

Thanks for your help anyway.
LVL 35

Expert Comment

ID: 35688454
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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

569 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