CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2ssrs-2012
SkyeBoniwell
SkyeBoniwell

Reputation: 7092

combining multiple results into one row in SSRS

I have a simple survey that I need to report on using SSRS.

It has 2 questions. One question can have multiple selections like below:

What is your name?

Pick your favorite colors (Red, Blue, Green, Yellow, Brown, Purple)?

Here are sample query results:

Tim       Red
Tim       Blue
Tim       Green
Mary      Yellow
George    Brown
George    Red
Jill      Purple

I'd like to combine them like this so that they all appear on one row in the report showing the selected answers in bold(or a different color) and the unselected answers just as plain text.

Tim: Red, Blue, Green, Yellow, Brown, Purple

Mary: Red, Blue, Green, Yellow, Brown, Purple

George: Red, Blue, Green, Yellow, Brown, Purple

Jill: Red, Blue, Green, Yellow, Brown, Purple

I tried using this expression in my report:

Join(Fields!favoriteColors.Value, ", ")

However I would just see #Error in my report.

Is something like this possible in SSRS 2008R2?

Upvotes: 0

Views: 1709

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

Unfortunately the JOIN doesn't work on a Grouping basis like you would think.

You can cheat and do it this way in your situation:

=JOIN(LOOKUPSET(Fields!Name.Value, Fields!Name.Value, Fields!Color.Value, "DataSet1"), ", ")

This will look up the values in the dataset based on the Name and combine the Colors with a comma seperator.

But this will only get the selected colors - not all colors. I think you would need another query for unpicked colors and combine them. You can use HTML markup for the boldening.

="<b>" & JOIN(LOOKUPSET(Fields!Name.Value, Fields!Name.Value, Fields!Color.Value, "DataSet1"), ", ")
& "</b>" & JOIN(LOOKUPSET(Fields!Name.Value, Fields!Name.Value, Fields!Color.Value, "DataSet2"), ", ")

Query for unpicked colors:

SELECT Name, Color FROM 
(select DISTINCT Color from #COLORS ) C
CROSS JOIN 
(select DISTINCT Name from #COLORS ) N 
EXCEPT SELECT Name, Color FROM  #COLORS

Upvotes: 1

Related Questions