Report Design: Crystal Report Formula Field Problem

Print Page | Close Window

Crystal Report Formula Field Problem

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1301
Printed Date: 02 May 2024 at 1:32am

Topic: Crystal Report Formula Field Problem

Posted By: ranigoal
Subject: Crystal Report Formula Field Problem
Date Posted: 11 Sep 2007 at 12:18pm

I am new to this.

Here is my problem I have columns for city, state, zip code. The problem with that was I could not formatted nicely for example:

Raleigh , NC 566777

Spring Field , OR 5552222

As you can see there are a lot of empty spaces. So I created a Formula field to make it look nice

Example:

Formula1field1: this is the code behind

{command.Mailing City} + ", " + {command.Mailing State/Province} + " " + {command.Mailing Zip/Postal Code};

This worked fine but the problem was if I have an empty zip code which is possible it won’t display the city or state. In some other cases I might have the city and the zip code not the state.

So for simplicity I changed the code behind the formula field to the following:

if ((Not IsNull({command.Mailing City}))and (Not IsNull({command.Mailing State/Province})) and (IsNull({command.Mailing Zip/Postal Code}))) Then

addre := {command.Mailing City} + ", " + {command.Mailing State/Province};

If ((Not IsNull({command.Mailing City}))and (Not IsNull({command.Mailing State/Province})) and (Not IsNull({command.Mailing Zip/Postal Code}))) Then

addre := {command.Mailing City} + ", " + {command.Mailing State/Province} + " " + {command.Mailing Zip/Postal Code};

the problem with this it is always executing the last if Statement

can you help me plz

Replies:

Posted By: jkwrpc
Date Posted: 11 Sep 2007 at 12:56pm

Try this lttle solution....

Create a text box, then place your city, state, and zipcode data fields inside it. That will take of the spacing issues. So it should solve your problems and if it does you wont need to deal with the null issues in a formula.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: ranigoal
Date Posted: 11 Sep 2007 at 1:05pm

Thank you for your replay. I don't think i can do that at this point and the reason is, i am using CrystalReport1.rpt to do this. Adding a text object then adding the columns in the text field it will not do anythig for me.

Thanks again

Posted By: jkwrpc
Date Posted: 11 Sep 2007 at 1:12pm

Sorry it wont work for you.

I do this all the time in my Crystal Report designs to resolve the spacing issues between fields you identified in your original post. I am not clear why this will not work your situation.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: ranigoal
Date Posted: 11 Sep 2007 at 1:21pm

Forgive me I am new to this. Here is what I did I right click on the rtp file then I added a text object then added the three columns and it did not work. I also tried the box object too. Can you guide me through what you suggested?

Thank you

Posted By: ranigoal
Date Posted: 11 Sep 2007 at 1:33pm

how can you creat a text box in Crystal Report

thanks

Posted By: jkwrpc
Date Posted: 12 Sep 2007 at 8:29am

Brian has a very good example in his online book concerning the problem and the solution. It even has pictures. Here is the liink

http://www.crystalreportsbook.com/Crystal_Reports_XI.asp?Page=2_6 - http://www.crystalreportsbook.com/Crystal_Reports_XI.asp?Page=2_6

Regards,

John W.
http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: wattsjr
Date Posted: 12 Sep 2007 at 10:35am

Although the example shown above is quite elegant (I plan to start using it myself), it does assume that you will always have all of the fields populated, or you won't't have any punctuation or labels imbedded in the text string. For example, the "," after the City or "Ext." as part of a phone number. Sometimes, your just stuck with having to use a formula.

So, to answer you original question (using your own names), I think that the following formula should work for you:

If IsNull({Mailing Zip/Postal Code})

Then If IsNull({Mailing State/Province})

