Group without duplicates 1

[brek2]

I am using CR8.5 with a SQL database. I have a report grouped by recruiter name and detail of interview information as follows:



Group1:
Recruiter Jim Smith:

Detail:
Name DateInterviewed InterviewedBy Req#

Jane Doe 3/1/2005 Betty Banns 578



Recruiter Carol Johns

Detail:
Bill Banes 1/2/2005 Carol Johns 684
Jane Doe 3/15/2005 Tim Towns 578


In the case of Jane Doe above, where she is interviewed twice for the same req#, I only want the record with the latest interview date. The record for Jane Doe with the earlier interview date for the same req# should not show in the report. Then I will need to sum total interviews for each recruiter.

Thanks in advance for your help!

 

[lbass]

I would use a SQL expression {%maxdate} for this:

(select max(AKA.`DateInterviewed`) from Table AKA where
AKA.`Name` = Table.`Name` and
AKA.`Req#` = Table.`Req#`)

Replace "DateInterviewed", "Name", and "Req#" with your actual field names, and replace "Table" with your table name. Leave "AKA" as is, since it is an alias table name. If you have any record selection criteria, you may need to adapt the expression.

Then in the record selection formula use:

{table.DateInterviewed} = {%maxdate}

This will entirely remove the earlier records from the report for the same req# and name, so that you should be able to insert counts per recruiter and get an accurate result.

-LB

 

[brek2]

Thank you, lb, for your response. DateInterviewed is actually a formula field that has another formula embedded in it –

If isnull({Table.HistoryTimestamp}) then ({@FInterview})

else {Table.HistoryTimestamp}


Is it possible to use a formula like this with your SQL expression example?

Thanks in advance for your help.

 

[lbass]

You can't use formulas in a SQL expression. What is the content of {@FInterview}? As a matter of course, you should always identify formulas by using {@formula} and also provide the contents of any nested formulas. Are you working with more than one table? If so, please explain what fields are from what table.

-LB

 

[brek2]

Thank you for your quick response. My apologies for not posting DateInterviewed as a formula in my first post. The exact content of the two formulas are below. I am working with a SQL view named “Interviews”.

{@DateInterviewed}

If isnull({Interviews.HistoryTimestamp}) then ({@FInterview})

else {Interviews.HistoryTimestamp}


{@Finterview}
if {Interviews.InterviewCreationDate} > {Interviews.DateInterviewed} then

{Interviews.InterviewCreationDate}


else {Interviews.DateInterviewed}



In the meantime, I found a sample report on the Business Objects website, “Conditionally Suppressing Records Included in Other Groups” in the zip file “cr_groups_drilldown_samples” that looks like what I need. It uses a subreport and arrays. I’m also trying that but don’t have it working yet. I hope your solution is simpler.

Thanks very much for your help.

 

[lbass]

I think the various if-then's related to the date field make the SQL expression untenable, so you might try the solution you read about.

-LB

 

[brek2]

I tried modeling my report after the Business Objects sample report using arrays and ran into the 1000 limitation problem. Is there any other solution?

Thanks in advance.

 

[lbass]

Okay, here's a solution that will probably be slow, because it uses a subreport in the detail section. In the main report, insert a detail_b section and then drag the detail_b section so that it becomes an empty detail_a section and your fields are in detail_b. Then insert a subreport that has a group#1 on {table.req#} and a Group#2 on {table.name}, and that shows the interview date in the detail section. Then create a formula {@maxdate}:

whileprintingrecords;
shared datevar maxdate := maximum({@dateinterviewed},{table.name});

Place this in the subreport in the GH#2 footer. Then link the subreport to the main report on {table.req#} and {table.name}. Place the subreport in the detail_a section. In the subreport, suppress all subreport sections and then in the main report, resize the subreport to make it as small as possible. In the main report, go to the section expert and highlight detail_a and check "Underlay following sections". Then highlight detail_b->suppress->x+2 and enter:

whileprintingrecords;
shared datevar maxdate;

{@DateInterviewed} <> maxdate

Also format detail_b to "suppress blank section".

I think this will work, although you may end up with some unwanted white space, since you cannot suppress detail_a.

-LB

 

[brek2]

You’re right, it is slower, but IT WORKS.

THANK YOU, LB!