Report Model/Builder

In a database there are 2 tables, one is student and the other is activities. A student can have multiple activities. There is a one to many relationship between student and activities based upon student_id. Not every student has an activity.
In report builder I would like all of the students to appear regardless of whether they have an activity or not. By default, only those students who have activities appear.

This appears:
Student Activity
1 Track
2 Track
4 Volleyball

I would like to see:
Student Activity
1 Track
2 Track
3 NULL
4 Volleyball

What settings need to be tweaked to make this happen?

Thanks for your assistance.Hi

What query are you using to generate this report? I created 2 tables: student, activity. Not all students have activities. If I use the following query:

select * from student
left outer join activity on student.i = activity.i

where i = student id in both the tables,
I get the list of students, activities (even for students who dont have activities mapped on the activity table)

The Left Outer Join operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.

Is this what you were looking for?

Thanks

|||Within Report Designer you can create a query and specify any type of join you need. This is IN REPORT BUILDER, where you are not able to specifiy the type of join you would like to use. The query is generated from the report model and dragging fields into the design area.

Thanks for your response though.|||Hi Chris

Can you try this out and let me know if this works?

Create a report model and within the data source view, right click and add a NEW NAMED QUERY, here you can specify a query with joins. Once this query is created, create the report model. This should also list the query that you have created, apart from the 2 tables.

Now from Report Builder, select this datasource and drag/drop your query.
Remove the columns that you dont want (I just had student id and activity)
and run report.

This will give you all records, even the students who dont have activities listed.

If you are just using a report model, I think you need to edit that and add a query into that, so you can use it in your report builder to get the required data.

Thanks.|||

Actually, you don't need to create a named query to do this. You can do it in the Report Model. In your example, you need to set Cardinality=OptionalMany on the role from Student to activity. This tells RB that not all students have activities (so don’t do an inner join or you might lose a few).

Report Format with a Group

Is there a way to have the data rows begin on the same row as the group fields. For example if I have a student with multiple tests/scores, I want the tests/score to begin on the same line as the students name.

You could place everything on a detail row...basically no grouping level. This will display the student name as a repeating element, however.

You can then add some conditional logic to the Hidden property to only display the first occurrence of that student name.

|||Do you have an example of the logic to hide all but the first occurance? The programming side of this is a little new to me.|||

Check out the HideDuplicates feature of TextBox: http://msdn2.microsoft.com/en-us/library/ms152916.aspx.

In ReportDesigner, right click on your textbox and check "Hide duplicates" in the Textbox Properties dialog.

Report Format with a Group

Is there a way to have the data rows begin on the same row as the group fields. For example if I have a student with multiple tests/scores, I want the tests/score to begin on the same line as the students name.

You could place everything on a detail row...basically no grouping level. This will display the student name as a repeating element, however.

You can then add some conditional logic to the Hidden property to only display the first occurrence of that student name.

|||Do you have an example of the logic to hide all but the first occurance? The programming side of this is a little new to me.|||

Check out the HideDuplicates feature of TextBox: http://msdn2.microsoft.com/en-us/library/ms152916.aspx.

In ReportDesigner, right click on your textbox and check "Hide duplicates" in the Textbox Properties dialog.