Collapsing a many-to-many relationship in a report model
Often a many-to-many relationship exists between two entities where the intermediate entity has nothing on it except the connecting roles. For example, it might be that an Employee can be assigned to many Regions, and each Region can have many Employees assigned to it, but there is nothing interesting about an Employee Region (the intermediate entity).
In such cases, you can effectively hide the intermediate entity from your users by doing something like the following, where the three entities involved are A -< B >- C:
1. Rename the role from A to B as "Cs"
2. Rename the role from C to B as "As"
3. Set ExpandInline=True on the role from B to C
4. Set ExpandInline=True on the role from B to A
5. Add the role from B to A to the HiddenFields collection of the role from A to B
6. Add the role from B to C to the HiddenFields collection of the role from C to B
7. Set Hidden=True on entity B
8. Set Hidden=True the # Bs attribute (or just delete it)
Once you've done this, entity B is effectively hidden from the user. The only exception will be in the Formula dialog, where if the user displays the expanded formula for a field related via this path, the individual roles from A to B and B to C will be displayed. Other than that, as far as a Report Builder user can tell entity B does not exist.