How to get a value of cell based on condition from 2nd group in same tablix in RDLC report

Question

I am working with RDLC report, where I have a very specific customer requirement to print a summary at the end of Report. Please refer to image below for clear understanding:

Report summary is generated based on two Groups: Resource (No), and Task Wage Type. 1st tablix group = No. 2nd tablix group = Task Wage Type.

I need to generate an average per resource where: Average = Total Cost / Quantity (where Task Wage Type = Hourly Task Wage)

So, I am working on extracting the value for Quantity where Task Wage Type = Hourly Task Wage and show it in Total Column for each group.

How can I achieve this?

Currently I have written this expression to achieve results, but its throwing an error as shown in picture above

=Sum(CDec(Fields!Total_Cost.Value), "Resource_No1")/Sum(IIF(UCase(Fields!WT_TaskWageType.Value)="HOURLY TASK WAGE", CDec(Fields!Quantity.Value), 1), "Resource_No1")

Finally my RDLC tablix image is shown below where I have two groups

How to resolve this error?

Answer 1

I can't see too much wrong with your expression so I would try to simplify things and build each part of the expression up until you get an error.

Two points though...

1. You should not need to specify the scope "Resource_No1" as it appears that the textbox is already within the correct scope (it's in he Resource_No1 group's rows).
2. Also, you appear to be adding 1 into your sum amount when the task wage type does not match your criteria. You should use 0 or nothing there instead I guess.

You could just try this to start with and then if that does not work, build up each part of the expression slowly.

=Sum(Fields!Total_Cost.Value)
 /
Sum(IIF(UCase(Fields!WT_TaskWageType.Value)="HOURLY TASK WAGE",
    Fields!Quantity.Value,
    Nothing)
   )

If you still get errors, try returning just the second part of the expression and see what you get.

I just tried this on a similar setup and it worked as expected.