asked on

SQL Reporting services - SPlit Expression?

Hi I have a field returned in my dataset of type text or string. I wish to sort on these fields and need to split them.

eg. I might have a data set such as

1.2
1.3
1.11
2.3

I need to stay away from stored procedures in that I don't have access to the server, but In reportviewer I could set a field expression like:

=Fields!OutlineNumber.Value.split(".")(1)

This works until there is nolonger a field in the array where I get #Error

Does anyone know an expression I can use on each of these fileds to handel this error and perhaps replace with a "00" so that it sorts correctly.

Your imeediate assitance is appreciated!

Field0=Fields!OutlineNumber.Value.split(".")(0)
Field1=Fields!OutlineNumber.Value.split(".")(1)
Field2=Fields!OutlineNumber.Value.split(".")(2)

Open in new window

ASKER CERTIFIED SOLUTION

reb73

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DEN_Jimbo

ASKER

Well I tried this with the attached snippet. I am getting erros starting at the array value 2. This still results in #Error being displayed in the column.

=IIF(IsNothing(Fields!OutlineNumber.Value),"00",Fields!OutlineNumber.Value.split(".")(2))

Open in new window

SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Chris Luttrell

uh, "math type sturr" = "math type stuff". typing too fast!

DEN_Jimbo

ASKER

Thanks guys! The combo of the 2 worked, putting rebs forumla in the dataset worked!

neasar

great ! thanks for this! save my day!