Tutorial :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  


Solution: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.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »