0

I have a very unique question & i have read countless topics on it, not found a correct answer. Please help me in this.

  1. I have a matrix where i'm calculating the count of all Satisfied, Very safistied, neither satisfied nor unsatisfied or very unsatisfied or unsatisfied. Even if the count value is less than 5 i'm using below code to hide the count of 5 .

    =(Switch(Count(Fields!Q27_A_1.Value)<5, "*", Count(Fields!Q27_A_1.Value)>5 , Count(Fields!Q27_A_1.Value)))

And i need to count the total of this cell into another cell in row and show their value 

But if it contains any "*" values 
then total will be "*" 
else display = Count(Fields!Q27_A_1.Value).

I have writtten these lines of custom code but not working. : Getting Error : Input String was not in correct format 

Public Function SafeConvert(ByVal num As String) As String

Dim s as String
IF IsNumeric(num) <5 
Then
Return "*"
Else :
For i as integer = 0 to 4
s += num
Next 
Return s
End IF

End Function

At the end, I'm calling this function into that cell using

=Code.SafeConvert(ReportItems!Textbox4.Value)
Hiten004
  • 2,425
  • 1
  • 22
  • 34
Avinash Kumar
  • 41
  • 1
  • 11

1 Answers1

0

The issue with your custom code seems superfluous since isn't going to accomplish anything more than the expression you already had. So I would suggest not using any custom code for this.

The main issue here is referencing values in other cells. SSRS has limited support for this built in. You cannot have a grouped expression and then reference it and aggregate it again. But there are ways to work around it to get the desired end result.

One way to do this would be to use subqueries in your SQL to get the additional values you need. Then in the report you would reference the new column to count the filtered values. I would recommend using this approach if possible.

Another option is to add a calculated field to your dataset. You would need to write an expression that filters the data row-by-row. Depending on your exact requirements, this may not be sufficient. Again, you would reference this new column in your table.

I know this isn't a specific answer, but I hope it points you in the right direction.

StevenWhite
  • 5,907
  • 3
  • 21
  • 46
  • Can you help me understanding this error message " contains an error: Input string was not in a correct format.". Error message only comes when its returning from For loop value . Can you confirm me whether the type casting has been done perfectly – Avinash Kumar Jan 10 '17 at 18:59
  • There are several issues with the function. `IsNumeric` returns true or false so it will always be < 5. You looping over `s += num` but you never change "num" so it would just concatenate the same string 5 times. You seem to be trying to iterate through values, but you're just passing in a string, it's not an array. It also isn't handling nulls since "s" is never initiated. But again, I would suggest not trying to fix the function, I don't think it will solve your issues. – StevenWhite Jan 10 '17 at 23:04
  • Thanks stevenwhite For this comments. Can you please update my code . i am new in SSRS and not sure about many stuffs please – Avinash Kumar Jan 11 '17 at 17:11