Wayne Snyder posted on July 01, 2009 10:13
Reporting Services Models garnered a lot of attention, and offer much promise, but the actual implementation falls short of being useful in many situations. The shortcomings I list below cause Reporting Services Models to be practically un-usable for me. Before you use Reporting Services Models, ensure the following problems do not cause you pain.
Purpose
The purpose of this document is to provide the information necessary to understand the issues around using SSRS Report Builder Models.
When you use report parameters which come from a model, no sort clause is issued to the database. Therefore the parameters may or may not be sorted, depending on how the underlying database accesses the data.
Work around: you may create an additional data set directly against the source database and provide your own SQL which can include the sort options.
There are two confusing issues around Model Navigation when you are creating a query against a model.
- The order of items in the entities list can change.
- This change occurs automatically, without your consent, and you cannot change this behavior. Look at the screen shots below in the “Outer Join does not work properly” section. The first entity list has Customer as the top (or primary) entity, followed by Sales Orders. After choosing some items from the Sales Orders entity, the query designer swaps the order of the entities. Sales Orders moves to the top, and Customer is included below. If you wish to add additional items from Customer, simply select it, in its new location and continue. You DO NOT have to cancel and begin your query a second time.
Work around: none
-
The recursive display of entities.
- The picture below shows an example. While there is a potential need for this kind of behavior, there are also bugs around this as well. This direct back and forth between two tables can be turned off by de-selecting the image to the right of the Entities label (highlighted with a blue outline.) This turns off advanced mode.
The example above shows a case where this potentially confusing list does make sense. For each Sales Order show the Customer. For each of those customers show all of that Customer’s Sales Orders. So this shows you all of the Sales Orders for a Customer’s specific Sales Order.
Work around: none
You can change anything except the following in a report model, and existing reports should still run:
Entities: ID and Inheritance properties
Attributes:.ID, DataType, and IsAggregate properties, and entity membership
Roles: ID and Cardinality properties, entity membership, related role and its cardinality and entity membership.
Note that if you change attribute expressions, entity/attribute/role bindings, or the definitions of tables and columns in the DSV (e.g. by modifying a Named Query), you may get different results, but the reports will still run.
Obviously, you can organize and re-organize things in and out of folders as often as you want, and all your existing reports will still load and run just fine.
Also, if you want to "deprecate" a field, you can use the Hidden property to exclude it from the design-time experience in Report Builder. This will not affect existing reports.
Optional relationships are not handled properly in SSRS Report Models. You have a Customer table, and a Sales Order table. Some customers have placed orders, and others have not. There is an optional relationship between Customer and Sales Order.
The example below shows two customers and the number of orders each has placed. This is proper behavior – we wish to see all customers, and their order count.
Now we wish to change the report data set to show all customers, and the Sales Order ID for each order placed by the customer. We still wish to include customer who have not placed orders in our report.
By adding detail fields from the Sales Order table, we can see the outer join problem. We see each of the 4 orders for customer AW00000034, but customer AW00000032 is no longer included in the report.
Work around: none