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.

Comments (8)

  1. vaski says:

    This works beautifully even to hide facts which is very user friendly.

    I was able to use this method to hide both factless facts and the ones with measure in it and still expose all the details by exposing user friendly dimensions.

    However in report builder when using the model I can still see the connecting entity B in your example after drilling down through A -> C

    Can I hide B there as well?

  2. Bob Meyers20 says:

    What do you mean "drilling down through A->C"? Do you mean using the automatic drillthrough feature when viewing a report?

  3. vaski says:

    In the report builder when you pick attributes from A, you will see rest of the related entites along with C.

    Now when you drag attributes of C on the report you can then see name of B which is hidden till then.

  4. NedBaker says:

    Is there a way to do this with many to one relationships?  i.e. let’s say that there can be many employees in a region, but only each employee belongs to a single region? I’m able to "hide" the intermediate table when navigating from C (Employee) to A (Region), but when i start with A (Region) I see B.


  5. passx says:


    I was wondering if there is any way of hiding a column in reporting services. How do I toggle visibility of a group of columns based upon visibility

    of a group? Only when a group is expanded, this group of columns

    should be visible.  Is there any way i can do this. Any help will be really great.

  6. PhilNicholas says:

    you need to edit the report in visual studio, report builder isn’t sophisticated enough to hide columns etc.

  7. jcantrell says:

    I have tried this, with success, but after doing it, I discovered a different problem.  I have a DB that includes something similar to the following:

    -> = 1 to Many  

    <- = Many to 1

            Y -> Z -> A -> B <- C

    There are other 1-to-many relationships off of "A" as well.  With this modeled in the DSV, I cannot add fields from "Y" or "Z" to a report that contain fields from "C" and "A".  I can navigate from C all the way to Z or Y, but I can only pull aggregations from those tables (counts of rows, etc..) and not detail data.  Help?    

  8. gurunguns says:

    I have a situation in Report Builder where I am unable to drag and drop the fields of a table into the Report. Below are the detail:

    TableB (PK – B_id)  <– TableA (PK – A_id) –> TableC (PK – C_id)

    TableA (One to Many) TableB

    TableA (One to Many) TableC

    • There is no direct relation b/w TableB & TableC.

    Steps I followed to Create an Ad-hoc report

    1. Now initially my Report’s Builder Left side Entity structure looks something similar to below:




    1. I included one of the field from TableB to my report which changed the structure to look like below tree:




    1. Now I added few fields from TableA and TableB again to my report. Now, though when I select TableC I am able to see all the fields of TableC in the below Fields section but not able to drag any of the field into the report.

    Please help!

    Thanks!! Devashish Dhingra MCP