How to use lookup() to bring a value from another dataset into a tablix

Question

I have two datasets that include the fields branch and sales. I using dataset1 for the rest of the table, but for one cell I want the sum of sales from dataset2. I want this to be included in the branch row group using the field from dataset1. So I used this function:

=lookup(fields!branch.value, fields!branch.value, sum(sales), "dataset2")

When I try this, I get the following error:

"The Value expression for textrun 'Textbox55.Paragraphs[0].TextRuns[0]' contains an aggregate function in an argument to a Lookup or LookupSet function. Aggregate functions cannot be used for the destination or result Expression parameter of a lookup function."

When I try this:

=lookup(fields!branch.value, fields!branch.value, sales, "dataset2")

I get this error:

"Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case."

What is the correct way to use this function in order to get the sum of sales from dataset2 from the expression?

Answer 1

Hi,

The syntax of lookup function should be like this:

=Lookup(source_expression, destination_expression, result_expression, dataset)

And aggregate functions cannot be used for the destination or result Expression parameter of a lookup function.

In your second try, it should be like this:

=lookup(fields!branch.value, fields!branch.value, fields!sales.value, "dataset2")

If you want to sum the sales, you may try like:

=sum(cdbl(lookup(fields!branch.value, fields!branch.value, fields!sales.value, "dataset2")))

For your reference:

report-builder-functions-lookup-function

ssrs-lookup-function