Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Analysis Server and Reporting Services (2005)  Report Model Many-To-many relationships

Author  Topic  Mr Lee Starting Member 1 Post Posted - 2007-04-18 : 10:41:19 I'm wondering as to the capabilities of Report Builder in handling many-to-many relationships resolved within the model using a junction entity. I have two entities Drivers and Vehicles; Drivers can drive multiple vehicles and vehicles can be driven by multiple drivers. In the relational database there is no physical junction table; the tables can be joined in SQL using a common field, basically the ID of the insurance policy they relate to. In the Report Model however I created a logical junction entity using a Named Query. This entity consists of the DriverID and VehicleID columns and the rows are made up of all the possible combinations between them. The two columns are set as a compound primary key, and each inividual column acts as a foreign key to form relationships to the Driver and Vehicle entities. When I did this however I still found that I could'nt have Driver and Vehicle information on the same report (bar aggregates). This is something my users definitely will be looking for and will be a struggle to explain why its not possible (if indeed its not). Thats when I began searching to see if what I was doing was possible or if there was a workaround. I havent found a clear answer however. What confused me even more was an MSN technical article from January 06 that mentions - " If the DSV has a many-to-many relationship implemented with relationship-resolution or junction tables, the report model will construct an entity by selecting identifying columns from both related entities." The Model does indeed contain the Drivers_Vehicles entity but it does'nt appear to be doing anything useful when I get to Report Builder. Also I came across a blog by Bob Meyers of MSN (March 06) describing how to hide the intermediate entity from the end user; this seemed to suggest that the relationship was possible and here was a tidy up to aid clarity for the user. I could be interpreting this wrong however. So I know I can model a many-to-many relationship using a junction entity in the Model builder, but will this relationship work as I mean it to in Report Buiilder i.e. will my users be able to drag fields from Driver and fields from Vehicles to the same report? If it is possible can anyone suggest what I may be doing wrong for it not to be working? If its not is there a work around that would prove efficient and also not add any complexity for my end users? Thanks, Lee.