CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008-r2
Greg
Greg

Reputation: 504

SSRS Expression to match values against another data source

Is it possible to have an expression which matches values from one datasource to another in an expression? I know one solution would be do join these together in a query but for the purpose of learning is it possible?

Here is my data: Dataset1

ship_code
---------
aa
bb
cc

Here is my data: Ships

ship_code | ship_name 
----------------------
aa        |  HMS apples
bb        |  HMS bananas
cc        |  HMS cherry

My main table is based around the ship_code from dataset1 but i'd like to convert it to the ship_name. SO rather than displaying the ship_code, we get the ship_name.

Something maybe like: IIf(fields!ship_code.value = First(fields!ship_code.value, "ships") , First(fields!ship_name.value, "ships") ,0).

Thanks

Upvotes: 0

Views: 580

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21703

You can do this using LOOKUP().

Try

=Lookup(Fields!ship_code.Value, Fields!ship_code.Value, Fields!ship_name.Value, "ships")

This reads... Using the ship_code from the current scope (the current scope will be the dataset that your table is bound to), find the value in the "ships" dataset in the fields "ship_code" and return the "ship_name" field.

Note: The correct term is DataSet not DataSource, the datasource is the 'connection' and the dataset is the structured results containing your data.

For more info check this MS doc https://learn.microsoft.com/en-us/sql/reporting-services/report-design/report-builder-functions-lookup-function?view=sql-server-ver15

Upvotes: 3

Related Questions