I want to create a model file that relates two tables. However, these tables do not have a foreign key relationship in the database. Is there any way to do this? I'm hoping to programmatically generate the XML for the smdl file. Here's the XML that is generated when there is a foreign key:
<Role ID="G958db767-3d65-4445-9ccb-5f76c41720fb">
<Name>Linked Problem</Name>
<RelatedRoleID>Gf5496c9e-13b8-4c66-965d-c3a3c9a78cc8</RelatedRoleID>
<Cardinality>OptionalOne</Cardinality>
<Relation Name="dbo_Incident_FK_Incident_Problem" RelationEnd="Target" />
</Role>
Is there any way to change this to XML reference a table and join fields instead of a FK?
The Role in the report model must be bound to a Relation in the Data Source View (DSV), but the Relation does not need to have any corresponding FK constraint in the underlying database. The solution is to define the Relation you want in the DSV section of the file, then create the Role pair and bind them to it.
|||Hi Bob,
To form the relation, I assume you mean that I make a key/keyref pair in the schema. Is this correct?
As for the role pair, I don't understand exactly where these items go. I looked at the ones generated when there was a FK in the database. It looks like one goes at the end of the first table's attribute (field) list and the second one replaces the attribute in the second table. Is this correct?
If you have any XML samples I would really appreciate it. I tried everything I could think of today and got nothing but errors when I tried to upload the model files.
Thanks!|||Never mind. I've since discovered that you can set up a foreign key with the "NO CHECK" option. I think I can do that and save myself from having to programmatically generate SMDL. Thanks!
No comments:
Post a Comment