CODEWITHSUNDEEP
reporting-serviceslookupssrs-2017
Michael Tot Korsgaard
Michael Tot Korsgaard

Reputation: 4014

SSRS lookup missing dataset

I have a dataset which looks like this

Name              Spend
"First Aid"       2
"Healing Arts"    0
"Surgeon"         NULL

I then have three separate textboxes which will be filled with the value of the column which matches the name.

Example: show value of spend in textbox if value of name equals First Aid for this I've made following expression

=Lookup(Fields!skill_name.Value, "First Aid", Fields!skill_spend.Value, "Skills")

My problem is however that I get an error saying that skill_name is missing its dataset, which doesn't make sense to me as it is informed in the end of the expression (skills)

Upvotes: 0

Views: 354

Answers (1)

Steve-o169
Steve-o169

Reputation: 2146

I think you may be misunderstanding the purpose of Lookup and how it is used. The purpose of the Lookup function is akin to a JOIN in SQL in some ways. Basically, you would have two datasets that each have a matching field with the other. In that scenario, the expression would match on the skill_name field and lookup the skill_spend value and the expression would look something like the following.

=Lookup(Fields!skill_name.Value, Fields!skill_name.Value, Fields!skill_spend.Value, "Skills")

As the documentation shows, the first reference to skill_name is the field you are referencing from the current dataset. The second reference is to the dataset from which you are attempting to look up a value. The third expression is the field you are looking up and the dataset should be the one you are attempting to look up a value from, not the current dataset scope.

Lookup(source_expression, destination_expression, result_expression, dataset)

From the best I can tell, you have a single dataset but separate textboxes that need the correct spend value. I think the following expression will work.

= IIF(Fields!skill_name.Value = "First Aid", Fields!skill_spend.Value, Nothing)

This expression should get the skill_spend value associated with the row "First Aid" only and leave the textbox blank otherwise.

Upvotes: 2

Related Questions