Long Label Expression

I have a long expression that returns the value of my one label. The expression is suppose to figure out if there are three parts to the MailAddress field and return the appriopriate number of fields properly formated. The format I need to get returned from this delimited field looks like

PersonIdNumber

CO Line

Name Here

123 Address Street

City State Zip Country

Each line is followed by a semicolon which I have coded to each be replaced by a newline. The main problem is that sometimes we have a CO Line and sometimes we don't. There will be a delimeter right before the AddressStreet Line even if the CO Line does not exist, so that shouldn't be a problem.

Also, the Name Here line is not returned from my delimited MailAddress field, it is a seperate field. For some reason the label returns an error in the report. I'm assuming it is it is in the code below. If you see something wrong with the expression please let me know.

Code Snippet

=IIF(Count(Split(Fields!MailingAddress.Value, ";")) = 3, (LTrim(Replace(Replace(Choose(1, Split(Fields!MailingAddress.Value, ",")),",", " "), ";", " "))& vbcrlf & Fields!Name.Value & vbcrlf & LTrim(Choose(2, Split(Replace(Replace(Fields!MailingAddress.Value, ";", vbcrlf), ",", " "), vbcrlf)))& vbcrlf & LTrim(Choose(3, Split(Replace(Replace(Fields!MailingAddress.Value, ";", vbcrlf), ",", " "), vbcrlf)))), (Fields!Name.Value & vbcrlf & LTrim(Choose(1, Split(Replace(Replace(Fields!MailingAddress.Value, ";", vbcrlf), ",", " "), vbcrlf)))& vbcrlf & LTrim(Choose(2, Split(Replace(Replace(Fields!MailingAddress.Value, ";", vbcrlf), ",", " "), vbcrlf)))))

I'm currently using SSRS 2005...

Hello,

What's the error you're getting when you add the Name field to the expression?

Jarret

|||

The error that is returned is for the entire expression (Name field included). The error just looks like your usual label error :

#Error

|||

Hello,

If you open the Output window (Ctrl+Alt+O), you should see the actual error message you are getting.

Jarret

|||

It is the rsRuntimeErrorinExpression:

The Value expression for the textbox ‘textbox1’ contains an error: Operation is not valid due to the current state of the object.

Preview complete -- 0 errors, 1 warnings

|||

Here is a couple of examples of the MailAddress field coming straight from our database:

With CO Line...

RE: 2423 N. First St.;,9838 Witicki Road;,Sandsburg,Ohio,23911;

Without CO Line..

;,9838 Witicki Road;,Sandsburg,Ohio,23911;

Hope this helps!

|||

Hello,

The problem was with the Count function call, you can't use it on an array. From your data examples, it looks like you will always have 3 semi-colons to seperate your data (whether or not it has data for that section).

Try this:

Code Snippet

=LTrim(Replace(Switch(Len(Split(Fields!MailingAddress.Value, ";")(0)) > 0, Split(Fields!MailingAddress.Value, ";")(0) & vbCrLf), ",", " "))

& Fields!Name.Value

& vbCrLf & LTrim(Replace(Split(Fields!MailingAddress.Value, ";")(1), ",", " "))

& vbCrLf & LTrim(Replace(Split(Fields!MailingAddress.Value, ";")(2), ",", " "))

Hope this helps.

Jarret

|||

Thanks for the quick replies! I tried this and it works with the CO Line, but not without. It chooses the address line to go above the Name field instead of nothing. It looked like this:

9838 Witicki Road

Dan Ricksworth

Sandsburg Ohio 23911

Instead of looking like:

Dan Ricksworth

9838 Witicki Road

Sandsburg Ohio 23911

I have decided to edit our database's MailAddress function that collects and adds the delimeters. I'm going to add the Name field into it and then just use the replace function to add the newlines. Hopefully this will work.

|||

I'm a little confused, I used the example data you supplied and got this as the result:

Mailing Address Name Result RE: 2423 N. First St.;,9838 Witicki Road;,Sandsburg,Ohio,23911; Bob Ricksworth RE: 2423 N. First St.
Bob Ricksworth
9838 Witicki Road
Sandsburg Ohio 23911 ;,9838 Witicki Road;,Sandsburg,Ohio,23911; Dan Ricksworth Dan Ricksworth
9838 Witicki Road
Sandsburg Ohio 23911

Is this not the correct format? If the first section of the MailingAddress has a value, then the name is listed 2nd, otherwise, the name is listed first. In the above table, the first row has the CO Line (marked in red), and the second row does not have a CO Line.

This is the expression I used:

Code Snippet

=LTrim(Replace(Switch(Len(Split(Fields!MailingAddress.Value, ";")(0)) > 0, Split(Fields!MailingAddress.Value, ";")(0) & vbCrLf), ",", " "))
& Fields!Name.Value
& vbCrLf & LTrim(Replace(Split(Fields!MailingAddress.Value, ";")(1), ",", " "))
& vbCrLf & LTrim(Replace(Split(Fields!MailingAddress.Value, ";")(2), ",", " "))

Jarret

