CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2ssrs-2012reportbuilder3.0
SkyeBoniwell
SkyeBoniwell

Reputation: 7122

cells not lining up in each row

I have an SSRS Matrix report that I built in Report Builder 3.0. The data looks good, but the rows don't line up. Every cell is in a separate row making the report hard to follow and read.

Here is a screenshot:

Sample Report

Here's sample data that the report is based on:

SELECT 's111' AS sessionID, 'q1' AS questionID, 'q1_a1' AS answerID,
'True/False : The Sun orbits the Earth.' AS stem,
'True' AS SelectedItem, 'False' AS UnselectedItem
UNION ALL
SELECT 's111', 'q2', 'q2_a1',
'MCQ: What planet is the largest gas giant in our solar system?',
'Jupiter', 'Moon'
UNION ALL
SELECT 's111', 'q2', 'q2_a2',
'MCQ: What planet is the largest gas giant in our solar system?',
'Jupiter', 'Venus'
UNION ALL
SELECT 's111', 'q2', 'q2_a3',
'MCQ: What planet is the largest gas giant in our solar system?',
'Jupiter', 'Vulcan'
UNION ALL
SELECT 's111', 'q2', 'q2_a4',
'MCQ: What planet is the largest gas giant in our solar system?',
'Jupiter', 'Pluto'
UNION ALL
SELECT 's111', 'q3', 'q3_a1',
'MCQ/Many: Pick the items that best describe why Mars does NOT currently support complex life.',
'God hates Martians', 'Intense radiation from the Sun'
UNION ALL
SELECT 's111', 'q3', 'q3_a2',
'MCQ/Many: Pick the items that best describe why Mars does NOT currently support complex life.',
'God hates Martians', 'Very dry, little to no liquid water'
UNION ALL
SELECT 's111', 'q3', 'q3_a3',
'MCQ/Many: Pick the items that best describe why Mars does NOT currently support complex life.',
'God hates Martians', 'very cold'
UNION ALL
SELECT 's111', 'q3', 'q3_a4',
'MCQ/Many: Pick the items that best describe why Mars does NOT currently support complex life.',
'God hates Martians', 'Intense radiation from an ancient nuclear holocaust'

Is there a way to fix the rows?

Thanks!

Upvotes: 1

Views: 60

Answers (1)

Christopher M. Brown
Christopher M. Brown

Reputation: 2220

Okay, so I'm guessing this is some kind of test where you are querying the available answers? If so, then your problem is with the joins in your query. The problem is that the query is finding nulls for the other questions each time it returns the answers for a given question. My guess would be that your SQL looks something like this:

Select s.student_name
    , q.question_txt
    , a.answer_txt

From student_table s
    left outer join question_table q
        on --something
    left outer join answer_table a
        on q.question_id = a.question_id

When you're cross-referencing the question ID with the answer ID, you're returning nulls in each row except where the question ID matches the answers returned for that question.

There are two basic solutions, one simple and one more complex. If you happen to have an auto-incrementing number for ever answer within a question (i.e., answer 1 has a value of 1 always, answer 2 has a value of 2 always), then you can simply add that number to your query and have row groups for the child's name and that auto-incrementing number.

If you don't have such a number in your data already, you can artificially create one by adding the following field to your data set and add a group to your tablix for it:

row_number() over (partition by question_id order by question_id, answer_id) as row

If I'm completely off base here in my assumptions, let me know via a comment and I'll do my best to help.

Upvotes: 2

Related Questions