Prev: SSRS2005, matrix but concat keys for last group level ?
Next: Quickly changing RDL files
From: BLeppert on 18 Mar 2010 13:18
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've
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" doesn't 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
'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
From: BLeppert on 18 Mar 2010 13:25
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
 | 
Pages: 1
Prev: SSRS2005, matrix but concat keys for last group level ?
Next: Quickly changing RDL files