SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Analysis Server and Reporting Services (2008)
 field validation error in ssrs sorting formula
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Tasta
Yak Posting Veteran

Israel
60 Posts

Posted - 02/13/2013 :  09:07:18  Show Profile  Reply with Quote
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 - 02/13/2013 :  15:32:39  Show Profile  Reply with Quote
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?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 02/13/2013 :  23:37:13  Show Profile  Reply with Quote
so you want numeric data alone to come first?

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

Go to Top of Page

Alan Schofield
Starting Member

United Kingdom
23 Posts

Posted - 02/26/2013 :  22:52:10  Show Profile  Reply with Quote
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.

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000