1

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

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

Summay Tablix

How to resolve this error?

Hassnain
  • 154
  • 1
  • 2
  • 17

1 Answers1

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.

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Perfectly working in ideal case, but breaks when Sum in the denominator is 0. How to handle it? I have tried multiple ways, but in each case it is throwing an #Error. – Hassnain May 31 '22 at 14:53
  • You can handle this directly in the expression by checking id the denominator is 0 in both parts of your expression but it's messy to read. A much easier way is to add a simple function to your report. There are lots of exmaples of how to do this (5 minute job), here's one you could look at. Look at the answer with 24 votes, not the accepted answer... https://stackoverflow.com/questions/10432714/divide-by-zero-null-workaround-in-ssrs-2008-report – Alan Schofield May 31 '22 at 17:11
  • Perfect, I have been able to compute my desired results. – Hassnain Jun 01 '22 at 10:32