# Using Split in SSRS

**URL:** <https://www.epiusers.help/t/using-split-in-ssrs/71422>\
**Category:** Epicor ERP 10\
**Tags:** reports\
**Created:** [September 30, 2020, 8:43pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422 "2020-09-30T20:43:26Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [September 30, 2020, 8:43pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/1 "2020-09-30T20:43:26Z")

</div>

I have an error in a report when I try to split a field that doesn’t have the index value in the field. the first 22 worked but the 23rd didn’t exist but it may in some cases. I have potentially 1 to 78 indexes and it may vary.

The Field shows #error

Expression I’m using:

=Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value, “JobHead”), Microsoft.VisualBasic.Strings.chr(13)).GetValue(23)

---

<div class="post-metadata">

**Author:** ![Jason\_Woods](https://www.epiusers.help/user_avatar/www.epiusers.help/jason_woods/32/80101_2.png) [@Jason\_Woods](https://www.epiusers.help/u/Jason_Woods)\
**Post date:** [October 1, 2020, 12:58am UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/2 "2020-10-01T00:58:05Z")

</div>

```vb
iif(
First(Fields!Description.Value, "JobHead").Split(chr(13)).Length >= 23, 
First(Fields!Description.Value, "JobHead").Split(chr(13))(23), 
"")

```

However, take a look at STRING\_SPLIT too:

> **[STRING\_SPLIT (Transact-SQL) - SQL Server](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15)**
>
> Transact-SQL reference for the STRING\_SPLIT function. This table-valued function splits a string into substrings based on a character delimiter.

---

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [October 1, 2020, 6:32pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/3 "2020-10-01T18:32:52Z")

</div>

> [@Jason\_Woods](#):
>
> ```auto
> iif(
> First(Fields!Description.Value, "JobHead").Split(chr(13)).Length >= 23, 
> First(Fields!Description.Value, "JobHead").Split(chr(13))(23), 
> "")
> 
> ```

I tried it but it still gives an error if the is not there. It won’t use the false “” parameter. It works for the lower values.

---

<div class="post-metadata">

**Author:** ![Jason\_Woods](https://www.epiusers.help/user_avatar/www.epiusers.help/jason_woods/32/80101_2.png) [@Jason\_Woods](https://www.epiusers.help/u/Jason_Woods)\
**Post date:** [October 1, 2020, 6:40pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/4 "2020-10-01T18:40:13Z")

</div>

Did you try the String\_Split in the SQL?

---

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [October 15, 2020, 3:40pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/5 "2020-10-15T15:40:47Z")

</div>

Not yet.

---

<div class="post-metadata">

**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\
**Post date:** [October 15, 2020, 4:31pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/6 "2020-10-15T16:31:14Z")

</div>

Another option is to use Report Functions. In the Report Properties, there’s a Code section and you can write small VB routines:

```auto
Public Function ConvertToTable(ByVal s as String) as String
    Dim sa() as string
    sa = split(s, "~")
    if (sa.Length < 78) then
      return("")
    end if
    Return sa(77)
End Function

```

_Edit_

Then your expression becomes:

```auto
=Code.ConvertToTable(First(Fields!Description.Value, "JobHead"))

```

---

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [October 21, 2020, 2:10pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/7 "2020-10-21T14:10:55Z")

</div>

Interesting, I haven’t used report functions. So this looks like it would make a table of 78 and even if the text I’m splitting has a variable amount less than 78. Then I can check the value isn’t “” and use it.

---

<div class="post-metadata">

**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\
**Post date:** [October 21, 2020, 2:27pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/8 "2020-10-21T14:27:55Z")

</div>

All standard Epicor reports use Report Functions so check out what they’re doing. There’s no syntax check until you upload it so do a little at a time and build on it.

---

<div class="post-metadata">

**Author:** ![bordway](https://www.epiusers.help/user_avatar/www.epiusers.help/bordway/32/30753_2.png) [@bordway](https://www.epiusers.help/u/bordway)\
**Post date:** [October 21, 2020, 4:21pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/9 "2020-10-21T16:21:11Z")

</div>

> [@pchestnut](#):
>
> split a field that doesn’t have the index value in the field…potentially 1 to 78 indexes

Just curious…  
Can you show us any examples of the field (Description)?

---

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [October 21, 2020, 6:08pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/10 "2020-10-21T18:08:49Z")

</div>

The field data below has separated returns of chr(13) I can split each and put them in specific fields on the report until I hit one missing in the index value then everything I’ve tried will return an error.

A)W/C 7.63 X 9.7 X 30.18 W/SKIN B)W/C 5.13 X 9.7 X B)W/C 5.13 X 9.7 X 30.18 C)W/C 7.63 X 9.7 X 30.18 W/SKIN D)W/C 5.13 X 9.7 X 30.18 E)S/C 1.67 X 12.5 X 31 W/SKIN F)F/C .67 X 12.5 X 25 W/SKIN G)D/C 1.5 X 28.18 X 31 W/SKIN H)W/C .125 X 18.5 X 26 (PLS-.125) I)W/C .125 X 1.5 X 30.18 (PLS-.125) J)F/C .5 X 30.18 X 35.4 W/SKIN K)F/C .5 X 10 X 13 W/SKIN L)F/C 3 X 20.5 X 30 W/SKIN M)F/C .5 X 20.5 X 26.5 W/SKIN N)W/C 3 X 10 X 30 W/SKIN O)W/C 2 X 9.72 X 30.18 W/SKIN P)W/C 4.13 X 9.72 X 30.18 W/SKIN Q)F/C 2X9.72X30.18 W/SKIN U)CASE ASSY R)LAM A-F,J,I&V V)W/C.13X1.5X30.18 W/ S)LAM K - M W)W/C 5.13X9.72X30.18 W/S T)LAM O-Q,H,W&X X)W/C1.88X7.6X26.18W/NNN U)CASE ASSYR)LAM A-F,J,I&V V)W/C.13X1.5X30.18 W/S)LAM K - M W)W/C 5.13X9.72X30.18 W/ST)LAM O-Q,H,W&X X)W/C1.88X7.6X26.18W/NNN