Then (RightTrim({Mailing City})

Else (RightTrim({Mailing City}) + ", " + (RightTrim({Mailing State/Province})

Else If IsNull({Mailing State/Province})

Then RightTrim ({Mailing City}) + " " RightTrim ({Mailing Zip/Postal Code})

Else RightTrim ({Mailing City}) + ", " + RightTrim ({Mailing State/Province}) + " " RightTrim({Mailing Zip/Postal Code})

It assumes that you will always have a city.

-------------
-jrw

Posted By: ranigoal
Date Posted: 12 Sep 2007 at 10:36am

http://www.crystalreportsbook.com/forum/member_profile.asp?PF=1753&FID=9 - jkwrpc ,

Thank you sooo much. This is exactly what I have been looking for since yesterday, for some reason I was not getting what I searched for in Google. Also thanks a lot for the resource you provided to me. This book is a great resource.

Posted By: ranigoal
Date Posted: 12 Sep 2007 at 11:07am

http://www.crystalreportsbook.com/forum/member_profile.asp?PF=1823&FID=9 - wattsjr ,

Thank you for your input,

I tried what you suggested and it worked, however, the only trick or downside of this is you can’t take out an empty row for example:

This is good

Mr. Mike Soranto

Director

1411 South St

Raleigh, NC 44444

Now if we don’t have the title we will have the following result

Mr. Mike Soranto

1411 South St

Raleigh, NC 44444

*Note an extra row between the name and the address

Thanks a lot for your input

Posted By: jkwrpc
Date Posted: 12 Sep 2007 at 11:11am

wattsjr

For what its worth... I often use this with databases having an address1 and address2 datafields. Often the address2 field is empty, no values, and it works just fine. It works really well with formula fields and other things where you want formatting abilities as well.

I hope you that as you use it you will find it save lots of time.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: wattsjr
Date Posted: 17 Sep 2007 at 2:52pm

http://www.crystalreportsbook.com/forum/member_profile.asp?PF=1753&FID=9 - jkwrpc

I've been playing with your suggection (concatenating fields in a text string) and I can't get it to suppress blank lines. If one of the fields is null, it just prints a blank line. When you referend to " address1 and address2 datafields" were those fields in the same line, or separate lines?

I'm using .Net 2003 and the CR that came with it at work. Was the blank line suppression implemented in a newer version?

I really like the insertion into a text string feature, and I just need to know when I can use it.

Thanks.

Regards,

-------------
-jrw

Posted By: jkwrpc
Date Posted: 17 Sep 2007 at 5:37pm

wattsjr

Sorry your are having trouble with this. Those were separate lines. The fields might layout like this in a textbox object.

name

address1

address2 (or forumla field containg address field see note below)

city, state zipcode

If address2 is giving you trouble you might try inserting the field in a formula and make certain the 'Default Values for Nulls' is showing from the drop down box. This automatically converts a null in string to an empty string. That should resolve any issues caused by nulls.

Hope this helps, do let me know if continue to have problems.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: wattsjr
Date Posted: 17 Sep 2007 at 10:30pm

jkwrpc

I think that what I may be missing here is that each line is in its own Detail sub-section. with the sub-section set (checked) to suppress when the sub-section is blank. It that what is happening here? Or is there some way that a single text object would suppress blank lines, in the same way that it can grow?

Thanks,

-------------
-jrw

Posted By: jkwrpc
Date Posted: 18 Sep 2007 at 12:36pm

wattsjr:

My concern is we may be talking about different things. So if I miss the point I apologize. All of the data fields in my sample. go into a single textbox object. I generally size it (the textbox) to what I want but left justify the texbox object which takes care of the datafields it contains. I cant recall ever having tried it with enabling the can grow functionality. I use this most often in generating form letters, etc.

For me it has always worked.

Regards,

John W.

http://www.CustomReportWriters.net - www.CustomReportWriters.net

Posted By: wattsjr
Date Posted: 24 Sep 2007 at 10:23am

jkwrpc

Hi John,

I just couldn't let it go, so I kept digging until I found out where we were differing. It’s all me. Even my oldest (CR6 – still in use, and please don’t ask…some jobs are just like that) version of CR has the “Suppress Embedded Field Blank Lines:” option in the Format Editor. I just never saw it – or maybe I just didn’t know what it did. Certainly, I will be making use of it from here on in.

I’m also using CR in VS.NET 2003, CR 10 and now CRXI. It’s a real challenge keeping them straight.

Since I’ve always been employed as a one-man department and am self-taught, this forum has proved to be a real god-send.

Thanks for your help and patience.

-------------
-jrw

Print Page | Close Window