如何在SSRS的LookupSet中通过String.Contains实现模糊关联查询

Absolutely, you can achieve this fuzzy matching without modifying your datasets—you just need to adjust the LookupSet expression to check if the Paper field contains the Subject value, instead of relying on exact matches.

Correct Expression Options #

Your previous attempts had syntax issues with how you structured the match condition. Here are two working approaches:

Option 1: Case-Sensitive Match

Use InStr (a VB function to find the position of a substring) to check if Subject exists within Paper:

=LookupSet(1, IIf(InStr(Fields!Paper.Value, Fields!Subject.Value) > 0, 1, 0), Fields!Grade.Value, "B")

Option 2: Case-Insensitive Match

If you want to ignore case (e.g., match "english" with "English"), convert both strings to lowercase first to ensure consistent matching:

=LookupSet(1, IIf(Fields!Paper.Value.ToString().ToLowerInvariant().Contains(Fields!Subject.Value.ToString().ToLowerInvariant()), 1, 0), Fields!Grade.Value, "B")

How This Works #

Let’s break down the logic step by step:

• The first parameter (1) is a dummy value—since we aren’t matching against a specific exact value, we just need a consistent marker to trigger the lookup.
• The second parameter uses IIf to return 1 only when the Paper field contains the Subject value. This tells LookupSet to include all records from Dataset B where this condition is true.
• The third parameter specifies we want to retrieve the Grade values for those matching records.
• The final parameter is the name of your lookup dataset ("B").

Example Validation #

For your specific datasets:

• Paper = "English Literature Autumn 1" will match Subject = "English" → returns A
• Paper = "Further Maths Spring 2" will match Subject = "Maths" → returns D
• Paper = "Physics" will match Subject = "Physics" → returns F

All three matches will now be returned correctly, instead of just the exact Physics match.

内容的提问来源于stack exchange，提问作者dlp_dev