SSRS report multiple concatenate

Question

I'm trying to join two data sets in my SSRS table report. The problem is that I can only concatenate two columns in my lookup statement. The third is being ignored. I need to do more than two. how can I achieve this? my values are duplicating in my drill-down report.

thanks for the help.

=Lookup(Fields!MONTH_OF_SERVICE.Value+Fields!REGION.Value+Fields!CLINIC_NAME.Value,Fields!C_MOS.Value+Fields!C_REGION.Value+Fields!CN.Value,Fields!CNT.Value,"DataSet5")

Answer 1

Without much more info on the datasets (data types, sample data from each dataset etc) it's difficult to offer much help.

In general though you can ...

1. Check that the datatypes of the corresponding fields are the same
2. Pad the values in in each column so they are the same width to avoid things like Month= 1 + Region = 1 matching to Month = 11 in the lookup dataset. For example if month is a number, format it to be 2 characters such as 04 for April
3. Add calculated columns to both datasets that will generate your lookup 'key'. The advantage of this is that you can show these temporarily in your report so you can ensure they are unique and the LOOKUP function is simpler as you will only be referencing a single field.