Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Using views in Report Builder 2.0 - relationships not detected? Expand / Collapse
Author
Message
Posted Friday, March 6, 2009 12:57 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #670596
Posted Thursday, March 12, 2009 5:54 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Monday, July 21, 2014 9:59 AM
Points: 886, Visits: 1,544
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!
Post #674860
Posted Thursday, March 12, 2009 6:45 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #674870
Posted Thursday, May 14, 2009 1:38 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, February 18, 2013 10:53 AM
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!
Post #717340
Posted Thursday, May 14, 2009 3:08 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, July 29, 2014 5:32 PM
Points: 1,045, Visits: 2,722
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, SQL Server MVP
Independent Business Intelligence Consultant
www.TimMitchell.net
@Tim_Mitchell

Post #717434
Posted Thursday, November 10, 2011 8:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, November 10, 2011 8:06 AM
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
Post #1203578
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse