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

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.


Categories: SQL Server
Rate this article:
No rating


Other posts by MikeDavis

Please login or register to post comments.