OK so I've developed quite a few reports using report server projects. Now I'd like to see what is available to the end-user after I, as a developer, am sick to tears of making and customizing reports. So I went to Report Builder.
I open Report Builder and see the list of my five entities. Then when I drag and drop a field from one of the entities to the report designer, ALL of the four other entities DISAPPEAR!
Can you not design a report in Report Builder using fields from multiple entities? The design of our databases have much to be desired, so that would make using Report Builder impossible...
Am I missing something here?
Yes, sure you can, if you previously defined the entities and relationship accordingly on either the database or the dsv laterone you should see the right relationships in the report model view. Did you have a look on the sample project with the Adventureworks database ?
Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||
I must still be missing something (most likely in the setup of the report model).
I'm not sure how to define the entities and relationships properly so that I can drag and drop fields from multiple entities. When I drop one of the fields, all of the other entities (except for the one that I grabbed the field from) disappear.
It seems that I haven't set up the relationships at all...? How do I do this?
Wait a minute, I think I see it.
I have to set the relationships in the view.
|||Ok, I defined the relationships between the tables in the view. I build and run the report model project.
Then when I run report builder and drag and drop a field, all of the other entities still disappear. When I delete the field, they all come back. Why is this?
|||Report Builder operates with Report Model - this is semantic model that describes your database in terms of entities, attributes and relationships between entities. In short, an entity usually represents a table, entity attributes are table columns (or expressions that include columns). Database relationships are represented as roles on entities (db relship is visible as two roles in semantic model). For example you may have two entities: Customers and Orders. Each customer may have multiple orders, each order may have one customer. In your database, when you want to ask question about customers and orders, you would join the two tables thus employing Customer-Order relship. In semantic model you will see two entities instead of tables and each entity will have a role that points to another entity (Customer entity will have role "Customer Orders" and Order entity will have role "Customer").
Now when you open report builder, initially you will see all entities. When you put one entity on the surface you start building your question (or report) in the context of this entity. So at this point the flat list of entities disappears and you see your base entity at the root. You also see all attributes of that entity and all roles going from that entity. Now if you want to reach for some attribute in another entity you will need to navigate thru roles (clicking on them) to the desired entity.
When you build (generate) your semantic model, Model Designer will create entities for tables that have primary keys. Model Designer will also autogenerate roles for relationships based on PK-FK constraints. If your tables do not have primary keys, foreign keys you can always add them in Model Designer prior to generating the model.
