Get Sum of Data from Second Dataset in the Second Tablix of SSRS report with Report Builder

Question

I have two different dataset which has a common value placed in two different Tablix in Report Builder tool. I want to achieve a SUM of ELEC_DATA in the second Tablix.

I have already tried using this expression.

=LOOKUP(Field!COMP_ID.value,Field!COMP_ID.value,Field!ELEC_DATA.value,"DATASET2")

The result shows nothing at all -- no errors as well.

Then I tried with custom code.

=code.sumlookup(LOOKUPSET(Field!COMP_ID.value,Field!COMP_ID.value,Field!ELEC_DATA.value,"DATASET2"))

The Result I am getting is "0".

The expected result would be the addition of ELEC_DATA like shown in the pic attached

Answer 1

Looks to me like you're using the LOOKUP function incorrectly. The LOOKUP function only requires 4 parameters to be used.

=LOOKUP([SourceValue], [DestinationValue], [LookedUpValue], "[SourceDataset]")

However, I'm not sure this is the best or even a viable solution to your issue. Honestly, this would be much easier if you were able to combine these two datasets into a single dataset query.

SELECT PAC2_ID, A.COMP_ID, DATE, ELEC_DATA 
FROM TABLE1 A JOIN TABLE2 B ON B.COMP_ID = A.COMP_ID

From there, you can use a Grouping on the second Tablix based on PAC2_ID which removes the need for any expressions. It should sum the values automatically when you apply the grouping.

Answer 2

Alright Let's try something like this.

In your 2nd Tablix create one more Column let's call it "PAC2ID_Calculated". Now for this Column we will use lookup Expression =Lookup(Field!COMP_ID.value, Field!COMP_ID.value, Field!PAC2_ID, "FirstDataset")

Now what will this return in your new Column, It will return PAC2_ID for COMP_ID from first dataset and your 2nd Tablix will be something like below

Once you get this, Group it based on PAC2_ID, you will get expected Result.

You can Hide COMP_ID column.