March 6, 2009 at 12:58 pm
Hey all,
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.
Thanks,
Tim
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
March 12, 2009 at 5:54 pm
Tim,
I'd love to help with this myself as I have been looking fro some time now a way to easily generate my own Interactive reports for use in SSMS 2005/2008 just like the Activity Monitor in SSMS 2008 which I assume is some type of report. In any event I can't even get to the point that I can get rdl files created with RB2 (Report Builder 2) to wokr outside of the RB2 IDE.
For example I have created a very basic reprt that pulls from a single view which works fine when run from within RB2's IDE. However if I try to use the rdl file for that within SSMS 2008 I get the below error:
The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' which cannot be upgraded. (Microsoft.ReportViewer.Common)
I thought I had read somewhere (between your piece and the one you referenced at MSSQLTIPS) that reports created with RB2 could be used with 2005 as well as 2008. Are you able to use rdl files from RB2 against a SQL Server 2005 DB?
Thanks
Kindest Regards,
Just say No to Facebook!March 12, 2009 at 6:45 pm
Yes, you can use Report Builder 2.0 against a SQL Server 2005 database. One item of note, I haven't tried publishing from Report Builder 2.0 to SSRS 2005 so I can't attest to that part, but the Report Builder is just a client tool and even connects to SQL Server 2000.
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
May 14, 2009 at 1:38 pm
Hi Tim, Did u ever find a way to uses views and detect the relationships inside the views? I have the same issue and was wondering how to handle this. I am using 2005 db with report builder 2.0..
Please let me know. Thanks!
May 14, 2009 at 3:08 pm
No, I didn't find a way to make this work. As an alternative, I created physical tables rather than views, and populate them as part of my DW ETL. It's a few extra steps and costs more disk space, but it works.
Tim Mitchell
TimMitchell.net | @tmitch.net | Tyleris.com
ETL Best Practices
November 10, 2011 at 8:10 am
Hi,
My question, irrelevant to the topic discussed here, how to find physical rdl file created by Report Builder 2.0 or any other version. Is it somewhere stored in ReportServer DB and generates rdl file on fly?.
I want physical path. I can able to see rdl file generated by report designer. Please help me.
Thanks,
Kris
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply