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:
- 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).
- 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.
- Create relationships in the DSV (using the editor again) from Customer to Address that join CustomerID=CustomerID and xxxAddressType=AddressType.
- 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
- Anonymous
January 06, 2007
Hey BobI 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 - Anonymous
January 15, 2007
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. - Anonymous
February 27, 2007
Hi BobI'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). - Anonymous
February 27, 2007
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?) - Anonymous
March 01, 2007
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 linkmain address link id = address link idwhere address links = otherLooks 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 - Anonymous
March 01, 2007
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 linkmain address link id = address link idwhere address links = otherLooks like the only way round this is not to use the expand inline and create effectively a new linking table. - Anonymous
March 01, 2007
Sorry please ignore the above post it worked really well, very impressive! - Anonymous
May 27, 2008
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 - Anonymous
June 05, 2008
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