none
Report Builder - is this a bug or is it by design? RRS feed

  • Question

  • After playing with report builder for some time I have stumbled upon a most annoying restriction in Report Builder: it does not allow including data from entities which are related to each other via a third entity in the following way:
    [table_A] many-to-one [table_B] one-to-many [table_C]. So if I want to show fields from both [table_A] and [table_C] Report Builder does not allow me to do so (unless I use an aggregate function on the fields in [table_C]).

    Here is an example of the actual schema:
    [Claims] many-to-one [Firm] many-to-one [Policy]
    or in other words a firm can have many policies associated with it as
    well as many claims.

    So let us say that a user wants to see a simple list of the claims for
    the firms which have certain types of policies.

    Here is what I expect to be generated by Report Builder when it queries the actual database:

    SELECT 
       C.ClaimNumber, F.FirmName, P.PolicyType
    FROM Claims C
       INNER JOIN Firms F ON F.ID = C.FIRM_ID
       INNER JOIN Policy P ON P.Firm_ID = F.ID

    Having in mind that Report Builder is always aggregating data (why?) this could optionally include a GROUP BY C.ClaimNumber, F.FirmName, P.PolicyType. 

    Here is what happens in Report Builder while I try to accomplish this: I drag the Claims table on the report canvas. Three columns from the table show up on the screen. Now I navigate to the related Firms table via the Claims-Firms relationship, and again choose Firm Name from the Firms table. So far everything is fine. I can also set up a filter - let's say I specify value for PolicyType and also that the policy is from a certain year. Work just fine - looks very promising ... However, if I try to drag a field (let's say PolicyType) from the Policy table I cannot do it - the report designer just ignores the drag-and-drop. The only way to include data from the Policy table is by using an aggregate function e.g. create a new field MIN(Policy Type) and this will work - but this is not what I really need as I may just want to see the records even if some of them are duplicated.

    The promise of Report Builder as I understand it is that business users without special training in databases should be able to easily prepare ad-hoc reports. However, with the current version (CTP JUNE and CTP SEPT) it seems impossible to build a report for a very common database schema ...

    I can understand that a report driven off a similar relationship could produce duplicate records but disallowing such a query altogether seems a bit crippling ...

    In order to reproduce the problem one could use the AdventureWorks database and more specifically the tables [CustomerAddress], [Customer] and [SalesOrderHeader] which are related [CustomerAddress] {n-to-1} Customer {1-to-n} [SalesOrderHeader]. Then it is not possible to drag fields from both the [Customer] and [SalesOrderHeader] tables ...

    I will appreciate any information as to whether this is going to be fixed. On the other hand if this behaviour is by design is there a way to make the restriction optional or work around it?

    Thanks a lot,

    Millen

    Monday, September 26, 2005 1:37 AM

Answers

  • This behavior is by design, and there is actually a simple way to get precisely the result you described, as opposed the rough approximation returned by the SQL statement you suggested.

    Here's how to get "a simple list of the claims for the firms which have certain types of policies":

    1. Drag on ClaimNumber and Firm->FirmName
    2. Open the Filter dialog
    3. Drag Firm->Policies->PolicyType onto the filter area
      - this creates an aggregate filter group called "Any Policies with:"
    4. Specify one or more values for policy type
    5. Click OK to close filter dialog
    6. Run report

    This report will have exactly one row per claim, as opposed to one row per claim/PolicyType combination.

    P.S. I'm guessing that your willingness to accept duplicate rows was based on domain knowledge that the Firm->Policy relationship isn't really one-to-many, but more like "one-to-usually-one".

     

    Thursday, December 22, 2005 12:06 AM
    Moderator

