How to exclude one value from a grouping sum, based on a value of another field?

Question

How do I exclude one value from a grouping sum, based on a value of another field?

ie I open Report=> Report Properties=>Code and insert my Custom Code, but how would I change the below code to exclude a numeric value of another field for the below case?

Public Function ChangeWord(ByVal s As String) As String
   Dim strBuilder As New System.Text.StringBuilder(s)
   If s.Contains("Others") Then
      strBuilder.Replace("Others", "Other NOT INCL")
      Return strBuilder.ToString()
      Else : Return s
   End If
End Function

Answer 1

I'm assuming you want to exclude a numeric value from a sum where the string value of a cell on the same row includes "Others", and that the function you've supplied is used as the grouping criteria for a table in the report. Apologies if this isn't correct.

It's not going to be possible to do this without using a second piece of logic, either a function or an Iif condition. I don't have SSRS available to test this at the moment, but (assuming your value column is an integer, the code will look something like:

Public Function ExcludeOthers(rowDesc As String, rowVal as integer)
    if ChangeWord(rowDesc) = "Other NOT INCL"
        Return 0
    else
        Return rowVal
    end if
End Function

Then, in the cell where you want the conditional sum to appear:

=Sum(ExcludeOthers(Fields!desc.Value,Fields!val.Value))

Alternatively, you could do this without the function by using Iif in the cell where the conditional sum will appear:

=Sum(Iif(ChangeWord(Fields!desc.Value) = "Other NOT INCL",0,Fields!desc.Value))

Depending on the nature of your source data, you could also do this by adding calculated columns to the report's source query.

I would favour the second or third option - custom code seems like overkill for this purpose.

Answer 2

I try to run this and I get an error Error 1 The Value expression for the textbox ‘Cost_2’ contains an error: [BC30451] Name 'ChangeWord' is not declared. C:\repos\Pointcast\PointCastNET\Reports\mse_ReportSalesSegmentationResponseRates_v2.rdlc 1

any ideas?