field validation error in ssrs sorting formula

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

field validation error in ssrs sorting formula

Author

Topic

Tasta
Yak Posting Veteran

60 Posts

Posted - 2013-02-13 : 09:07:18

I have a problem with the sorting formula in my tablix. The sorting was always as
cint(Fields!KeyForRows.Value) ,
but now the fields can contain alphanumeric values.So i tried to change as

iif(Parameters!DrillMain.Value ="...",
Fields!KeyForRows.Value,cint(Fields!KeyForRows.Value))
but it doesn't work, as far as I understand, that though in my case sorting is as Fields!KeyForRows.Value, it checks the other part of condition and fails on validation, and I need to preserve sorting as integer.

tm
Posting Yak Master

160 Posts

Posted - 2013-02-13 : 15:32:39

Could you post the fields and some data and what the sort should be.

Could you post the full expression for the sort in tablix?
What error are you getting?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-02-13 : 23:37:13

so you want numeric data alone to come first?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Alan Schofield
Starting Member

23 Posts

Posted - 2013-02-26 : 22:52:10

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources