[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

SQL Reporting services - SPlit Expression?

Posted on 2009-04-28
6
Medium Priority
?
6,800 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:DEN_Jimbo
6 Comments
 
LVL 25

Accepted Solution

by:
reb73 earned 1000 total points
ID: 24252537
Try using IIF() function with a check for IsNothing() as follows -
Field0=IIF(IsNothing(Fields!OutlineNumber.Value),"00",Fields!OutlineNumber.Value.split(".")(0))

Open in new window

0
 

Author Comment

by:DEN_Jimbo
ID: 24252636
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

0
 
LVL 27

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 1000 total points
ID: 24252802
You can avoid the stored procedure but intercept the data at the source. Go to your Dataset and add a calculated field.  Right click on the dataset and Add, then name it and chose Calculated field and put your split expression in there.  Then you can reference that field and it will not give an error when no source value was pressent.  Not sure why the IIf logic does not work above, I had tried that first also.  We have add computed fields before, usually for math type sturr, I just had to think about doing it here.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24252806
uh, "math type sturr" = "math type stuff".  typing too fast!
0
 

Author Closing Comment

by:DEN_Jimbo
ID: 31575544
Thanks guys!  The combo of the 2 worked, putting rebs forumla in the dataset worked!
0
 

Expert Comment

by:neasar
ID: 33587071
great ! thanks for this! save my day!
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question