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:
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?