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
OptionDisplaySequence
(1)
ListDisplaySequence
(1)
SelectedYN
(1)
Report
(1)
OptionName
(1)
ListName
(1)
Dataset
(1)
Matrix
(1)
  BLeppert replied to BLeppert
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
Create New Account
help
Particular query problem SQL Server Hi, I have 2 similar tables, like these: Create Table Report(Month int, Value float) Create Table ReportCorrect(Month int, Value float) with these values: Insert Report values(1, 16), (2, 11), (3, 12), (4, 15), (5, 0), (6, 34), (7, 56 34), (7, 56), (8, 14), (9, 24), (10, 31), (11, 12), (12, 14) In the Report table, for the months 5 (May) and 10 (October), I have Value = 0. Now I third table too. But if I find two months with value = zero in the table Report, I have to sum them with the first month with non zero value. Luigi This Server 2000: SELECT month, SUM(value) AS value FROM ( SELECT R1.Month, C.Value FROM Report AS R JOIN ReportCorrect AS C ON R.Month = C.Month AND R.Value = 0 LEFT JOIN Report AS R1 ON R1.Month > R.Month AND R1.Value <> 0 AND NOT EXISTS(SELECT FROM Report AS R2 WHERE R2.Month < R1.Month AND R2.Value <> 0 AND R2.Month > R.Month) UNION ALL SELECT C.Month, C.Value FROM Report AS R JOIN ReportCorrect AS C ON R.Month = C.Month AND R.Value <> 0
Drillthrough report using "Jump to URL" quits working when Parent Report Parameter values are changed SQL Server I have a summary report that lists totals and the report has the ability to drillthrough to another "details" report by clicking the numbers on the summary report. The details report is rendered in a separate window using details report. This has worked fine for some time, but my problem is I need to modify
Report Parameters SQL Server I am familiar with other reporting tools like crystal and access but am new to ssrs. It seems like I have everything setup right but the report is only returning one record. I am passing three parameters to a report. The three parameters are @as_of_date, with available values set to none, default values set to to the id, and the label field set to the name. When I view the report all the parameters are displayed and I am able to enter data. For the as it returns the same number of records. I dragged a couple of field onto the report and the report runs, but only one record is ever returned. Am I missing something simple? I would like to confirm the report is a detail report, the fields look like they are in the body of the report. is there a
RDLC 2008 Error in print layout mode SQL Server Hi I have converted a local report in a windows form application from VisualStudio 2008 to VisualStudio 2010. In the report viewer the report is correctly visualized, but, sometimes, in print layout (or if i print the report) I obtain this exception: "An error has occurred during report processing" "Index was outside the bounds of the array". The report is binding to a object data source, if i delete some object from my data SQL Server Programming Discussions Error (1) Exception (1) Charles Wang Hi (1) Giorgio Parmeggiani (1) Report (1) Multiple threads (1) Local report (1) Data source (1) Hi Giorgio, Is your object in your data source shared by
SSRS 2008 SQL Server Hi All, I have a report with nested sub reports and these sub reports are toggled by parent report items. for Example: Country List - -- (Main Report) State list - -- (sub report of Country list report) - -- report results based on country key input. . . County list - -- (sub report of State list) - -- report results based on state key input. . . when I run the report showing all countries list