Report Model; problem regarding many-to-many relations

I'm having trouble setting up a report model to create reports with report builder. I guess I'm doing something wrong when configuring the report model, but it might also due to change of primary entity in report builder.

I have 3 tables: Client, Address and Product. The Client has PK ClientNumber. The Address and Product both have a FK relation on ClientNumber. The relation between Client and Address is 1-to-many and also between Client and Product: Product-(many:1)-Client-(1:many)-Address.

I've created a report model (mostly auto generate) with these 3 tables, for each table I've made an Entity. Now on the Client Entity , I've got 2 roles, Address and Product. They both have a cardinality of 'OptionalMany', because Client can have multiple Addresses or Products. On both Address and Product I have a Client Role with cardinality 'One', because for each Address or Product, there has to be a Client (tried OptionalOne as well...).

Now I'm trying to create a report in Report Builder (2.0) where I select fields from these three entities. I'd like an overvi开发者_如何转开发ew of Clients with their main address and their products, but I don't seem to be able to create a report with fields from both Address and Products in it. I start by selecting attributes from Client, and as soon as I add Product for example the Primary entity changes as if I'm selecting Products (instead of Clients).

This is a basic example of a problem I'm facing in a much more complex model. I've tried lots of different things for 2 days, but I can't get it to work. Does anyone have an idea how to cope with this?

(Using SSRS 2008)

Edit: in T-SQL this is what i mean:

SELECT  *
FROM    CLIENT
    INNER JOIN ADDRESS ON CLIENT.CLIENT_NUMBER = ADDRESS.CLIENT_NUMBER AND ADDRESS.TYPE = 1 --Main Address
    INNER JOIN PRODUCT ON CLIENT.CLIENT_NUMBER = PRODUCT.CLIENT_NUMBER
WHERE CLIENT.CLIENT_NUMBER = 1

My experience dates back to Report Builder 1.0, but MS hasn't changed much in their report model since so it probably still applies.

As you mention, you can define many to many relations in the model, but materializing those relations in one tabular report is not possible. Once you chose one of the entities on the many side (Products), you are left without a way to bring data from the other side (Addresses).

You may have some luck with creating a "junction" table (ClientID-AddressID-ProductID) and hidding it from your users like Bob's SQL Reporting Services Blog suggests.

Ultimately this is a big limitation with MS models and it doesn't seem to improve with their latest incarnation Denali BISM many-to-many

I understand the original post is 4 years old and SSRS report models are becoming irrelevant but one solution is to define the OptionalMany relationships as OptionalOne in the model, essentially fooling the model to allow navigation and appropriate query generation.