none
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

    g@g.com

    1/1/2010 11:20:00

    1214831

    23438438

    Town2

    g@g.com

    1/1/2010 12:20:00

    1214831

    23438438

    Town2

    g@g.com

    1/1/2010 13:20:00

    1214831

    23438438

    Town2

    g@g.com

    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) & " - " & 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

Answers

  • 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
    • Marked as answer by Gary An Thursday, June 03, 2010 6:22 AM
    Tuesday, June 01, 2010 9:05 AM
    Moderator

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 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
    • Marked as answer by Gary An Thursday, June 03, 2010 6:22 AM
    Tuesday, June 01, 2010 9:05 AM
    Moderator
  • Thanks Raymond, It is just what I wanted.
    Thursday, June 03, 2010 6:27 AM