# How to group by all the adjacent same value?

• ### Question

• there are have the below details table.

 User Name TimeStamp Lat Lon Address g@g.com 1/1/2010 10:20:00 5514831 86438438 City 1 1/1/2010 11:20:00 1214831 23438438 Town2 1/1/2010 12:20:00 1214831 23438438 Town2 1/1/2010 13:20:00 1214831 23438438 Town2 1/1/2010 14:20:00 5514831 86438438 City 1

I want to list a user's gps log in one day, this should group by TimeStamp.Date and Address,the result is expected should be as below.

 g@g.com 1/1/2010 10:20-10:20 City 1 11:20-13:20 Town 2 14:20-14:20 City1

but, if group by Date and Address, the result as below. for the time range I use FormatDateTime(Min(Fields!TimeStamp.Value), 4) &amp; " - " &amp; FormatDateTime(Max(Fields!TimeStamp.Value), 4), below result is not right.

 g@g.com 1/1/2010 10:20-14:20 City 1 11:20-13:20 Town 2

How to make a distinction between adjacent same address in a day? and get the second result table belove.

Monday, May 31, 2010 2:44 AM

• Hi Gary,

Do not group by “Address”. You need a custom function to group the recodes. Try this:

1)    Add the Below Code under the custom Code Tab in ReportProperties menu.

Dim sumGroup as Integer

Function MyFunc(ByVal _Address  As Object) As integer

Dim flag as integer

End If

sumGroup = sumGroup + 1

else

sumGroup  = sumGroup

End If

MyFunc = sumGroup

End Functio

2)    Create a group with expression =Code.MyFunc(Fields!ID_Address.Value)

I created a demo for you with the dummy dataset:

SELECT     '1/1/2010 10:20:00' AS date, 'City 1' AS _Address

UNION ALL

SELECT     '1/1/2010 11:20:00' AS date, 'Town2' AS _Address

UNION ALL

SELECT     '1/1/2010 12:20:00' AS date, 'Town2' AS _Address

UNION ALL

SELECT     '1/1/2010 13:20:00' AS date, 'Town2' AS _Address

UNION ALL

SELECT     '1/1/2010 10:20:00' AS date, 'City 1' AS _Address

http://cid-75f1f331db7892bf.skydrive.live.com/self.aspx/.Public/SAMPLE/group.rdl

Hope this helps,

Raymond

Raymond Li - MSFT
• Marked as answer by Thursday, June 3, 2010 6:22 AM
Tuesday, June 1, 2010 9:05 AM

### All replies

• Hi Gary,

Do not group by “Address”. You need a custom function to group the recodes. Try this:

1)    Add the Below Code under the custom Code Tab in ReportProperties menu.

Dim sumGroup as Integer

Function MyFunc(ByVal _Address  As Object) As integer

Dim flag as integer

End If

sumGroup = sumGroup + 1

else

sumGroup  = sumGroup

End If

MyFunc = sumGroup

End Functio

2)    Create a group with expression =Code.MyFunc(Fields!ID_Address.Value)

I created a demo for you with the dummy dataset:

SELECT     '1/1/2010 10:20:00' AS date, 'City 1' AS _Address

UNION ALL

SELECT     '1/1/2010 11:20:00' AS date, 'Town2' AS _Address

UNION ALL

SELECT     '1/1/2010 12:20:00' AS date, 'Town2' AS _Address

UNION ALL

SELECT     '1/1/2010 13:20:00' AS date, 'Town2' AS _Address

UNION ALL

SELECT     '1/1/2010 10:20:00' AS date, 'City 1' AS _Address

http://cid-75f1f331db7892bf.skydrive.live.com/self.aspx/.Public/SAMPLE/group.rdl

Hope this helps,

Raymond

Raymond Li - MSFT
• Marked as answer by Thursday, June 3, 2010 6:22 AM
Tuesday, June 1, 2010 9:05 AM
• Thanks Raymond, It is just what I wanted.
Thursday, June 3, 2010 6:27 AM