Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using views in Report Builder 2.0 - relationships not detected?


Using views in Report Builder 2.0 - relationships not detected?

Author
Message
Tim Mitchell
Tim Mitchell
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 2907
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


YSLGuru
YSLGuru
SSC Eights!
SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)SSC Eights! (959 reputation)

Group: General Forum Members
Points: 959 Visits: 1658
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!
Tim Mitchell
Tim Mitchell
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 2907
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


monet
monet
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
Points: 2 Visits: 89
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!
Tim Mitchell
Tim Mitchell
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1112 Visits: 2907
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, Microsoft Data Platform MVP
Data Warehouse and ETL Consultant
TimMitchell.net | @Tim_Mitchell | Tyleris.com
ETL Best Practices


Krishna-400391
Krishna-400391
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search