SSRS Report Models no Primary Keys

When creating report models you might run into bad database designs where the tables have no foreign keys and/or no primary keys. This can be resolved in the report model DSV. I have created two tables, one with first names, the second with last names. Each has an ID column which is the row that matches the row on the opposite table. There are no primary keys and no foreign keys on either table.


In visual studio I created a report model project and selected the database with these two tables. Then I created a DSV with these two tables.


After you create the DSV (Data Source View), right click on the DSV and select open.You will see the two tables in the DSV view.


Then I right clicked on the row to set a primary key. Remember this row must be unique.


Then I dragged the first ID to the second ID to create the foreign key. Remember, these must be matching data types.


Then I create the model and it has the relationships needed to create reports.


I created a report in report builder 2.0. Here you can see how the names match up correctly.


This entry was posted in SSRS and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s