Creating a role to one of several related items

  • Article

I call this the Primary Address problem, because a classic example is when you have a Customer table and an Address table, and each customer can have many addresses (Primary, Billing, Shipping, etc.), but no more than one of any given type. If you have a FK constraint defined, the report model wizard will automatically detect the 1:* relationship and create an OptionalMany role from Customer -> Address (and an OptionalOne role coming back). However, this still doesn't make it easy to create a report showing Customers and their Primary Address information. What you really want is a separate 1:1 relationship for each type of address, so you can pull in address information without making Address the primary entity of your report.

The best way to do this I know of is like so:

  1. Add calculated fields in the DSV to the Customer table for each type of address, called "xxxAddressType". Each calculation should be a constant (e.g. 'PRI', or whatever the values in your AddressType field are).
  2. Define a unique constraint on the Address table by opening the DSV in code view (XML). Find the primary key constraint for the Address table, insert a copy immediately after it, set msdata:PrimaryKey="false" on the new one and add xs:field elements for the CustomerID and AddressType fields. You have to do this in code view because you can't define non-PK unique constraints in the DSV editor.
  3. Create relationships in the DSV (using the editor again) from Customer to Address that join CustomerID=CustomerID and xxxAddressType=AddressType.
  4. Create roles in the report model from the Customer entity to the Address entity. Bind the roles to the new relationships you just defined in step 3.

[UPDATE] For reference, here's an example of defining a unique constraint (step 2). In the DSV, find the primary key constaint on the same table, which should look something like this:

<

xs:unique name="Address_Constraint1" msdata:ConstraintName="Constraint1" msdata:PrimaryKey=true">
<xs:selector xpath=".//Address" />
<xs:field xpath="AddressID" />
</xs:unique>

Then make a copy, and modify it like this:

<xs:unique name="Address_Constraint2" msdata:ConstraintName="Constraint2" msdata:PrimaryKey="false">
<xs:selector xpath=".//Address" />
<xs:field xpath="CustomerID" />
    <xs:field xpath="AddressType" />
</xs:unique>