CODEWITHSUNDEEP
sqlsql-server-2008reporting-serviceslookup
OmGanesh
OmGanesh

Reputation: 1062

SSRS Lookup a cell value from datasets using parameter condition

I have a dataset("users") generated from query which results in the data as follows which is displayed in a tablix in my report:

name salary
xyz 100
abc 200
pqr 150

Now, i have also a parameter for selecting the username.So, i just want to reuse this dataset to filter and display the salary in another textbox based on selected parameter. e.g. If i select xyz from parameter , then the textbox value should be 100

I tried with this expression in my textbox

=Sum(Lookup(Fields!Name.Value,Parameters!Name.Value,Fields!Salary.Value,"Users"))

But, i get the error message as "The Value expression for the textbox2 uses an aggregate expression without a scope"

Upvotes: 1

Views: 10719

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

I think you are passing arguments to the lookup function in a wrong order. Lookup function receives arguments in the below order:

=Lookup(
  the value to search,
  the field in which you are going to look for a match,
  the field you want to get,
  the scope
)

Your expression should look like this:

=Lookup(Parameters!Name.Value,Fields!Name.Value,Fields!Salary.Value,"Users")

REFERENCE

You can use SUM if the returned value is numeric but it could return a incorrect sum if you don't handle the null values returned when there is no a match.

=SUM(
  Lookup(Parameters!Name.Value,Fields!Name.Value,Fields!Salary.Value,"Users"),
  "Users"
)

Let me know if this helps.

Upvotes: 5

Related Questions