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.

clip_image002

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.

clip_image004

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.

clip_image006

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

clip_image008

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

clip_image010

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

clip_image012

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

clip_image014

Advertisements
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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s