How to merge 2 datasets with 2 different datasources in SSRS 2010

Question

I am basically trying to recreate an access database in SSRS 2010. I have 2 different datasets and they are from 2 different servers (or datasources). I have concatenated several fields to make one keyword between the two in my SQL like this:

to_char(Trim(CONCAT(FLT, CONCAT(ORG, CONCAT(DST, CONCAT(TIL,FLT)))))) AS KeywordField

I have tried the lookup function and I can get data, but it seems like it doesn't just show me the ones that match in both tables but also if there is not a match it puts a blank line in for the fields in the primary dataset.

I basically need them to match themselves up against each other to get the right rows back. Both tables in access are about 7300 records (one a little smaller than the other) and after merging them together I have about 7153 left that matched. When I do this with lookup I always get the full amount back from the primary dataset. For example: If my main dataset is Plan and the other is VRR. I'll use:

=lookup(Fields!KEYWORDFIELD.Value, Fields!KEYWORDFIELDVRR.Value, Fields!MVMNR.Value,"VRR")

and I'll get back all the data that matches, or blank rows that didn't match up but it gets me to the total amount of items in the primary dataset. I know this because I can put a regular field beside my lookup field and it will be data from the primary dataset that didn't match in the lookup to the VRR dataset.

So my question is how can I get this to work? Should I continue with trying the lookup or lookupset? Or should I try subreports? Which I also have no experience with and could not find a good tutorial to show me how to actually link them together to get the correct results out of the subreport that matches the primary dataset. Thanks for any help, I appreciate it.

Answer 1

If I understand correctly, you don't want to show rows where the lookup doesn't return a value. In this case just change the row visibility so that it is hidden if the lookup returns "Nothing".

It looks like you have the lookup function working correctly, so there is no need to change anything there.

Lookupset is only needed if you expect the lookup table to give back multiple rows for each row in the primary table, and subreports are completely the wrong direction.

edit following comments:

Make sure you are setting the row visibility (see Image), not table visibility. Your expression for visibility in the comments looks like it should work, but you don't need the IIF, as IsNothing already returns a boolean.