locked
Models, Report Builder 2 and "left join" RRS feed

  • Question

  • Hello,

    I'm new to reporting svcs and I'm writing a report based on a report model (.smdl) created in VS.NET 2008.  I seem to be missing out on the report builder query view's analog to a "left join."  Model is very simple:

    Three entities:
    Cust (custid, custname)
    Ord (ordid, custid, orddate, ...)
    Charge (chargeid, ordid, chargetype, chargevalue...)

    Think of a "charge" as an optional cost (a special charge) associated with an order -- some orders have them, some don't.

    Model was auto-generated from views (.dsv) which accurately indicate the relationships between cust and ord, and between ord and charge. I noted that when designing the view relationships there was no kind of option to indicate (for example) that the relationship should be treated as a "left join".  

    Now I jump to Report Builder 2 (RB2) to design a report based on this model.  My goal is to simply list for each order: customer name, order date, charge type, charge value (i.e., order row would repeat if it had more than one charge type).  I want ALL orders listed, even if an order doesn't have any charges -- what I would consider a "left join" in the traditional sense.

    I use RB2's Query Designer to create the dataset, and merely select (i.e., double-click) the entities I want as fields in the report: custname, orddate, chargetype, chargeval.  I then create a simple table in the report based on this dataset.

    When running the report, I get only those orders which actually have charges.  I was rather hoping to get all orders.  Is there a way I can specify that?

    Thank you,
    Bill Dawson
    Tuesday, February 17, 2009 4:31 PM

Answers

  • A few more details on Raymond's solution:
    The key here is that all report model queries are centered around a "base entity" and each row at the detail level in your report represents a row in this base entity.  You may note that in your model, there is no entity which corresponds to "a charge for an order or just the order if it has no charges".  You need to explicitly create this Order-Charge entity in the model, based on a Named Query in the DSV.  This named query can just select the keys from the Ord table and the Charge table with a LEFT OUTER JOIN between the two.  You then create relationships from these keys to the Ord and Charge tables, and update the model (right-click on Model root node and Autogenerate).
    When you start with details from Ord and navigate through the new role to Charge and select additional details, Report Builder will choose the Order-Charge entity as the root/base entity of the report.
    We are considering functionality for a future release to allow creating these types of queries directly in Report Builder without requiring the model designer to explicitly create the LEFT OUTER JOIN entity.
    • Proposed as answer by Carolyn Chau Tuesday, February 24, 2009 8:35 AM
    • Marked as answer by BillVienna Tuesday, February 24, 2009 10:02 PM
    Saturday, February 21, 2009 8:50 PM

