SSRS possible to print 1 row of detail with 2 rows in dataset

Question

Need to print values from multiple rows in the dataset to a single row in the details based on main group, questiontab, row, and column IDs.

Looking for advice on if this is possible in SSRS, possibly using nested tables or lookup/lookupset/join?

Unable to add any functions or procs at the DB level, limited to the existing dataset format.

Column # is limited to 1 or 2 only

Dataset

| MainGroup | QuestionTab | QuestionRow | QuestionColumn | Question    | Answer    |
|-----------|-------------|-------------|----------------|-------------|-----------|
| Group1    | TabA        | 1           | 1              | Question1   | Answer1   |
| Group1    | TabA        | 1           | 2              | Question2   | Answer2   |
| Group1    | TabA        | 2           | 1              | Question3   | Answer3   |
| Group1    | TabA        | 2           | 2              | Question4   | Answer4   |
| Group1    | TabB        | 1           | 1              | Question234 | Answer234 |
| Group1    | TabB        | 2           | 1              | Question789 | Answer789 |

Current Output, tried various groupings and expressions with lookup, lookupset, join

| MainGroup | QuestionTab | QuestionRow | Question1output | Answer1output | Question2output | Question2output |
|-----------|-------------|-------------|-----------------|---------------|-----------------|-----------------|
| Group1    | TabA        | 1           | Question1       | Answer1       |                 |                 |
| Group1    | TabA        | 1           |                 |               | Question2       | Answer2         |
| Group1    | TabA        | 2           | Question3       | Answer3       |                 |                 |
| Group1    | TabA        | 2           |                 |               | Question4       | Answer4         |
etc etc

Desired Output

| MainGroup | QuestionTab | QuestionRow | Question1output | Answer1output | Question2output | Question2output |
|-----------|-------------|-------------|-----------------|---------------|-----------------|-----------------|
| Group1    | TabA        | 1           | Question1       | Answer1       | Question2       | Answer2         |
| Group1    | TabA        | 2           | Question3       | Answer3       | Question4       | Answer4         |
| Group1    | TabB        | 1           | Question234     | Answer234     |                 |                 |
| Group1    | TabB        | 2           | Question789     | Answer789     |                 |                 |

Alan Schofield Alan Schofield · Accepted Answer · 2021-05-17T21:34:22

This should be fairly simple.

I first recreated your dataset with the following...

DECLARE @t TABLE (MainGroup varchar(20), QuestionTab varchar(20), QuestionRow int, QuestionColumn int, Question varchar(20), Answer varchar(20))

INSERT INTO @t VALUES 
('Group1', 'TabA', 1, 1, 'Question1', 'Answer1'),
('Group1', 'TabA', 1, 2, 'Question2', 'Answer2'),
('Group1', 'TabA', 2, 1, 'Question3', 'Answer3'),
('Group1', 'TabA', 2, 2, 'Question4', 'Answer4'),
('Group1', 'TabB', 1, 1, 'Question234', 'Answer234'),
('Group1', 'TabB', 2, 1, 'Question789', 'Answer789')

SELECT * FROM @t

Next I added a matrix control with 1 row group and 1 column group.

The row group has to group on all the columns that make the row unique, in this case that's MainGroup, QuestionTab, and QuestionRow

Note: The group names below are not important, they were called that by default as I started by dragging the main group column to the matrix control.

The column group needs to group just on QuestionColumn

Add extra columns (not groups) by right-clicking the first column and choosing insert--inside group--right, repeat so that you have 3 columns in the row group.

In the column group column (the last column) , insert a new column inside the group in the same way.

You should now have 5 columns in the design. Assign each column as shown in the image below.

To get the custom column headers, first I had to split the cell as it was merged by default, and then set the expressions to

="Question " & Fields!QuestionColumn.Value & " output"

and

="Answer " & Fields!QuestionColumn.Value & " output"

respectively. Finally a tiny bit of formatting on the header and this is the result.

SSRS possible to print 1 row of detail with 2 rows in dataset

1 Answers