Sql-server – How to model a “left join” in SQL Server Reporting Services

reporting-servicessql-server

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

Best Solution

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.

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Best Solution

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql – How to return only the Date from a SQL Server DateTime datatype

Related Question