All replies

  • Hi Dawson,

     

    In Report Model Project, the relations of each table have been fixed. It means we could not specify the specific condition JOIN, Report Model will create the Data source Views automatic. I‘d like to suggest you a couple of solutions, I hope my explanation will help you solve this issue.

     

    Solution1 is creating your own view in database like this:

     

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE ID = object_id(N'[dbo].[test_view]') AND OBJECTPROPERTY(ID, N'IsView') = 1)

    DROP VIEW [dbo].[test_view]

    GO

    CREATE VIEW test_view

    AS

    SELECT T1.CusID, T2.OrdID, T3.ChargeID

    FROM Cust T1 JOIN Ord T2 ON T1.CusID=T2.CusID LEFT JION Charge T3 ON T2.OrdID=T3.OrdID

     

    Then select the view test_view in Data source Views Wizard.

     

    Solution2 is creating a new database diagram in SQL Server Management Studio. It will change the relations of the specific table. After that, you will get your own relations of each table.

     

    Please let me know the results.

     

    Regards,

    Raymond

    Friday, February 20, 2009 6:51 AM
  • A few more details on Raymond's solution:
    The key here is that all report model queries are centered around a "base entity" and each row at the detail level in your report represents a row in this base entity.  You may note that in your model, there is no entity which corresponds to "a charge for an order or just the order if it has no charges".  You need to explicitly create this Order-Charge entity in the model, based on a Named Query in the DSV.  This named query can just select the keys from the Ord table and the Charge table with a LEFT OUTER JOIN between the two.  You then create relationships from these keys to the Ord and Charge tables, and update the model (right-click on Model root node and Autogenerate).
    When you start with details from Ord and navigate through the new role to Charge and select additional details, Report Builder will choose the Order-Charge entity as the root/base entity of the report.
    We are considering functionality for a future release to allow creating these types of queries directly in Report Builder without requiring the model designer to explicitly create the LEFT OUTER JOIN entity.
    • Proposed as answer by Carolyn Chau Tuesday, February 24, 2009 8:35 AM
    • Marked as answer by BillVienna Tuesday, February 24, 2009 10:02 PM
    Saturday, February 21, 2009 8:50 PM
  • Hi all,

    I am just looking for some clarification on this issue (I have the same problem) -

    I can see how the solution of putting the entire query into the DSV with left joins will solve the problem, however I very much want to keep the entities separate, i.e. have my three queries in the DSV, 3 entities in the Model, and link them optionally.

    As far as I can tell, this is entirely possible - there are two main things that point towards this - the first being the cardinality, which set itself automatically correctly to:

    T1 - OptionalMany - T2 - OptionalMany - T3, and conversely
    T3 - OptionalOne - T2 - OptionalOne - T1

    Ideal - this is exactly the relational model I want to encompass - when I select from T1 and T2, it should give me all of T1 and T2 where it exists - a left join.

    So step one in building my report, I take a field from T1. The table list in the top left part of the screen then says:

    T1
     |--T2

    Good so far - so I can click on T2, and take a field from there.

    When I do this the tree switches:

    T2
     |--T1

    And the filter changes from "All T1" to "All T2" - effectively making it require T2 to show on the report.

    I have no idea whatsoever why it would switch the order (i.e. switch the base table to T2 from T1).

    The second reason I think that RS supports what I want to achieve the IsLookup property on the entity. I have enabled this on T2 and it cleverly removes it from the list in the top left until I select a field from T1 - correct, as I only want it available as a lookup from T1.

    This makes it even more inexpicable that it switches T2 to the primary table - how can a lookup table be the primary table?

    I can't really see that the report model system fails to do left joins at the model level, and that I should be forced into doing it in the DSV - surely there is a way to do this? Or is RS simply not complete yet?

    Hoping someone can help me with this!

    Thanks,

    Dominic
    Thursday, June 18, 2009 3:43 PM
  • Please note the following from my previous post:
    "all report model queries are centered around a "base entity" and each row at the detail level in your report represents a row in this base entity"

    The tree switches from T1 to T2 because you have chosen a detail field from T2.  The current structure of report model queries does not permit selection of a detail field from T2 if the base entity (root of the tree) is T1 and T1 has a to-many relationship with T2.

    This is a known limitation of report model queries and yes we want to make it easier :)   The only current workaround is to define an extra entity in the model which does the LEFT OUTER JOIN.  Note that this does not mean you need to merge the 2-3 entities into one--you can simply define an additional entity which does the LEFT OUTER JOIN (projects the primary keys from each side) and defines roles to the two joined entities.  This entity can then be used on an as-needed basis in your queries.

    (p.s. I'm the primary dev owner for report modeling components in RS so you can consider this authoritative :)

    Friday, June 19, 2009 10:06 PM
  • The only current workaround is to define an extra entity in the model which does the LEFT OUTER JOIN.  Note that this does not mean you need to merge the 2-3 entities into one--you can simply define an additional entity which does the LEFT OUTER JOIN (projects the primary keys from each side) and defines roles to the two joined entities.  This entity can then be used on an as-needed basis in your queries.

    (p.s. I'm the primary dev owner for report modeling components in RS so you can consider this authoritative :)

    Aaron,  can you expand upon this ?   Do you have a diagram that you can share that visually informs this solution ?

    Also,  do you have any information going forward on when Microsoft will implement a solution for this in Report Model, as in what specific release of SQL Server ?  I read some comments that suggested the fix would be implemented in Report Builder rather than Report Model.  I hope that's not the case.


    Thanks.
    Thursday, March 4, 2010 1:37 PM
  • I don't have a sample/visual but I'll try to give a little more detail briefly. It's pretty straightforward.

    Roughly, you currently have something like the following in the DSV:
    table Customer (CustomerID, ...)
    table Order (OrderID, CustomerID, ...)

    You need to add:
    nested query CustomerOrder
    (SELECT c.CustomerID, o.OrderID
     FROM Customer c LEFT OUTER JOIN Order o ON c.CustomerID = o.CustomerID)

    Then create relationship from CustomerID in this new table to CustomerID in the Customer table, and similar with OrderID to Order table.

    When you update your report model (right-click Report Model root node in model designer and select Autogenerate), you will get a new CustomerOrder entity with roles to Customer and Order. You can then use this entity in reports where you want to see all Customers with Order details but also include Customers that don't have any Orders.
    Thursday, March 11, 2010 10:43 PM
  • Aaron,

    Thanks for the workaround. I was able to get it to work as needed for one fact table and one customer dimension table in an SS 2008 star schema using an SSRS 2008 report model and Report Builder 2.0. I can see all customers with facts but also customers without facts. Great!

    However, I have many more dimension tables relating to the fact table that users would like to be able to return with or without facts. So I need to be able to return all combinations of dimensions with or without facts. For example I would like to return facts for each customer for each date for each product and so on for those combinations that have facts and those that don't. Creating an entity that LEFT JOINs the fact table to the CROSS JOINs between all the dimensions doesn't seem like a feasible solution. How do you suggest applying your workaround for multiple dimension tables? Or what is the typical design of a report model created against a star schema data warehouse that allows many dimensions to be returned with or without facts?

     

    Thanks in advance,

    Kim

    Wednesday, June 2, 2010 2:31 PM
  • You can try creating an entity which starts with the fact table and does RIGHT JOINs to each of the dimension tables.  It should select the facts from the fact table and the keys from the dimension tables (so you end up with a schema that looks the same as the fact table but has extra rows in it).

    I'm not sure how the performance will be but if you have PKs/FKs defined correctly then SQL Server should be able to throw out extraneous joins when possible.

    Friday, June 4, 2010 1:05 AM