CODEWITHSUNDEEP
reporting-servicesssrs-2012
Lode Vlaeminck
Lode Vlaeminck

Reputation: 944

Horizontal and vertical row in 1 from

I have this problem with multiple rows and the same data in SSRS. My data set looks like this:

ID | name | related ID
1  | 1a   | 5
1  | 1a   | 6
1  | 1a   | 7

And i would like to get something like this as a result.

ID | Name 
1  | 1A
Related Items: 5 , 6 , 7

OR

ID | Name         
1  | 1A           
Related Items: 5  | 6 | 7 |

But now when i group my row on the ID I get : 

ID                | Name  | ...        
1                 | 1A    | ...       
Related Items: 5  | 6 | 7 |

Related Items: 5  | 6 | 7 |

Related Items: 5  | 6 | 7 |

Can someone explain could display only 1 row with the header data and 1 row with the related item

Upvotes: 1

Views: 69

Answers (1)

Ian Preston
Ian Preston

Reputation: 39566

If you're happy with this approach...

ID | Name 
1  | 1A
Related Items: 5 , 6 , 7

since you're using SSRS 2012 you can use the LookupSet function to get the string of related items for each ID and display this in a simple table.

I have a simple table:

enter image description here

With one Row Group based on ID.

The expression is:

="Related Items: "
    & Join
    (
        LookupSet
        (
            Fields!ID.Value
            , Fields!ID.Value
            , Fields!relatedID.Value
            , "DataSet1"
        )
        , ", "
    )

Which uses LookupSet to get a list of matching relatedID values for a given ID, then uses Join to turn the array returned by LookupSet into a string.

With your data:

enter image description here

This gives the expected results:

enter image description here

Upvotes: 3

Related Questions