Show all the entities of report model in query designer in report builder

  • Article

Show all the entities of report model in query designer in report builder.

Summary: This is in an example of using reporting services for creating the report model. While creating a report model we should always familiar with roles.

Problem: In our situation there were a couple of people who need to mix up the fields of different entity to generate a report but when they click on 1 entity then other entity automatically not showing. So here I am describing how to show all the entities in query designer.

Solution:

1. Firstly created one data source and give it to adventure works database**.**

2. Created data source view and adding few tables from adventure works database**.**

http://jeeveshfuloria08.files.wordpress.com/2011/03/22.png

3.  Then select report model, right click, add new item and select report model, give any name of report model.

4. in report model highlight Model and right click new and the entity, give any name of the entity. Then highlight entity and go to properties and click on icon in front of binding, then it is showing Entity Binding dialog box, here select the table or columns. And repeat this for each entity you want to include in your report.

http://jeeveshfuloria08.files.wordpress.com/2011/03/42.png

5. Select entity from left penal and you find all these attribute of that entity in right panel by default or you can create attribute from an entity, highlight the entity then new then source field. You find new attribute in right panel.

http://jeeveshfuloria08.files.wordpress.com/2011/03/52.png

6.you can rename this and highlight the new attribute and go to properties and select binding and then choose the field what you like to select. Repeat this process for the entire attribute whatever you like to use.

http://jeeveshfuloria08.files.wordpress.com/2011/03/62.png

You can also create aggregate field. And if you want to show that field under some filed which is related to that field. Select that field go to properties and select variation of properties then select the attribute you want to place this under that attribute.

http://jeeveshfuloria08.files.wordpress.com/2011/03/6-21.png

Now see this is under Tax amount field.

http://jeeveshfuloria08.files.wordpress.com/2011/03/6-31.png

7. Now the important thing is role. How to select the role for entity. Role is nothing only the relationship between entities. In t-sql we use primary key and foreign key for binding the table. And in report model we do same thing by using the role. So for creating the role

Right click on right panel and then select new then role,  it show all the entity that we are using in report model then select which entity have same key  that match with other entity. Now highlight the role and go to properties then select binding it show only one match column select this

http://jeeveshfuloria08.files.wordpress.com/2011/03/72.png

8. Repeat this process to all the entities. But remember you can create role only then when entity have some matched (identity column) or you can also create logical primary key. so bind all the entities to other entity which have some relationship.

http://jeeveshfuloria08.files.wordpress.com/2011/03/81.png

9. Now important thing is when you highlight the role and go to properties and now select related role. Drilldown this, select binding you find one column there something source, select this then you find if you select the customer entity you will find the role automatically.

http://jeeveshfuloria08.files.wordpress.com/2011/03/91.png

10. Now report model is ready you can deploy this to report server. You find the report model herein figures my data source and data source view name is adventure works DW2008R2.so it shows in report manager.

http://jeeveshfuloria08.files.wordpress.com/2011/03/101.png

11. Open the report builder. Select new data source then browse and select your report model.then open. Now click ok.

http://jeeveshfuloria08.files.wordpress.com/2011/03/111.png

12. Right click on datasets then new, select use a dataset embedded in my report and select data source. Now click query designer. You find all the entities. Select any entities you will find all the attribute of that entity in below side under fields.

http://jeeveshfuloria08.files.wordpress.com/2011/03/121.png

13. Now you can drag and drop the attribute. And when you select customer entity it show 3 entities in entities panel.

http://jeeveshfuloria08.files.wordpress.com/2011/03/131.png

14. Now select fact internet sales it show all the related entities which we bind earlier.

http://jeeveshfuloria08.files.wordpress.com/2011/03/141.png

15. Here you can drag and drop n number of field from all the entities. Click ok or you can also run here.

http://jeeveshfuloria08.files.wordpress.com/2011/03/151.png

16. Here you find the query. It is totally different from the SSRS query. It is based on Semantic Query and xml. Click ok your dataset is ready and now you can create your report.

http://jeeveshfuloria08.files.wordpress.com/2011/03/161.png

This is dummy report I created from the above dataset.

http://jeeveshfuloria08.files.wordpress.com/2011/03/16-21.png