CODEWITHSUNDEEP
sql-serverreporting-servicesgroup-bypivot
Andrew Jahn
Andrew Jahn

Reputation: 667

ssrs combining rows into result set

So I have a sql server 2005 query that returns results like so:

Address | Doctor
-----------------------
1         Dr A
1         Dr B
1         Dr C
2         NULL
3         NULL
4         Dr D
4         Dr E
5         Dr F

What I want is to get the output so that when I render the report I have them grouped together:

Dr A
Dr B
Dr C
Address1

Address2

Address3

Dr D
Dr E
Address4

Dr F
Address5

The problem is right now in my SSRS report I group by Address and it is only listing the first Dr and if the location has more then 1 Dr then they are not being listed.

Upvotes: 0

Views: 1167

Answers (3)

sama
sama

Reputation: 11

You can use group on property of ssrs..

If u have all the fields(address and doctor in ur example) on a table.. right click the table row and in the group on section, write the following (=Fields!Doctor.value) which says group by Doctor

It acts as a (group by) in sql.

Upvotes: 1

Andrew Jahn
Andrew Jahn

Reputation: 667

In order to do this on the sql side (which was required to render the report correctly) I had to use Coalesce which my exact situation was detailed in this link:

Coalesce example grouping up multiple rows

Upvotes: 0

Raj More
Raj More

Reputation: 48018

Starting out, it looks like you have a LEFT join between the Doctors and the Address.

I think you should group by Doctor and put the Address into the detail section and they should all appear properly.

Upvotes: 1

Related Questions