Created a model that contains 2 entities. I open that model in report builder 1.0. When I drag a field from one of the entities into the design area, i can no longer see the other entity or use any of the fields from that other entity. I need to use fields from both entities in the same report (join tables). In management studio i would simply write a query and join tables but i cannot figure out how to do this in report builder. Once i select a field from one entity how do i select fields from more than one entity and drag them into the same report.

If anyone was having this same problem i found the answer here: http://jeeveshfuloria08.wordpress.com/category/ssrs/

bump

you have to be kidding me.. BrettKMR has a valid question ... and in fact I have been trying to figure this thing out all night.. so, the link that you have included in here indicates all those steps just because we cannot see the entities once ONE is being selected in the builder? that's crazy but then again makes sense why Microsoft came back with the version 2 and 3. well, I guess, we will have to be going out of our ways to make this thing work. Sorry, I am not shotting or intending to shoot the messanger .. it is just weird.

BrettMKR, have you found an answer to this besides the link in below? This is a very interesting one.. anyway, you don't think that the single instance template or multi-instance templates have something to do this one? Do you think the ultimate solution also might be using a linked report to the model? perhaps that would take care of the things.. just spitballing here..

also, you know that the version 3.0 builder is not compatible with the stright sql server 2008, right? It would be nice though, and would have eliminated the issues with the previous version of builder's lack of functionality or differences. well..

In spite of your accurate description of the issue, the proposed solution is not going to work. When creating a new source field, you can only bind this newly created field to the highlighted entity. For instance, in employee table, created a new source field, you can only bind this newly created field with the employee entity and not any other entities on the left pane. Therefore, this is not the solution. Unless there is someting that I am missing and with the lack of rest today perhaps it is permissible.

This link got it working for me. Open up your model in VS and get into the datasource view. You create a relationship here (basically joining tables ON a common field). Then in the design view right click and create a new role. THEN you have to bind both tables in properties. Its an annoying process that is not nearly intuitive but once you know how to do it it only takes a minute. Now i can drag any fields from ALL of my entities into the report in report builder.

let me know if you need more specific help, this works.