How do I eliminate the duplicates.

[rahulroy08]

Hi

I'm using CR 8.5 and SQL Server 2005.

In my report when I run it for some employees it gives me duplicates. How do I eliminate them.

I have developed a report which looks something like this:

Emp_NAME (Main_Grp)
 
  Peak_Flag (sub-grp1)
  2007        fld1 fld2 fld3
  2008        fld4 fld5 fld6 (all are diff fields on the dbase)
  2009        fld7 fld8 fld9 (all are diff fields on the dbase)
          ...
          ...
          -------------------------------------------------
sub-total1 (fld1+fld4+fld7)(fld2+fld5+fld7)(fld3+fld6+fld9)

Peak_Flag (sub-grp2)
  2007        fld1 fld2 fld3
  2008        fld4 fld5 fld6 (all are diff fields on the dbase)
  2009        fld7 fld8 fld9 (all are diff fields on the dbase)
          ...
          ...
          -------------------------------------------------
sub-total2 (fld1+fld4+fld7)(fld2+fld5+fld7)(fld3+fld6+fld9)


(Grp-footer)
 
total
sub-total1-points (=sub-total1)
sub-total2-points (=sub-total2 * 2)
Total Points (=stotal-pts1 + stotal-pts2)

But for some employees it shows as:

Emp_NAME (Main_Grp)
 
  Peak_Flag (sub-grp1)
  2007        fld1 fld2 fld3
  2008        fld4 fld5 fld6 (all are diff fields on the dbase)
  2009        fld7 fld8 fld9 (all are diff fields on the dbase)
          ...
          ...
  2007        fld1 fld2 fld3
  2008        fld4 fld5 fld6 (all are diff fields on the dbase)
  2009        fld7 fld8 fld9 (all are diff fields on the dbase)
          ...
          ...
  2007        fld1 fld2 fld3
  2008        fld4 fld5 fld6 (all are diff fields on the dbase)
  2009        fld7 fld8 fld9 (all are diff fields on the dbase)
          ...
          ...

         -------------------------------------------------
sub-total1 (fld1+fld4+fld7)(fld2+fld5+fld7)(fld3+fld6+fld9)

Peak_Flag (sub-grp2)
  2007        fld1 fld2 fld3
  2008        fld4 fld5 fld6 (all are diff fields on the dbase)
  2009        fld7 fld8 fld9 (all are diff fields on the dbase)
          ...
          ...
  2007        fld1 fld2 fld3
  2008        fld4 fld5 fld6 (all are diff fields on the dbase)
  2009        fld7 fld8 fld9 (all are diff fields on the dbase)
          ...
          ...
 
        -------------------------------------------------
sub-total2 (fld1+fld4+fld7)(fld2+fld5+fld7)(fld3+fld6+fld9)


(Grp-footer)
 
total
sub-total1-points (=sub-total1)
sub-total2-points (=sub-total2 * 2)
Total Points (=stotal-pts1 + stotal-pts2)

I checked my Database for the data and it shows some duplicate rows.And also I have already checked the box which says select distinct records.(Database-->select distinct records). but no good.

Also my report prompts me 4 parameters
1. organization 2. arrival date 3.start date 4. end date

and my record selection formula has the following...

{SMPR_GOALS.NAME_PROPERTY}={?Organization} and
{SMPR_MART.DATE_BEGIN} >= {?Activity_StartDate}and
{SMPR_MART.DATE_END} <= {?Activity_EndDate}and
{SMPR_MART.ARRIVE_AFTER} >= {?ArrivalDate}

Hope this info helps.

Can someone help me with this.



Rahul.