This is the code in a field expression that works. Each field uses a specific number of the index.

=Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value, “JobHead”), Microsoft.VisualBasic.Strings.chr(13)).GetValue(23)

---

<div class="post-metadata">

**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\
**Post date:** [October 21, 2020, 6:23pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/11 "2020-10-21T18:23:35Z")

</div>

Have you tried adding a `Chr(13)` to the end of it?

And you’re sure its a base 1 (meaning `GetValue(1)` returns the first element), and not base 0 (where `GetValue(0)` returns the first, and `GetValue(22)` would return the 23rd) ??

---

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [October 21, 2020, 6:30pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/12 "2020-10-21T18:30:38Z")

</div>

the problem is each record changes it the number of elements. may be one to 78. as soon as the number doesn’t exist there is an error. I’ve even tried a IIF it is over a number to put “” and it still evaluates if it contains the index and errors. I haven’t found a way to ignore the error.

=IIF(  
First(Fields!Description.Value, “JobHead”).Split(chr(13)).Length \< 24,Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value, “JobHead”), Microsoft.VisualBasic.Strings.chr(13)).GetValue(24),  
“”)

---

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [October 21, 2020, 6:36pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/13 "2020-10-21T18:36:31Z")

</div>

You can see the last one is error.

 ![image](https://www.epiusers.help/uploads/default/original/3X/2/8/28345d71ac0bbf08813dfc1e7573d7321bbc3ea6.png)

---

<div class="post-metadata">

**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\
**Post date:** [October 21, 2020, 6:41pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/14 "2020-10-21T18:41:23Z")

</div>

Just looked up the `split` function

> Returns a **zero-based** , one-dimensional array containing a specified number of substrings.

(emphasis mine)

So your expression should be **something like** (not checked for syntax)

```VB
=IIF(First(Fields!Description.Value, "JobHead").Split(chr(13)).Length <= 23,
Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value,"JobHead"),Microsoft.VisualBasic.Strings.chr(13)).GetValue(22),
    "")

```

**Edit,** not sure how you’re get `A)W/C ...` as your first line, _ **unless …** _ there is a CR prior to it.

# EDIT #2

Corrected function code (was `GetValue (24)`, changed to `GetValue(22)` … )

---

<div class="post-metadata">

**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\
**Post date:** [October 21, 2020, 6:41pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/15 "2020-10-21T18:41:44Z")

</div>

The split command returns the length of the array but isn’t the index zero based?

I made the same mistake in my example above…

---

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [October 21, 2020, 6:51pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/16 "2020-10-21T18:51:32Z")

</div>

This is the expression for A and it works.

=Microsoft.VisualBasic.Strings.Split(First(Fields!Description.Value, “JobHead”), Microsoft.VisualBasic.Strings.chr(13)).GetValue(0)

If I try 24 thru 78 that doesn’t exist with this record it errors.

---

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [October 21, 2020, 6:56pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/17 "2020-10-21T18:56:22Z")

</div>

I tried your code and still got the error. Because 24 doesn’t exist even if the IIF is True or false.

---

<div class="post-metadata">

**Author:** ![ckrusen](https://www.epiusers.help/user_avatar/www.epiusers.help/ckrusen/32/50584_2.png) [@ckrusen](https://www.epiusers.help/u/ckrusen)\
**Post date:** [October 21, 2020, 7:02pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/18 "2020-10-21T19:02:59Z")

</div>

Are you sure you’re getting the Split that you think? I’m only guessing at what your data is supposed to be, but the following looks like it failed to split some “records”

 ![image](https://www.epiusers.help/uploads/default/original/3X/b/4/b44723e08a35c0926b72683b2787c27f1bd8287c.png)

---

<div class="post-metadata">

**Author:** ![Mark\_Wonsil](https://www.epiusers.help/user_avatar/www.epiusers.help/mark_wonsil/32/51594_2.png) [@Mark\_Wonsil](https://www.epiusers.help/u/Mark_Wonsil)\
**Post date:** [October 21, 2020, 7:05pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/19 "2020-10-21T19:05:22Z")

</div>

Let try this:

```auto
Public Function GetItem(ByVal s as String, itm as integer) as String
    Dim sa() as string
    sa = split(s, chr(13))
    if (sa.Length < itm) then
      return("")
    end if
    Return sa(itm)
End Function

```

Then your expression would be same on every item but you would vary the index:

=Code.GetItem(First(Fields!Description.Value, “JobHead”),\< zero-based item # \>)

---

<div class="post-metadata">

**Author:** ![pchestnut](https://www.epiusers.help/user_avatar/www.epiusers.help/pchestnut/32/28711_2.png) [@pchestnut](https://www.epiusers.help/u/pchestnut)\
**Post date:** [October 21, 2020, 7:12pm UTC](https://www.epiusers.help/t/using-split-in-ssrs/71422/20 "2020-10-21T19:12:10Z")

</div>

First attempt to run.

 ![image](https://www.epiusers.help/uploads/default/original/3X/6/4/646f887e6c7d3dbfcc3cee801e736ba04c44e341.png)

[Next page](https://www.epiusers.help/t/using-split-in-ssrs/71422.md?page=2)