Report Writer Calculated Field.

(0) Share

Report

Posted on by Community Member

Hello

Would anyone be able to advice me on how to create a Calculated field on Report Writer to combine two Address lines.

For example I have columns Address1, Address2, Address3 in a table. In this case if I want to show Address1 and Address2 together in one calculated field, how do I write this calculated field

Address1 could be Los Angeles and Address2 could be California. Therefore, final result the calculated field should print is Los Angeles California.

much appreciated

*This post is locked for comments

I have the same question (0)

All responses (5)

Answers (3)

Verified answer

Justin Thorp 2,265 on at

Like (0)

Report

Hi,

When making the calculated field in Report Writer you want to make use of the "CAT" operator.

From the help: CAT

Joins the data in two fields with a string storage type into one string of characters. CAT stands for “concatenate,” and is symbolized by the pound (#) sign when displayed in an expression. You may want to use the CAT operator in conjunction with the STRIP function so that only the data in each field is displayed, and not empty blanks following the data.

Thanks,

Justin

Was this reply helpful? Yes No
Suggested answer

Tim Foster 8,515 on at

Like (0)

Report

See this article:

intouchdynamicsgp.blogspot.ca/.../how-to-avoid-blank-address-lines-in.html

Effectively you are providing all of the address data when you call the rw_selectAddrLine function and then select the line you want to print.

This will give you a formatted address with no blank lines and a lot less effort.

Tim

Was this reply helpful? Yes No
Verified answer

Jeff451QL 155 on at

Like (0)

Report

Below is another link that has an excellent explanation of the rw_SelectAddrLine Address Line function in Report Writer.

http://dynamicsgpblogster.blogspot.com/2008/10/understanding-rwselectaddrline-address.html

Jeff

Was this reply helpful? Yes No
Verified answer

L Vail 65,271 on at

Like (0)

Report

Hi,

If you're just trying to get an address to look good, use the rw_selectAddrLine mentioned several times below. If you are actually needing a separate field that includes the concatenation of Address1 and Address2, follow Justin Thorp's answer. The rw_selectAddrLine is a great function, but it deals with the whole address block and does not yield a single calculated field that will combine the fields you referred to.

If you will be using the CAT function, be sure to also CAT a comma and space if you desire one. The comma and space would be constant values that you would add to your calculated field.

Kind regards,

Leslie

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

I could do with CAT with a STRIP as I found on

support.microsoft.com/.../866460

for the problem I have at hand.

I've got my address lines sorted using rw_selectAddrLine separately but these lines are printed in all Upper-case for some reason.

Thanks everyone for all the replies.

Cheers

Was this reply helpful? Yes No