SQL Server - SSRS 2005 matrix using rows as headings

Asked By BLeppert

18-Mar-10 01:18 PM

Has anyone ever had to do this?  Display a matrix on an SSRS report using the
row data as a heading and repeating the column headings for each row?  I have
gotten pretty close using a matrix containing a rectangle in the Data area
which itself contains another matrix; however since the column data needs to
repeat, the "row  heading" does not span accross the entire report.

Below is an example - plus some generic SQL to generate the dataset
(ListName=Row, OptionName=Column, SelectedYN=Data).

Thanks for the help!
BLeppert

Standard Matrix:
Never | Almost Never | Sometimes | Fairly Often | Very Often
1. In the...                X
2. In the...     X
3. In the...                                                          X

The way it needs to be displayed on the report:

1. In the last month, was your favorite color green?
Never | Almost Never | Sometimes | Fairly Often | Very Often
X

2. In the last month, how often did you drive a car?
Never | Almost Never | Sometimes | Fairly Often | Very Often
X

3. In the last month, how often did you have a fantastic idea?
Never | Almost Never | Sometimes | Fairly Often | Very Often
X

Generic SQL:
SELECT
'1. In the last month, was your favorite color green?' ListName
, 'Never' OptionName
, NULL SelectedYN
, 1 ListDisplaySequence
, 1 OptionDisplaySequence
UNION SELECT
'1. In the last month, was your favorite color green?' ListName
, 'Almost Never' OptionName
, 1 SelectedYN
, 1 ListDisplaySequence
, 2 OptionDisplaySequence
UNION SELECT
'1. In the last month, was your favorite color green?' ListName
, 'Sometimes' OptionName
, NULL SelectedYN
, 1 ListDisplaySequence
, 3 OptionDisplaySequence
UNION SELECT
'1. In the last month, how often was your favorite color green?' ListName
, 'Fairly Often' OptionName
, NULL SelectedYN
, 1 ListDisplaySequence
, 4 OptionDisplaySequence
UNION SELECT
'1. In the last month, was your favorite color green?' ListName
, 'Very Often' OptionName
, NULL SelectedYN
, 1 ListDisplaySequence
, 5 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Never' OptionName
, 1 SelectedYN
, 2 ListDisplaySequence
, 1 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Almost Never' OptionName
, NULL SelectedYN
, 2 ListDisplaySequence
, 2 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Sometimes' OptionName
, NULL SelectedYN
, 2 ListDisplaySequence
, 3 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Fairly Often' OptionName
, NULL SelectedYN
, 2 ListDisplaySequence
, 4 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Very Often' OptionName
, NULL SelectedYN
, 2 ListDisplaySequence
, 5 OptionDisplaySequence
UNION SELECT
'3. In the last month, how often did you have a fantastic idea?' ListName
, 'Never' OptionName
, NULL SelectedYN
, 3 ListDisplaySequence
, 1 OptionDisplaySequence
UNION SELECT

18-Mar-10 01:25 PM

Typo in the SQL above (copy/paste error)

SELECT
'1. In the last month, how often was your favorite color green?' ListName
, 'Never' OptionName
, NULL SelectedYN
, 1 ListDisplaySequence
, 1 OptionDisplaySequence
UNION SELECT
'1. In the last month, how often was your favorite color green?' ListName
, 'Almost Never' OptionName
, 1 SelectedYN
, 1 ListDisplaySequence
, 2 OptionDisplaySequence
UNION SELECT
'1. In the last month, how often was your favorite color green?' ListName
, 'Sometimes' OptionName
, NULL SelectedYN
, 1 ListDisplaySequence
, 3 OptionDisplaySequence
UNION SELECT
'1. In the last month, how often was your favorite color green?' ListName
, 'Fairly Often' OptionName
, NULL SelectedYN
, 1 ListDisplaySequence
, 4 OptionDisplaySequence
UNION SELECT
'1. In the last month, how often was your favorite color green?' ListName
, 'Very Often' OptionName
, NULL SelectedYN
, 1 ListDisplaySequence
, 5 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Never' OptionName
, 1 SelectedYN
, 2 ListDisplaySequence
, 1 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Almost Never' OptionName
, NULL SelectedYN
, 2 ListDisplaySequence
, 2 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Sometimes' OptionName
, NULL SelectedYN
, 2 ListDisplaySequence
, 3 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Fairly Often' OptionName
, NULL SelectedYN
, 2 ListDisplaySequence
, 4 OptionDisplaySequence
UNION SELECT
'2. In the last month, how often did you drive a car?' ListName
, 'Very Often' OptionName
, NULL SelectedYN
, 2 ListDisplaySequence
, 5 OptionDisplaySequence
UNION SELECT
'3. In the last month, how often did you have a fantastic idea?' ListName
, 'Never' OptionName
, NULL SelectedYN
, 3 ListDisplaySequence
, 1 OptionDisplaySequence
UNION SELECT
'3. In the last month, how often did you have a fantastic idea?' ListName
, 'Almost Never' OptionName
, NULL SelectedYN
, 3 ListDisplaySequence
, 2 OptionDisplaySequence
UNION SELECT
'3. In the last month, how often did you have a fantastic idea?' ListName
, 'Sometimes' OptionName
, NULL SelectedYN
, 3 ListDisplaySequence
, 3 OptionDisplaySequence
UNION SELECT
'3. In the last month, how often did you have a fantastic idea?' ListName
, 'Fairly Often' OptionName
, NULL SelectedYN
, 3 ListDisplaySequence
, 4 OptionDisplaySequence
UNION SELECT
'3. In the last month, how often did you have a fantastic idea?' ListName
, 'Very Often' OptionName
, 1 SelectedYN
, 3 ListDisplaySequence
, 5 OptionDisplaySequence
ORDER BY
ListDisplaySequence
, OptionDisplaySequence