CODEWITHSUNDEEP
joinreporting-servicesleft-joinssrs-2012outer-join
Andy K
Andy K

Reputation: 5054

ssrs lookup like an inner join but how how about a left join?

I'm familiar with lookup, which for me I see (but maybe wrongly) as the equivalent of an inner join.

I'm wondering what is the equivalent of a left join, a right join and a full outer join.

Thanks.

Upvotes: 0

Views: 4817

Answers (2)

Pete Rennard-Cumming
Pete Rennard-Cumming

Reputation: 1618

The explanation from R. Schreurs is correct, and the lookup functions work most like left joins, but there are workarounds you can use if editing your data sources isn't an option.

The easiest way in most cases to simulate a right join is to reverse the datasets, i.e. use DataSet2 as your table's data set and Lookup values from DataSet1.

To simulate an inner join, you can use a lookup to control which rows of your data are visible. Using an expression in the Row Visibility properties, you can hide rows where an ID doesn't exist in both datasets:

=IIF(IsNothing(Lookup(Fields!ID_1.Value, Fields!ID_2.Value, Fields!ID_2.Value, "DataSet2")), True, False)

For a table that works like a full outer join, I'm not aware of a solution that would work without using a third dataset. If DataSet0 has all possible IDs from DataSet1 and DataSet2, you can use it to lookup values from both the other datasets. You could then use a similar expression to the above to hide rows where an ID isn't found in either DataSet1 or DataSet2.

Upvotes: 3

R. Schreurs
R. Schreurs

Reputation: 9085

To me, Lookup does not look like the equivalent of an inner join, but the equivalent of

select 
    CustomerName = (select 
                        Customer.Name 
                    from 
                        Customers 
                    where 
                        Orders.CustomerId = Customer.Id),
    Orders.OrderDate
from 
    Orders

A join takes two tables and matches rows on both tables based on some on-clause. The type of join prescribes what to do with unmatched rows on either side.

Lookup, and the SQL equivalent above, loops, at least conceptually, through one table and tries to find for each row a matching row in some other table. The result is more like a left join than an inner join, as unmatched rows will remain in the result. There are differences, however, as a Lookup will expect to find a single row on the right side and a join will expand the result set to all matched rows on the right side. So, lookup is normally used to lookup from a many side to a one side of a relation, like from Orders to Customers above. Also, joined tables will participate in aggregations.

There is a LookupSet function, which will lookup multiple values, but, unlike a join, it will not expand the result set, but return an array of values that will be 'nested' in the row.

So, summarizing, I don't think a lookup is an equivalent of an inner join and no equivalents exist in SSRS for any other type of join.

Having said that, you would normally perform a join in the data source of your report, assuming this is an SQL data source.

Upvotes: 3

Related Questions