Business Intelligence Blogs

View blogs by industry experts on topics such as SSAS, SSIS, SSRS, Power BI, Performance Tuning, Azure, Big Data and much more! You can also sign up to post your own business intelligence blog.

SSRS Report Models no Primary Keys

  • 17 February 2014
  • Author: MikeDavis
  • Number of views: 7267
  • 0 Comments

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


Print
Categories: SQL Server
Tags:
Rate this article:
No rating
MikeDavis

MikeDavisMikeDavis

Other posts by MikeDavis

Please login or register to post comments.