SSRS Custom Code Loop

Question

Can any one of you please explain me how the loop (For, While) loops works in here

A      B
Apple  13
Grape  5
Orange 16

I have written this code, but it's not working here.

Public Function SafeConvert(ByVal num As String) As String

Dim S as Integer
For i as Integer = 0 to 2
  s += num
next
Return s

End Function

Instead of adding each number, SSRS is adding only the last cell 16 * 3 times and showing incorrect result.

And can you please answer this question as well.

I have a matrix whose text box contains 3 values, how do I calculate the sum of all the values for Q27_A_1 if the value is less than 5?

It seems your tablix doesn't have any group so it shows the last row calculation result. Also note you can replace the **FOR** loop in your code by `num * 3`, even you don't need the custom code to get what you are after. Explain better and add sample data and an expected result example. — alejandro zuleta, Jan 10 '17 at 20:56
Let me Explain my problem in more depth. I have a dataset where i need to calculate the sum of all the values like 13, *, 16, so i wants the output as *. Since "*" can't be added into numeric values. Depending upon my parameter selected, the numbers of row value will changes I wants the result to publish 34 instead of (last row Group B vallue = 16*3=54 ) A B apple 5 grapes * (if in some case it is 13 Oranges 16 I wants the output , sum of all these values . is there any other way round, Please help me in this — Avinash Kumar, Jan 10 '17 at 21:05
In SSRS just try `=SUM(CINT(Fields!B.Value))`, assuming B is a string column (varchar, nvarchar, text...). There is no need to use custom code in this one. — alejandro zuleta, Jan 10 '17 at 21:13
We do not accept "fix this for me" questions here, @AvinashKumar - I have removed those requests. Questions of that kind are closed quite quickly here. — halfer, Jan 10 '17 at 21:51

iamdave · Answer 1 · 2017-01-11T11:03:11.760

You do not need to use any custom code to achieve this. If you are confident that all your string values are actually numbers you can use one of the many conversion functions along with a sum in your tablix expression to add everything together.

For whole numbers:

=sum(cint(Fields!B.Value))

For decimal values:

=sum(cdec(Fields!B.Value))

And for double values:

=sum(cdbl(Fields!B.Value))

To use these expressions, you will need to add a group to your tablix based on your dataset and put these expressions in your Group Total textbox.

To sum up all the values that are less than 5, again you need to have a group on your tablix and you can use the sum function. In this instance however, you will need to substitute any values of 5 or over with 0, so they do not add to the total of the sum:

=sum(iif(Fields!Q27_A_1.Value < 5, Fields!Q27_A_1.Value, 0))

score 0 · Accepted Answer · answered Jan 11 '17 at 17:01

Yeah, I understand your point of Sum function to perform this operation. it would be very easy to achieve this by using Sum function. But i have a giant report with more than 5000 matrix boxes and i need to apply on it. So just wants to confirm if the custom codes works in here and whats wrong in this code of lines.

Public Function SafeConvert(ByVal num As String) As String

Dim S as Integer

For i as Integer = 0 to 2

s += num

End Function

What it is doing is just adding the last cell for 3 times. i want to add all the value of cell during runtime.

SSRS Custom Code Loop

2 Answers2