How to group by all the adjacent same value?

Question

there are have the below details table. 

User Name	TimeStamp	Lat	Lon	Address
[e-mail]	1/1/2010 10:20:00	5514831	86438438	City 1
[e-mail]	1/1/2010 11:20:00	1214831	23438438	Town2
[e-mail]	1/1/2010 12:20:00	1214831	23438438	Town2
[e-mail]	1/1/2010 13:20:00	1214831	23438438	Town2
[e-mail]	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.

[e-mail]
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) & " - " & FormatDateTime(Max(Fields!TimeStamp.Value), 4), below result is not right.

[e-mail]
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.

Answer 1

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 Address As System.Collections.Hashtable

Dim sumGroup as Integer 

 

Function MyFunc(ByVal _Address  As Object) As integer

Dim flag as integer

If (Address Is Nothing) Then

Address = New System.Collections.Hashtable

End If

If (Not Address .Contains(_Address)) Then

Address .clear()

sumGroup = sumGroup + 1

Address .Add(_Address, nothing)

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

 

You can download RLD file from:

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

 

Hope this helps,

Raymond

---

Raymond Li - MSFT

Answer 2

Thanks Raymond, It is just what I wanted.