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).