I'm working with SQL Server Report Builder 2.0 for the first time, and so far I'm enjoying it. However, I've run into an issue that's bugging me and I hope someone else has already found and resolved this.
When building a report, the Report Builder will peek into the metadata and detect existing PK/FK relationships for tables to do joins. However, I'm using a couple of views to abstract my data, but those relationships are not detected when I use a view as opposed to the underlying table. When I select the views to use, the following message is generated:
Relationships could not be detected between the selected tables. The query might produce an unexpected result set.
Do you want to edit the query text to relate the tables to the rest of the query?
When I revert back and use the underlying tables rather than the views, and the relationships are detected appropriately.
When using views, I am given the option to edit the SQL query to explicitly do the join for those views, but the purpose of what I'm doing is to try to abstract the underlying data store as much as possible to allow users to build their own reports without having to engage them in editing SQL code. I do understand that views do not directly have PK/FK relationships, so I understand why this is happening, but I'm also hoping that this is something I can address without resorting to using physical tables for abstraction.
Tim Mitchell, Microsoft Data Platform MVP
Data Warehouse and ETL ConsultantTimMitchell.net
| Tyleris.comETL Best Practices