Groups problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Groups problem

Author

Topic

RobertParker
Starting Member

10 Posts

Posted - 2007-11-17 : 09:25:38

I have a problem with displaying a report.

It is supposed to look like this:
Location 1
Address 1
Address 2
Address 3
Contact 1
Contact 2

Instead it displays:
Location 1
Address 1
Contact 1
Contact 2
Address 2
Contact 1
Contact 2
Address 3
Contact 1
Contact 2

In the database, any location can have several addresses or contacts. The query returns this when it runs.
Location 1, Address 1, Contact 1
Location 1, Address 1, Contact 2
Location 1, Address 2, Contact 1
Location 1, Address 2, Contact 2
Location 1, Address 3, Contact 1
Location 1, Address 3, Contact 2

I know I need to creatively group this someway to get it to display right, but I can't figure it out.

tm
Posting Yak Master

160 Posts

Posted - 2007-11-23 : 11:41:01

Use table item and create group
for Location called "table1_Locations" as the first group
and also create a group for Address as the second group.

Select the detail line where the contacts will be displayed and put the expression below into Visibility >> Hidden property.

=IIF(Fields!Addresses.Value <> Last(Fields!Addresses.Value, "table1_Locations"), True, False)

This will work for your data but will NOT work if your data changes to

Location 1, Address 1, Contact 1
Location 1, Address 1, Contact 2
Location 1, Address 2, Contact 1
Location 1, Address 2, Contact 2
Location 1, Address 2, Contact 3 << added contact
Location 1, Address 3, Contact 1
Location 1, Address 3, Contact 2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources