Hi Tasta,
The problem is the SSRS always tries to evaluate the False part of your IIF statement, even if the initial condition returns True (annoying isn't it!)
I've seen a technique (can't test it at the moment I'm afraid) where you add the same condition to the False part of your IIF statement but I tend to write a function within the report to do the job.
Just in case you've never used custom code before here's a few basic steps.
1. Right-Click the report area (not the body) it's usually yellow and choose Report Properties
2. Click the Code Tab
3. Write you VB code
4. Click OK
Code might look something like (absolutely not checked at all code!!)
Public Function GetSortValue(pKeyForRows as string) AS Integer ' leave "AS Integer" off is you want to return a variant type
Dim result as Integer
If pKeyForRows = "..." THEN Result = 0 ELSE Result = cint(pKeyForRows)
GetSortValue = result
End Function
5. In your report set you sort expression to =Code.GetSortValue(Fields!KeyForRows.Value)
Obviously this will return all the "..." rows as zeros so you can modify this to return whatever you want or add two sorts, one that sorts by you new function and another that then sorts by the original unconverted Fields!KeyForRows.Value value or something like that.
If I'm off the mark, please supply some examples of KeyForRows values and what the expected sort value would be for each and I'll try to help.
field validation error in ssrs sorting formula
Posted - 02/13/2013 : 09:07:18
