Recently I worked on report model project come with SSRS. My first impression to this is it is so cool. By simply define the data source view and the data model, users can generate professional-view report by dragging and drop the entity’s fields. All the database relationship will be automatic generated for you. However, after looked into it for a while, I noticed that it’s not the as good as I thought. It comes with lots of cons when somebody actually use this, if they didn’t really consider this carefully before they decide use it, it may become a nightmare for them.
Here are some cons I identified, which hopefully can be recongnized by other people too.
1. The data source view and data model need to be very carefully implemented. the relationship between entities has to follow the relationship defined in the database, unique records need to be considered carefully and logical primary key is the key when define data source view. It will leave the displayed data uninconsistence with the database records if the logical primary key is misplaced or partly missing.
2. The generated report query using ad-hoc report is inefficient. It leave a lot of un-necessary relation in the database query. The reason is when the report engine try to build the query for the report, it will retrieve the data source view’s query and add all the relationship defined, and all the records defined in your entities will be selected, it leave the report required fields to be the last one to be selected from these joined queries.