|||Thats weird... I copied and pasted your expression directly into my label and it gave me the wrong format. It probably is my fault. I'm assuming that somewhere I missed a delimeter (either comma or semi-colon) and gave you incorrect data from MailingAddress. Your expression looks right. I'll give you the solution. At this point I have already added the Name field to the MailingAddress field and have got the correct format. It would be too time consuming to go back and test to figure out where I went wrong. Thanks for the help though!|||

Yes, that is weird that it showed differently. Oh well, glad to help!

Jarret

long html string

Hi All
I'm trying to display a html report from a store procedure but the string is
to long. I try to cut the sting in parts but no luck.
CREATE PROCEDURE createReport
@.orderNumber int
AS
declare @.lineDesc01 varchar(8000)
declare @.lineDesc02 varchar(8000)
declare @.lineDesc03 varchar(8000)
set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
<td > ''+ cast(b.orderID as varchar) +''</td> bla...'
set @.lineDesc02 = '</td>
<td>''+ isnull(b.myDesc, '') +''</td>
</tr>
</table><br> bla.bla...
set @.lineDesc03 = '</td>
<td>como esta</td>
</tr>
</table></body></html>''
FROM orders b
WHERE b.orderID = @.orderNumber'
exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
How can i fix this problem?
Is other way to do this?
Tks in advance.
JFBWhy are you creating the presentation (HTML) in the stored procedure? You
should always try to minimize the amount of data you pass server <-> client
and you should leave as much of the presenation as you can where it belongs,
at the presentation layer.
But aside from that, why are you using variables at all?
SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
'</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
'</td><td>como esta...up to about 8000'
FROM orders
WHERE orderID = @.orderNumber
Now the presentation tier just has to write out however many columns you end
up with (and I only suggest using columns because you need to concatenate).
There are much, much, much better was to do this, even if you are going to
insist on the bad practice of missing data operations with presentation
details...
"JFB" <help@.jfb.com> wrote in message
news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
> Hi All
> I'm trying to display a html report from a store procedure but the string
> is to long. I try to cut the sting in parts but no luck.
> CREATE PROCEDURE createReport
> @.orderNumber int
> AS
> declare @.lineDesc01 varchar(8000)
> declare @.lineDesc02 varchar(8000)
> declare @.lineDesc03 varchar(8000)
> set @.lineDesc01 = 'select lineDesc = ''<html><head><title>blabla...
> <td > ''+ cast(b.orderID as varchar) +''</td> bla...'
> set @.lineDesc02 = '</td>
> <td>''+ isnull(b.myDesc, '') +''</td>
> </tr>
> </table><br> bla.bla...
> set @.lineDesc03 = '</td>
> <td>como esta</td>
> </tr>
> </table></body></html>''
> FROM orders b
> WHERE b.orderID = @.orderNumber'
> exec( @.lineDesc01 + @.lineDesc02 + @.lineDesc03)
> How can i fix this problem?
> Is other way to do this?
> Tks in advance.
> JFB
>|||How about learning something new and useful?
Keywords: SQLXML, XSL.
If it really needs to be done straight from the server.
ML|||Tks...You right I will try to use my normal way.
Can you tell me about other ways?
Rgds
JFB
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
> Why are you creating the presentation (HTML) in the stored procedure? You
> should always try to minimize the amount of data you pass server <->
> client and you should leave as much of the presenation as you can where it
> belongs, at the presentation layer.
> But aside from that, why are you using variables at all?
> SELECT '<html>... '+RTRIM(OrderID)+'...up to about 8000',
> '</td><td>'+COALESCE(myDesc,'')+'... up to about 8000',
> '</td><td>como esta...up to about 8000'
> FROM orders
> WHERE orderID = @.orderNumber
> Now the presentation tier just has to write out however many columns you
> end up with (and I only suggest using columns because you need to
> concatenate).
> There are much, much, much better was to do this, even if you are going to
> insist on the bad practice of missing data operations with presentation
> details...
>
> "JFB" <help@.jfb.com> wrote in message
> news:uAKXgsfkFHA.2792@.TK2MSFTNGP10.phx.gbl...
>|||JFB,
Generally you would return only data from the database, usually in the form
of a recordset. For example, your stored proc would be like this (contents
only):
SELECT OrderID, [Description]
FROM dbo.Orders
WHERE OrderID = @.OrderId
--
Then say you were presenting the report via an ASP web page, it would look
something like this:
// This code would be in your ASP file
// Assume command has been executed and returned a recordset "rs"
for (; !rs.EOF; rs.MoveNext())
{
Response.Write( "<tr><td>" );
Response.Write( rs.Fields("OrderID").Value );
Response.Write( "</td><td>" );
Response.Write( rs.Fields("Description").Value );
Response.Write( "</td></tr>" );
}
"JFB" wrote:

> Tks...You right I will try to use my normal way.
> Can you tell me about other ways?
> Rgds
> JFB
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:uMABvxfkFHA.320@.TK2MSFTNGP09.phx.gbl...
>
>|||mmm... do you have any exmples? or any web sites to check it out?
Tks for you reply
JFB
"ML" <ML@.discussions.microsoft.com> wrote in message
news:44494029-B841-4A9E-8005-9CDDA3557DDD@.microsoft.com...
> How about learning something new and useful?
> Keywords: SQLXML, XSL.
> If it really needs to be done straight from the server.
>
> ML|||> any web sites to check it out?
www.sqlxml.org