How to model a "left join" in SQL Server Reporting Services?

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

Answer 1

I believe the left joins happen based on the order you add fields onto the query designer canvas. As long as you add customer fields, then order fields, then charge fields, you should be right.

You can verify the actual query that was run using SQL Profile which is very handy for debugging these sorts of things.

Answer 2

I got an answer via Sql Server forums:

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/20d4b4fd-dc0b-428e-a5b8-aedf5c53d340

Relevant portion here from Aaron Meyers:

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.