Error in SSRS - while substract field value between 2 dataset into 3rd dataset - SQL Server 2008

Question

I'm trying to get the dataset3 column value by calculating (substracting) dataset1 column value and dataset2 column value and I'm displaying dataset3 column value .

Dataset1:

    FieldName1 - ID
    FieldName2 - Name

Dataset2:

    FieldName1 - ID
    FieldName2 - Name

Dataset3:

 FieldName1 - Exp [=First(Fields!ID.Value, "Dataset1")-First(Fields!ID.Value, "Dataset2")] 
 FieldName2 - Exp [=First(Fields!Name.Value, "Dataset1")-First(Fields!Name.Value, "Dataset2")]

Error msg:

The expression used for the calculated field '=First(Fields!ID.Value, "Dataset1")-First(Fields!Name.Value, "Dataset2")' includes an aggregate, RowNumber, RunningValue, Previous or lookup function. Aggregate, RowNumber, RunningValue, Previous and lookup functions cannot be used in calculated field expressions.

Example:

Dataset1:

ID  Name
1    A
2    B
3    C

Dataset2:

ID  Name
2    B
4    D

Output:

Dataset3:
ID Name
1   A
3   C

Please help!

Note: I tried with LOOKUP function but no luck.

=Lookup(First(Fields!ID.Value, "Dataset1"),First(Fields!ID.Value, "Dataset2"),First(Fields!Name.Value, "Dataset1"),Dataset1)

are you creating the dataset3 with calculated fields and those expression ? — KrazzyNefarious
well you can't create a dataset which in turn references to another dataset — KrazzyNefarious
@BhupeshC Ok. Instead of create new dataset. Can I display the calculated value into report table. — Dhana

KrazzyNefarious KrazzyNefarious · Accepted Answer · 2014-03-19T12:16:22

Whenever you create a report tablix, you have to assign it a dataset, you can check that by going into tablix properties, general and check the dataset name.

Now when you already have a dataset assigned to your tablix, you can get the values from other dataset by using lookup()

Keeping the scenario in mind here, you can create a report tablix and assign dataset1 to it and then create an expression in that tablix as follows :-

lookup(Fields!ID.Value,Fields!ID.Value,Fields!Name.Value,"Dataset2")

This will lookup in Dataset2 using values of the ID column from Dataset1 and when matched, it will return Name column from Dataset2

More about Lookup()

Error in SSRS - while substract field value between 2 dataset into 3rd dataset - SQL Server 2008

1 Answers