When we link from one entity to another entity in SSRS 2005 Report Builder, we get an INNER JOIN between the two entities. The Report Builder tool does not natively support OUTER JOIN functionality. However, there is a way to create OUTER JOINs in Report Builder models and overcome this limitation.

We have two tables, CUSTOMER(with a PK of CustomerID) and ORDER(with a PK of OrderID and a FK of CustomerID), with a one-to-many relationship. Each CUSTOMER record could have 0 or more ORDER records. The relationship created between these entities in our data source view will support INNER JOIN links only. Any time we select CUSTOMER records and look to the ORDER entity, our results will only contain CUSTOMER records that have corresponding ORDER records. Our poor Orderless Customers are the victims of foreign key discrimination. In some reporting scenarios, this may serve our purpose. However, if we want to be sure our disenfranchised Customers lacking Orders see the light of day in our report, the default data source view design is not adequate.

We must create a third entity to advocate for the rights of our Orderless Customers. We have a few options as to where to create this entity. We can use a Named Query directly in our Data Source View. Or we can create a view in our database to serve this purpose, which has the added bonus of supporting indexes. Whichever path we choose, our SELECT statement will be the same:

SELECT C.CustomerID, O.OrderID

FROM Customer C

LEFT JOIN Order O ON

O.CustomerID = C.CustomerID

Let’s assume we chose to create a view called vwAdvocate. Once we add vwAdvocate to our data source view, we create two relationships:

1. vwAdvocate.CustomerID to CUSTOMER.CustomerID

2. vwAdvocate.OrderID to ORDER.OrderID

In our Report Model, the CUSTOMER and ORDER entities will each contain a role pointing to vwAdvocate, we will call them CUSTOMER-vwAdvocate and ORDER-vwAdvocate respectively. Likewise, vwAdvocate will have one role pointing to CUSTOMER(vwAdvocate-CUSTOMER) and another role pointing to ORDER (vwAdocate-ORDER). In the Report Builder tool, when we want to use OUTER JOIN functionality between CUSTOMER and ORDER, we utilize the CUSTOMER-vwAdvocate role to get to vwAdvocate. From there, we use the vwAdvocate-ORDER role to get to ORDER. Now we can add ORDER fields to our model without eliminating CUSTOMER records lacking ORDER records.

Below are some things to note about using this method:

1. When we utilize our CUSTOMER-vwAdvocate role to get to vwAdvocate, our report’s primary entity will change from CUSTOMER to vwAdvocate. Thus, we can only add fields from entities that have a relationship with vwAdvocate, namely CUSTOMER and ORDER in our example. So, we are not able to use our OUTER JOIN method to get to more than one lookup table.

2. Now that we have 2 paths (roles) to get from CUSTOMER to ORDER(the direct role as well as the path through the vwAdvocate roles), we have created a bit of a loop. This allows users to click through the roles from CUSTOMER to vwAdvocate to ORDER back to CUSTOMER to vwAdvocate again, etc over and over and over. This can be overcome by educating our users about this in our end-user training/documentation.

3. OUTER JOIN queries require more server resources than INNER JOIN queries. Thus, with the method above, users are now able to have a larger impact on server performance. So, as part of end-user training/documentation, we would want to stress to our users that they should only use this OUTER JOIN functionality when they really need it.

While using this OUTER JOIN functionality in our Report Builder models is not completely without cost, it is still, in my opinion, a worthwhile option to consider when INNER JOINs will not get use the results we desire.