Need Help on SSRS SPLIT function

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)

Need Help on SSRS SPLIT function

Author

Topic

raj vardhan43
Starting Member

20 Posts

Posted - 2012-10-19 : 18:05:37

Hi All I need your advice,

1) I have a dynamic string which changes each time.

for example today the string will be "ItemA,ItemB,ItemC" and tomorrow it will be "itemA,ItemB" only.

2) I have an SSRS report which has 3 columns

3) I want to split this string "ItemA,ItemB,ItemC" and want to show splitted substrings in these 3 columns of ssrs table. the Delimiter is "," (comma)

4) I had used

=Split("ItemA,ItemB,ItemC",",").GetValue(0) in the first column of the report

=Split("ItemA,ItemB,ItemC",",").GetValue(1) in the second column of the report

=Split("ItemA,ItemB,ItemC",",").GetValue(2) in the third column of the report

5) everything works fine until my string is "ItemA,ItemB,ItemC" ,

BUT WHEN MY STRING CHANGES TO "ItemA,ItemB", then I am seeing "#Error" in the third column.

I understood the error, for

=Split("ItemA,ItemB,ItemC",",").GetValue(2) we dont have any value because the string now has only 2 values after applying split function

Is there any way i can avoid #Error in the third column.

NOTE: I have to compulsorily use 3 columns in the ssrs report.

I had tried using =Replace(Split("ItemA,ItemB",",").GetValue(2),"#Error","NULL") in the third column but that also wont worked.

Appreciate Your Help,

Raj Vardhan

raj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-19 : 23:07:05

where does the string of value come from? can this be done from the source?

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

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-20 : 09:02:31

Add 3 dummy comma's to the end of the first parameter:

=Split("ItemA,ItemB,ItemC"+",,,",",").GetValue(2)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources