CODEWITHSUNDEEP
asp.netreporting-servicessql-server-2008-r2
David Megnin
David Megnin

Reputation: 79

How to Concatenate strings conditionally for display in SSRS

In SSRS, how could I create a field, similar to a "comments" field in a record that would list a set of strings, conditionally, depending on how a set of questions on a VB.NET web form are answered?

Say, 5 questions have RadioButtonLists with "Y", "N", and "N/A" as the three answer items. The answers go into a SQL (Server 2008R2) table as a "Review" record.

Question #2 and #4 are answered "N", the rest are answered "Y" or "N/A."

I'm trying to get that comments field in SSRS to be populated with similar to the following:

Q2: 1100 - No Documentation in file.
Q4: 4100 - No Case note.

In SQL I've tried declaring a variable to hold a string and concatenating question string values based on field contents. I tried nested IIF expressions in the SSRS column, but I can't figure out how to do what you can do in C# or VB like string1 = string1 + string2 or string1 += string2, in order to selectively add the strings to a variable to then display.

I need an SSRS report for each of the forms and each record needs that comments field listing the "Findings" or questions with "N" answers.

Upvotes: 0

Views: 2272

Answers (1)

Jesse
Jesse

Reputation: 873

After your comments, I think I understand better. You want a comments column with the No values concatenated together with line breaks. You will have to adjust the below to match your tables but hopefully this helps.

select [AssignedOneActivity], 
       Comments = STUFF( ( 
                                SELECT char(10) + char(13) + a2.[AssignedOneActivity] + ': ' + a2.answer 
                                FROM dbo.[WIA_Youth] a2 
                                where a.ReviewID = a2.ReviewID 
                                FOR XML PATH(''), TYPE 
                                ).value('.', 'nvarchar(max)'), 1, 2, ''
                        ) 
from dbo.[WIA_Youth] a

However, I now read that your storing your Q+A in columns. so maybe this would be better:

select
    reviewid,
    Comments =
    case when [AssignedOneActivity] = 'N' then 'Q1: No answers' + char(10) + char(13) else '' end +
    case when [AssignedTwoActivity] = 'N' then 'Q2: No answers' + char(10) + char(13) else '' end +
    case when [AssignedThreeActivity] = 'N' then 'Q3: No answers' + char(10) + char(13) else '' end 
from dbo.wia_youth

Upvotes: 1

Related Questions