July 15, 2010 at 10:01 am
Greetings,
I am working on a report that is being fed by a query coming from an Oracle DB.
A few of my fields are multi-value fields which are comma delimited(i.e. Fields!PROJ.Value can contain ( NP, BS, 1831) while another value may be ( BS, 1831)) As you can see some time the delimited array can contain from zero to 4 members at any given row. IN SSRS 2008 I have been able to pull the first member by using this expression:
=Split(Fields!PROJ.Value,",")(0).ToString()
But if there are no members in the array then i get an error in that row. And when I pull the second member where one does not exist I get an error. Is there away to this to place a " "space instead of getting an error.
All help is appreciated.
July 20, 2010 at 6:12 am
MyBlueBuddha (7/15/2010)
Greetings,I am working on a report that is being fed by a query coming from an Oracle DB.
A few of my fields are multi-value fields which are comma delimited(i.e. Fields!PROJ.Value can contain ( NP, BS, 1831) while another value may be ( BS, 1831)) As you can see some time the delimited array can contain from zero to 4 members at any given row. IN SSRS 2008 I have been able to pull the first member by using this expression:
=Split(Fields!PROJ.Value,",")(0).ToString()
But if there are no members in the array then i get an error in that row. And when I pull the second member where one does not exist I get an error. Is there away to this to place a " "space instead of getting an error.
All help is appreciated.
The issue arises when you want to convert an empty value into a string. You can split an empty field, that is not an issue.
You can not solve this with an IIF statement though, problem with the IIF statement is that if you have something like this:
=iff(split(fields!proj.value,",").Length>=2,split(fields!proj.value,",")(1).ToString(),"Blank")
Checking if the array has 2 or more values if it does split it and show me index 1(2nd value) otherwise return blank.
Now what IIF does is execute the true and the false statement regardless of the expression statement(proj.value.length=>2...). So it will always try to retrieve index 1 in the array and convert it, which is empty and thus cannot be converted to a string.
The solution for this has to be made on the database level. I suggest filling the empty values with dummy data. Say for example you fill all the empty values with 0,0,0,0(depending on how many values it can hold) and then in the report filter out these fields.
=IIF(fields!proj.value="0,0,0,0", "nothing", Split(fields!proj.value,",")(0).ToString)
It will try to convert the split value into a string which is fine since it holds 4x 0, but it will return "nothing".
This last solution will always work. Just make sure it has to empty values to convert.
Hope this works for your solution
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply