removing duplicates

[Armyuser]

Hello everyone,
   I have a report that is supposed to show report dates for those personnel that have one, and blank data for those who don't.  But, what I'm getting is 2 rows for each person that has a report date.  One shows the report date and the other shows no data.  How can I get the report to show only the report date for those who have one while still showing blank data for those who don't?  I want only one row of data for each person.

[wyconian]

Hi

What's your source?  Is it a flat database or a cube / DMR?

It sounds like there are multiple records for your personnel, check if that's true by looking at teh udnerlying data.  If it is you'll need to come up with some kind of filter that restricts the data to what you want it to be.

The other option which may be easier but isn't as 'correct' is to aggregate the date records so you get the max(date) for the person.  This should give you the actual date where there is a date and a blank and will just give you the blank where there is no date.

Good luck :-)

[Armyuser]

Good morning and thank you for your quick reply.  Please forgive my ignorance here, but I have a fairly basic knowledge of the query tool and a very limited knowledge of the report studio.  I believe that my source is a flat database.  I believe that you are correct about the multiple records, or lines of data, for each person.  If they have a report date, an additional line of data is created instead of just putting the report date in the blank line.  I could filter out blank data, but not all of the personnel I'm querying have a report date and I want to see them as well.  I don't know of another way to filter the data where I get just blanks for those without a report date and a single line of data for those with a report date.  I decided to try to build a report in report studio because it's more versatile, and I hoped that it would allow me to display the data the way I want to.  I will look at the aggregate function and see if it will work for me.

Again, thank you for the assist.  I appreciate it.

[awilbourn]

If you have a mix of data for a person, where there is a blank (NULL) and then a date. You might want to try to create a data item and use the maximum (<date column here>) and is if that works out for you.

[wyconian]

Another thing to try is having a query that has all your people in it and then another query that just has people with a date.  If you join these two queries together you can have an outer join so you get all people coming back form query 1 and then the dates from query 2 which should give you a blank for the people that don't have dates.

[Armyuser]

Thank you all for the help.  I was able to get the data that I wanted by playing around with the aggregate function.  When I get some time, I'll try the other suggestions to see if they work better or quicker.