Solved

Suppressing duplicate data in SSRS

Posted on 2011-03-02
6
2,637 Views
Last Modified: 2012-05-11
I would like to suppress duplicate data from columns on a report. i cannot find a simple way to do this. in crystal I could select that option, is there an expression for that in SSRS?
The duplicates are showing in the second one down (1009216) for the Location, Type, Persons involved, and App@Scene. I would only like to show them in the first row for that Inc Number 1009216. is this possible?
Duplicate Sample
0
Comment
Question by:srodgers45
  • 3
  • 2
6 Comments
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 35023103
What version of SSRS are you on?  This is from 2008, you can set the HideDuplicates propery on cells and rows on the Property sheet.
I believe it was similar in 2005 but I do not have older instances to look at anymore to check.
HTH,
Chris HideDuplicates Property
0
 

Author Comment

by:srodgers45
ID: 35023824
When I try to hide duplicates on just one field I get the error below. If I do it for the entire dataset it gives another error: "The stream cannot be found.The stream identifier that is provided to an operation cannot be located in the report server database"  I am using 2008
error
This is what the report design looks like
report design
0
 
LVL 9

Expert Comment

by:sureshbabukrish
ID: 35038031
in 2005, use Previous function in expression

=IIF(Previous(IncLocation!Field.Value) = IncLocation!Field.Value,true,false) use this in visiblity property expression in the text box in detail section of IncLocation column
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:srodgers45
ID: 35038389
That works, unfortunately it suppresses the last column of the report I need to show the vaule for. I only want to supress duplicates for each unique record. Any thoughts?

Thank You

The bottom right should also have "True" next to E145, as it is a unique record, like it does next to "BC4" above
 Example
0
 
LVL 9

Accepted Solution

by:
sureshbabukrish earned 500 total points
ID: 35038436
use the column name in the expression which differenciates the two rows, i.e BC4,E143,E145,M145 WITH E145 IN THE SECOND ROW.

IIF(Previous(IncLocation!Field.Value) = IncLocation!Field.Value And Previous(IncNum!Field.Value) = IncNum!Field.Value, true,false)

i hope you understood what i meant.
0
 

Author Closing Comment

by:srodgers45
ID: 35038593
That worked, Thank You
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Steps to solve SSRS SQL 2008 R2 User Access Control (UAC) Permission Error With the introduction of SQL Server 2008 R2 and Vista (Windows 7 as well) came new enhanced security features. One of the features included was User Access Control (UAC) t…
A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

929 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now