Do distinct inside SSRS report

Question

Do distinct inside SSRS report

mtz676

SSCrazy Eights

Points: 9181
More actions
June 15, 2016 at 1:20 am

#311185

My existing SQL
returns these values in the dataset
select a,b,c,d,e,f,g,h from tableA
1 2 3 4 5 6 7 8
1 2 3 4 9 8 6 5
1 2 3 4 6 3 1 2
1 2 3 4 9 3 1 0
8 6 5 4 2 3 4 5
But in the report I am displaying only
a,b,c,d so this is resulting in duplicates being displayed.
How do I do distinct inside the SSRS report
so that I only display
1 2 3 4
8 6 5 4
as the result.
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

tindog SSCarpal Tunnel Points: 4892 More actions · Answer 1

Change your dataset to only use the fields you need. Or use grouping if that's not possible.

Btw you may want to reconsider your database design if that is truly the layout of your table and not the result of a function/sp/view etc. Can make things easier for you in the long run.

ivan.ho.hs SSC Enthusiast Points: 142 More actions · Answer 2

If the above isn't possible you can always treat your current query as a subquery and do

select distinct * from (subquery) A

Alan Burstein SSC Guru Points: 61136 More actions · Answer 3

There are some scripts out there for how to add custom code to your report, you can also handle this through doing some SSRS grouping but each of these options will be painfully more difficult than it should be to get a unique set of values.

As has been said, the only good way to do this is by changing the underlying SQL. There's no reason that should not be an option. As ivan said, you could just use the existing query as a subquery and run:

select distinct a,b,c,d from (subquery) A

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

MMartin1 One Orange Chip Points: 27774 More actions · Answer 4

This is just to illustrate the concept outside of SSRS

Create table #temp (

colA tinyint,

colB tinyint,

colC tinyint,

colD tinyint,

colE tinyint,

colF tinyint,

colG tinyint,

colH tinyint

)

/* inssert some test data into our table */

insert into #temp

SELECT 1,2,3,4,

floor((rand()*100)),

floor((rand()*100))

GO 7

insert into #temp

SELECT 8,6,5,4,

floor((rand()*100)),

floor((rand()*100))

GO 3

--/*[optional] take a look at the data --> */ SELECT * from #temp

/* output */

select distinct colA, colB,colC,colD

from #temp

;

-- drop table #temp

----------------------------------------------------