SQL Reporting services - SPlit Expression?

Posted on 2009-04-28
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


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:


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!



Open in new window

Question by:DEN_Jimbo
    LVL 25

    Accepted Solution

    Try using IIF() function with a check for IsNothing() as follows -

    Open in new window


    Author Comment

    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.


    Open in new window

    LVL 26

    Assisted Solution

    by:Chris Luttrell
    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.
    LVL 26

    Expert Comment

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

    Author Closing Comment

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

    Expert Comment

    great ! thanks for this! save my day!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Maximize Your Threat Intelligence Reporting

    Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

    Suggested Solutions

    Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
    Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
    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.…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    11 Experts available now in Live!

    Get 1:1 Help Now