Search code examples
reportssrs-2008

Duplicate records in chart ssrs

I have 2 datasets as below:

select Name, CIType, count() as NumDuplicates from CI group by Name, CIType having count() > 1;

select Name, CIType, count() as NumNonDuplicates from CI group by Name, CIType having count() = 1;

The first one identifies duplicate entries while the second one identifies unique entries.

What I would like to do is to combine the results of both datasets in one chart as below:

enter image description here

The chart would show the duplicate and non duplicate entries for each CI type.

Whatever I have tried so far is not working...

Any help/guidance would greatly be appreciated.

Solution

  • You can't easily use two datasets in a single chart like that. It would be much easier if you did this in your dataset query.

    You could use a single query

    SELECT [Name], CIType, count() as RowCount 
    , IIF(Count(*) >1 , 1, 0) AS HasDuplicates
FROM CI 
GROUP BY [Name], CIType

    This will give you the counts and a column that indicates whether there are duplicates or not.

    You could swap you the 1, 0 for something more meaningful that you could use in the chart legend e.g., IIF(Count(*) >1 , "Has Duplicates", "None Duplicate") AS HasDuplicates