Share via

Collapsing a many-to-many relationship in a report model

  • Article

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

  • Anonymous
    April 15, 2006
    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?
  • Anonymous
    April 21, 2006
    What do you mean "drilling down through A->C"? Do you mean using the automatic drillthrough feature when viewing a report?
  • Anonymous
    April 21, 2006
    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.
  • Anonymous
    May 31, 2006
    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.

    thanks!
  • Anonymous
    October 31, 2006
    HiI 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 visibilityof a group? Only when a group is expanded, this group of columnsshould be visible.  Is there any way i can do this. Any help will be really great.
  • Anonymous
    January 04, 2007
    you need to edit the report in visual studio, report builder isn't sophisticated enough to hide columns etc.
  • Anonymous
    October 31, 2007
    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 <- CThere 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?    
  • Anonymous
    May 15, 2009
    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) TableBTableA (One to Many) TableCThere is no direct relation b/w TableB & TableC.Steps I followed to Create an Ad-hoc reportNow initially my Report's Builder Left side Entity structure looks something similar to below:TableBTableATableCI included one of the field from TableB to my report which changed the structure to look like below tree:TableB   TableA       TableCNow 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 MCPddhingra1@humana.com