We help IT Professionals succeed at work.

How to not show duplicate records SSRS

WJM
WJM asked
on
I have a report on employee headcount, I am trying to show the distinct count of employees along with their information and their room assignment; however, some employees are assigned multiple rooms and therefore show up more than once, I would like to see the multiple rooms; however, I only want to see the employee listed one time.  I am attaching an image that will hopefully help.  I don't know if it's Friday or my head still being in Crystal Reports but I'm missing how to set this up properly in SSRS/BIDS, thank you.

Bill
Example of what I don't want
Comment
Watch Question

Can you post your query?

Commented:
you can change your backend query to concate the room number separated by comma or any delimeter provided that values in other columns are same.
BRONZE EXPERT
Commented:
You need grouping. Same as you did with the department. Every department is only shown once, with the underlying employees. When you group on employee also you'll get every employee once. The wizard probably won't create the report exactly as you want it, but try to get as close as you can and then adjust it to get what you want.
There is a hide duplicates also, but that's risky. Suppose you have two employees with the same title (like in your example) then the title will be hidden.
WJM

Author

Commented:
Thank you everyone.

Nicobo, it was the hide duplicates feature that worked for me.  In order to get my count properly I used

=CountDistinct(Fields!EMPID.Value)

This way it didn't add the duplicate values if the employees were assigned multiple rooms.  Thanks again for your help.

Bill

Explore More ContentExplore courses, solutions, and other research materials related to this topic.