Creating a role to one of several related items


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>

Comments (9)

  1. gerald_schinagl says:

    Hey Bob

    I am a bit confused with point 2 (the unique constraint). I assume this constarint is needed on the customer table, not on the adress table – or am i mixing something up ?

    Gerald

  2. Bob Meyers20 says:

    No, it is required on the Address table. If it is not present the report model will complain that you are specifying 1:1 roles without having a unique constraint both underlying tables in the DSV. Customer already has a unique constraint on CustomerID, so you only need to add one to the Address table.

    Hope that helps.

  3. PhilNicholas says:

    Hi Bob

    I’ve previously added a query to my view for main address etc. and linked this to the linking table or the address table if the linking table hasnt got anything of interest. In the report model I then do an expandinline on the child table.

    My current problem is to show the most eligible record for someone. E.g. a record that has a maximum weighting and is current based on dates.

    nb Can you add an example of the xml changes for the example above? Also it would be great if many of the items here could be put into the user documentation as this is a bit on the basic side at the moment! Would be nice if it was as clear and detailed as Excel help for example (..as a tool aimed at similar kinds of users).

  4. PhilNicholas says:

    Thinking about it a bit more I think I need to do a combination of the two techniques (as the view will still return multiple records).

    What effect does setting a one to many relationship to one to one in the report model have? Will this return all the related records  while allowing me to add other tables with a one to many relationship (ie relying on the end user to manage this in the filter?)

  5. PhilNicholas says:

    Ah just found a problem with the query method, in my database I have a main address and other addresses. So I have added a query for main address with 1:1 link to my linking table from person to address. I then expand this linking table inline.

    The problem if I do this is if I do a query on my ‘address links’ and filter for ‘other’ address(es) and include my new link to main address, the main address links to the other address creating a circular link.

    ie. creates query like this-

    address links>-links>-person-main address link

    main address link id = address link id

    where address links = other

    Looks like the only way round this is not to use the expand inline and create effectively a new linking table.

    If I link to addresses and filter this by say an other address flag I

  6. PhilNicholas says:

    Ah just found a problem with the query method, in my database I have a main address and other addresses. So I have added a query for main address with 1:1 link to my linking table from person to address. I then expand this linking table inline.

    The problem if I do this is if I do a query on my ‘address links’ and filter for ‘other’ address(es) and include my new link to main address, the main address links to the other address creating a circular link.

    ie. creates query like this-

    address links>-links>-person-main address link

    main address link id = address link id

    where address links = other

    Looks like the only way round this is not to use the expand inline and create effectively a new linking table.

  7. PhilNicholas says:

    Sorry please ignore the above post it worked really well, very impressive!

  8. Dating says:

    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 hav

  9. Weddings says:

    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 hav