SSRS: Dynamic Columns

SSRS: Dynamic Columns

Not knowing how many columns will be returned for any given report at runtime is a problem. At least you would think! Easy approach, make the column names dynamic!

Most of the time we lay out our reports with static columns and populate them with returned values. In this case we are populating the column names with returned values. The Matrix lay out is the the way to go because of the physical column row setup.

 In this case we are using QuestionDisplay as the column header and PrimaryAnswer as the value in the row for that column. The report will populate the number of columns based on the number of values returned for QuestionDisplay. We will then use SELECT DISTICT to manage this sprawl.

The DataSet Query:

SELECT

DISTINCT

vw_SurveySummary

.SurveyID

,

vw_SurveySummary.SurveyName

,

vw_SurveySummary.SectionOrder

,

vw_SurveySummary.SectionName

,

vw_SurveySummary.QuestionNumber

,

vw_SurveySummary.QuestionSubNumber

,

vw_SurveySummary.QuestionDisplay

,

vw_SurveySummary.QuestionRank

,

vw_SurveySummary.QuestionSummary

,

vw_SurveySummary.QuestionMultiAnswer

,

ISNULL(vw_AnswerDetail.UnitCode,'') AS UnitCode

,

ISNULL(vw_AnswerDetail.PrimaryAnswer,'') AS PrimaryAnswer

FROM

ICQsurvey2.dbo.vw_SurveySummary vw_SurveySummary

LEFT

OUTER JOIN ICQsurvey2.dbo.vw_AnswerDetail vw_AnswerDetail ON vw_SurveySummary.QuestionID = vw_AnswerDetail.QuestionID

WHERE

SurveyName='2011 ICQ' AND QuestionNumber IS NOT NULL

Results Returned:

Creating the Report:

I typically use the report wizard as a starting place for a new report.

The grouping will make all the differance here, follow these guidlines:

• Group the row by your Filter, UnitCode in this example.
• Group your Column by your header value, Display Question.

         
Results:
The values for your row under each column will coordinate to the line value that was returned.