Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Usually we design the table to have x number of rows and y number of columns in a report. But how can we create a report which adds the rows and columns dynamically at run time based on the result of the source query?

For example I want to list stdentId, StudentName and any course each student has enrolled in. As the number of courses is different from one person to the other, I should add the rows and related column for courses at run time based on the query result. How can it be done? For example:

enter image description here

Thanks for your help in advance.

share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

I think you got it half way there with the grouping on student ID. In your report, the group header should include the student ID and name fields, and the detail should include one row per course. You just need to format the layout/grid to make it look like the student ID/name is part of the detail data. Worst case, just add student name to the grouping.

share|improve this answer
add comment

I don't know how your report table is currently being designed, but I would think the report table list all the rows from the query results (with predefined number of columns, in your case, 2 columns). As long as the query is correctly developed to return all required rows e.g. all enrolled courses, the report table designing part should be fairly straight forward.

share|improve this answer
    
That's what I expected too but the current issue is when there are multiple courses, the original record gets repeated per course. Which is not what we need. Only the Course rows should be added, not the whole student record. I have a row group on StudentId, but it does not help. –  Sky May 17 '13 at 0:57
    
@nazilla, it sounds like the query was developed incorrectly, at least for this reporting purpose. If you have the choice, the much easier way is to fix the query to return the desired result (without duplicate original records) instead of trying to get around the problem at the report design. –  Travis Gan May 17 '13 at 4:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.