All replies

  • I have had this very same problem and I have not found a way around it, I have had to produce many reports using the report designer in BI Dev Studio because of this problem which is not really what I want to do.

    I believe the problem stems from having more than one row returned from the third level of entities, if report builder hinks that this could be the case it stops you from adding any field which is not an aggregate.

    Sorry I couldn't be of more help
    Monday, September 26, 2005 12:23 PM
  • Well, it seems that this behaviour we will have to live with, at least for now ...

    I will have to explain to the users that this restriction is actually a feature to prevent them from messing up their reports ... although I don't think this explanation will sound very convincing (especially for users that have used MS Access for example).

    Still, I wish MS could add a "List" report template which will not aggreagate records (or add some way of turning off grouping) ... In this case the Report Builder will be much more useful for ad hoc reporting and there won't be any concern about producing inflating totals or multiplicated results.  

    What we really need is the equivalent of the MS Access "Totals" toggle button to turn on and off grouping.
    Tuesday, September 27, 2005 6:42 AM
  • When you initially add your first field to a Report Builder report, you sort of "step into" that entity and view related entities from its perspective. This provides at least 2 benefits: it prevents cross joins, and when multiple tables join to the same reference table, there is no ambiguity involved because you "step into" the table that you want before adding a field from the reference table. This initial field selection determines the "top" entity, and as you navigate through the model, that primary entity normally doesn't change.

    However, I noticed that when you navigate through a role with one-to-many cardinality, Report Builder will "rearrange" the order of the entities. For example, I would add a field from the primary key table and then add a field from the foreign key table. Report Builder would automatically move the 2nd entity to the top of the Entities window as if I had added one of its fields first. This does not occur in a one-to-one relationship.

    My role was actually a one-to-OptionalMany because I wanted a left outer join on the primary key table. This "rearrangement" was creating an inner join. In my troubleshooting, I changed it to a one-to-OptionalOne, and I did not find any negative side effects. The cardinality does not truly reflect the relationship in the data, but it does provide correct results. (If someone can tell me why I should not do this, please email me.)

    I changed all of my "many" cardinalities to "one", and it appears to be working. Also, I bound 2 roles to the same relationship (from the data source view) creating 2 paths to the same entity: one with inner join cardinality and one with outer join cardinality. I added an (Inner) or (Outer) suffix to designate which was which. While this might be confusing for end users, join requirements are dependent upon the reporting needs which can be different for different reports. Only do it where necessary, explain it to your users, and you should be good to go.

    To make a long story short, the "many" side of the cardinality was causing me problems. I changed everything to one-to-one and one-to-OptionalOne, and everything works. Hope this helps.

    I have not had any training in Reporting Services or metadata modeling, and I am young and pretty new to BI. As such, I wouldn't mind some feedback on this...

    Tuesday, December 20, 2005 8:47 PM
  • Joshua
    I've few comments/questions on this one.
    1) you said:  I bound 2 roles to the same relationship (from the data source view) creating 2 paths to the same entity: one with inner join cardinality and one with outer join cardinality.

        why we need to create 2 roles instead can we use Count attribute that gets generated for each entity when we generate model. the users will need to add filter say  "count attribute >0" for inner joins
    and if they want to see only parent records that no matching records in child table then that'll be "
    count attribute =0". if they want everything they just dont need any filter (its Left outer join) , right ?
    by default when i join 2 entities its almost always Left outer join (on parent) so my abv suggestion should work.
    did i make any sense ?

    2) you said :

    The cardinality does not truly reflect the relationship in the data, but it does provide correct results. (If someone can tell me why I should not do this, please email me.)

    I changed all of my "many" cardinalities to "one", and it appears to be working

    I have similar situation .i've 2 tables ,patient and transactions (1 - many relation). since i am having same problem as original poster so i followed ur suggestion .i.e. 1 - many to 1 - 1 but when when i run report even though some patients have more than one transaction the report builder displaying only 1 transaction !!

    am just wondering how you managed to get correct results in your case.

    please let me know if i misunderstood ur post.

    Thanks

    Wednesday, December 21, 2005 2:11 AM
  • I also tried bell.joshua's suggestion and changed the cardinality of a One-To-Many relationship to One-to-One optional. Unfortunately, the results returned by the report included only one record (per record in the main table) even when the table on "many" side of the relationship actually contained more than one record.  I also captured the generated SQL statements in Profiler and the statement was not restricting the join in any way (it was generating a LEFT OUTER JOIN), however it appears the the report's engine is stripping out the additional records (effectively enforcing the One-to-One relationship).


    So, the hack didn't work for me.
    Wednesday, December 21, 2005 6:59 AM
  • Prk, thanks for the comments...

    1) Question regarding using multiple roles:
    It is true that you could use outer joins for all of your roles and then use filters to emulate an inner join. This is not the most efficient approach, however, and could cause performance issues. I am working with tables that have hundreds of millions of records, and I do not want to return data just so that I can then filter it out.

    Other modeling tools assign cardinality when the relationship is created, and I see value in being able to create multiple roles with different cardinalities that use the same relationship. I am sure that some people's needs will be different than mine, and your filter approach will work. I was just very pleased to see that the flexibility was there. Make sense?

    2) I reexamined my results. I have not examined the SQL being passed to SQL Server, but it appears as though Report Builder is including some sort of DISTINCT option in the query (either in the query passed to the server or as it is rendering the report). Say I have the following tables:

    Customers
    CustomerNo
    -----------------------
    1
    2


    Orders
    CustomerNo               OrderNo
    ------------------------------------------
    1                                 1234
    1                                 1235
    2                                 1236

    If I create a report with Customers.CustomerNo and Orders.CustomerNo, I will only get 2 records back:

    CustomerNo               CustomerNo[2]
    ----------------------------------------------
    1                                 1
    2                                 2

    I have seen this effect before with Cognos ReportNet's "auto-group and summarize" feature, but that option could be turned off in ReportNet. I have not figured out how to turn it off in Reporting Services.

    If I include all of the record identifiers for each table in the report, I get all of the records:

    CustomerNo               CustomerNo[2]         OrderNo
    -----------------------------------------------------------------
    1                                 1                               1234
    1                                 1                               1235
    2                                 2                               1236

    I have also found that including an aggregation in the report will show that multiple transactions can be returned with the one-to-one cardinality:

    CustomerNo               CustomerNo[2]         OrderCount
    ------------------------------------------------------------------
    1                                 1                               2
    2                                 2                               1

    So, the results are not being limited by the one-to-one cardinality. I can still get a one-to-many resultset. It just looks like Report Builder wants to summarize it for you by using some sort of DISTINCT effect. It helps that double-clicking an entity name automatically adds all of the identifying attributes to the report. Can anyone else replicate this scenario, and does anyone know how to turn off the auto-summarization?

    -Josh

    Wednesday, December 21, 2005 3:55 PM
  • Also, FYI... (this is kinda weird)

    Take a look at your grouping in the report as well. I just noticed that when I double-click my "Customers" entity and then double-click my "Orders" entity, it puts them under the same group tab. This returns only the first OrderNo. If I manually add the same fields, it places them under different group tabs which returns both OrderNo's.

    I am not claiming to have all the answers, but I am hoping that the continued discussion will eventually lead to some clarity.

    -Josh

    Wednesday, December 21, 2005 4:11 PM
  • Millen
    am just wondering have u tried using "sql views" ? i believe you are using tables directly in your model designer ,right ?
    what you can try is when joining two tables (in the sql view) make sure that its Left outer join instead of Inner join .
    meantime i'll also try this one.
     
    Wednesday, December 21, 2005 6:43 PM
  •  bell.joshua wrote:
    Also, FYI... (this is kinda weird)

    but I am hoping that the continued discussion will eventually lead to some clarity.

    -Josh

    i agree with u Joshua. your posts definetly helped me understand these concepts with more clarity and helped me solve a issue am having in report builder!!
    Thank you very much .
    Wednesday, December 21, 2005 6:46 PM
  • prk, not sure which "sql views" you meant... Were you referring to a view in the SQL Server database or a named query in the *.dsv in Model Designer?

    I'll throw this out there just in case it helps anyone: all of my entities are "named queries". I assigned my business names with aliases in the SQL to maximize reuse. Also, the reference tables were really cluttering the model, so I included all code descriptions next to their respective codes in the named queries. One or two reference tables were too wide to make this practical, but it helped in the rest of the cases: fewer relationships, fewer entities, and a more user friendly model.

    Using views in SQL Server will obviously accomplish the same result and further maximize reusability, but you might not want to clutter your database. Just depends on what you're going for.

    -Josh

    Wednesday, December 21, 2005 7:12 PM
  • Yes,Joshua
    am referring to views in sql server database.

    Using views in SQL Server will obviously accomplish the same result and further maximize reusability, but you might not want to clutter your database. Just depends on what you're going for.


    in my case i should be fine as our front end app also need some of the functionality/data i expose thru these views.
    Wednesday, December 21, 2005 8:15 PM
  • This behavior is by design, and there is actually a simple way to get precisely the result you described, as opposed the rough approximation returned by the SQL statement you suggested.

    Here's how to get "a simple list of the claims for the firms which have certain types of policies":

    1. Drag on ClaimNumber and Firm->FirmName
    2. Open the Filter dialog
    3. Drag Firm->Policies->PolicyType onto the filter area
      - this creates an aggregate filter group called "Any Policies with:"
    4. Specify one or more values for policy type
    5. Click OK to close filter dialog
    6. Run report

    This report will have exactly one row per claim, as opposed to one row per claim/PolicyType combination.

    P.S. I'm guessing that your willingness to accept duplicate rows was based on domain knowledge that the Firm->Policy relationship isn't really one-to-many, but more like "one-to-usually-one".

     

    Thursday, December 22, 2005 12:06 AM
    Moderator
  • I do not recommend creating roles whose cardinality or optionality differs from the underlying data.

    Report Builder uses this information heavily to provide an ad-hoc design environment that both discourages inappropriate queries by novice users and enables many sophisticated and powerful features for power users. When role cardinality is fundamentally misleading, the UI will also be misleading in many ways, some obvious, some not. Frankly we have not even contemplated what RB would do if you specified all roles as one-to-one regardless of the underlying cardinality.

    Thursday, December 22, 2005 12:31 AM
    Moderator
  • Thanks a lot for the proposed solution.

    I realize that the suggested query can be achieved with the help of a filter, however, my real concern was that this design decision prevents the users from showing data (e.g. the Policy Type) from the Policy table (or any table in a similar schema). In this way the filter description becomes the only means to display data from the third entity (Policy).

    In my opinion this behaviour is confusing for "business users" - they may try to drag fields from the Policy table and will be confused when nothing happens i.e. some of the fields can be added to the report and some can't. At least there should be some indication that the fields from this entity are not available (to be used as columns for the report).

    I guess that there is always a trade off between protecting the users from creating meaningless queries and allowing them to fully explore their data. At any rate I think it is necessary to articulate more clearly that the system is imposing a restriction rather than leaving the impression of a bug.
    Thursday, December 22, 2005 1:44 AM
  • Thank you for your helpful feedback. I appreciate your thoughts on this. We will work on making this experience more natural and understandable in the next release.
    Saturday, December 24, 2005 6:17 PM
    Moderator
  • Hi,

     

    I am facing the same situation with one to many relations in SSRS report model/report builder as discussed over here. Does anyone has any idea if this has been resolved in 2008 release?

    Monday, July 28, 2008 6:09 PM
  • yes I am also facing the same issue, and its hard to explain to business for this limitation..
    Thanks!! Devashish Dhingra MCP
    Sunday, May 17, 2009 5:48 PM