• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 797
  • Last Modified:

How to add a line return in my SQL Query that Crystal Reports will recognize

Hi, I'm fairly new to Crystal Reports and cannot figure this out. I've got my report running fine and collecting the data I want from my SQL Server database. The only issue is that I need to force it to do a line return under certain conditions from data returned back from my SQL query. I can't set a 'reflow text' property in the field on Crystal report to solve this b/c I need to specify where the line return is. I've tried using vbCrLf and Chr(10) returned in my sql query, but both just write out that text when exported to Crystal reports. I am using the version that came with my VB.NET 2003.

How can I tell crystal reports from my SQL query results to have a line return when I need it to?

thnks!

0
trevoray
Asked:
trevoray
  • 8
  • 6
  • 2
1 Solution
 
trevorayAuthor Commented:
BTW, the reason i need to do it this way is because i am exporting address fields. if there is no information for say, address line 2, then i need my sql query to tell it to move to the next line. my sql query returns one field that has the address information in it. the company, dept, addr1, addr2, city, state, zip. i can't have these as seperate fields in Crystal reports. I want it all in one field to look more like a mailing label.

In other words, I DON"T want:

First Last
Company: My Company
Dept:
Addr1: 123 Main St
Addr2: Apt 34
City: Anytown
State: CA
Zip: 55555

But I DO want:

First Last
My Company
123 Main St
Apt 34
Anytown, CA 55555


I can do this just fine in my asp pages because I just have the sql query insert a BR tag whenever i want a new line.
0
 
SpykairCommented:
Try Chr(13) and Chr(10) in your query.

An alternative whichi you can use is this:
I assume that you group on Employee(First Last).
Place each og the address detail fields in it's own detail section within the group. Right click on the detail section - > Insert section below.
Rightclick on each section -> Section expert -> Tick "Suppress Blank Section".

Hth,
Spykair
0
 
trevorayAuthor Commented:
I tried the chr(13) and chr(10) and it just returns them as the were part of the text. It reads like:

First Lastchr(13) & chr(10)
123 main st. chr(13) & chr(10)

also, i tried to select supress blank section under the 'format section' for my details section and then i did another export and there are blank liines where there is no data.
0
How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

 
SpykairCommented:
What does your query look like ?

First Last
Company: My Company
Dept:
Are these normal db fields or are they subreports ? If they are subreports you'll have to "Suppress Blank SubReport" as well.

Spykair
0
 
frodomanCommented:
Hey trevoray - don't do it in your SQL query, create a formula in Crystal instead and just display the formula on your report instead of the individual fields:

stringVar EntireAddress;
EntireAddress := {table.FirstName} + ' ' + {table.LastName} + chr(13)
if not isNull({table.Company}) then
   EntireAddress := EntireAddress + {table.Company} + chr(13)
if not isNull({table.Department}) then
   EntireAddress := EntireAddress + {table.Department} + chr(13)
...(same for addr1, addr2, etc.)...
EntireAddress := EntireAddress + {table.City} + ', ' + {table.State} + ' ' + {table.Zip}
EntireAddress;


Don't forget when you drop the formula on your report to change the vertical height so you can see the whole address or check the 'Can Grow' property so this happens dynamically.

frodoman
0
 
trevorayAuthor Commented:
ok, just to test and give me a mininum to make sure it works right, i tried this and i get an error saying,

"the remaining text does not appear to be part of the formula"

This is what I am putting in the formula field. I've never done formulas with CR before, so I just entered in what you told me to..

stringVar EntireAddress;
EntireAddress := {runsql.FirstName} + chr(13)
if not isNull({runsql.LastName}) then
   EntireAddress := EntireAddress + {runsql.LastName} + chr(13)
EntireAddress;

0
 
frodomanCommented:
You're missing some semicolons in that formula:

stringVar EntireAddress;
EntireAddress := {runsql.FirstName} + chr(13);  <--- Added one here
if not isNull({runsql.LastName}) then
   EntireAddress := EntireAddress + {runsql.LastName} + chr(13);   <--- Added one here
EntireAddress;


Basically you need one after every statement (the "if..then..something" is all considered one statement).

frodoman
0
 
frodomanCommented:
By the way, that was my fault - I didn't include the semicolons in my original formula - sorry 'bout that.

frodoman
0
 
trevorayAuthor Commented:
ok, i applied your changes and i do not get an error and the report exports, BUT, now nothing is being produced.

To simplify this for the easiest troubleshooting. I am returning the 'Company Name' instead of LastName (but still calling LastName just so I don't have to change my settings right now/ doing a SELECT companyName as LastName). For testing, I'm using your formula and asking it to display if it's not null, but don't display if it is null. On my export though, every single record is exporting this formula line as a blank field.

This is the latest version of the formula I am using:

stringVar EntireAddress;
if not isNull({runsql.LastName}) then
   EntireAddress = {runsql.LastName} + chr(13);
EntireAddress;


0
 
frodomanCommented:
You need to change the "=" in this line:

    EntireAddress = {runsql.LastName} + chr(13);

To ":="

If you want to validate the proof of concept, use this exact formula - just copy and paste:

stringVar EntireAddress;
if 1=1 then
  EntireAddress := 'Name' + chr(13);
if 2=2 then
  EntireAddress := EntireAddress + 'Addr1' + chr(13);
if 3=999 then
  EntireAddress := EntireAddress + 'This will not display' + chr(13);
if 4=4 then
  EntireAddress := EntireAddress + 'City' + ', ' + 'State' + ' ' + 'Zip';
EntireAddress;

You will see this display:

Name
Addr1
City, State Zip

Don't forget to turn on 'Can Grow' in the field formatting.  Once you're seeing this with the line breaks, just one by one replace the if..then statements with your actual field values.

0
 
trevorayAuthor Commented:
ok, i've applied your changes and now records that have data are displaying. but what if someone has no data for their address? i wanted the fields below to 'move up' when there is no results at all from the formula. is this possible?
0
 
frodomanCommented:
That's where the "if not isNull({field}) then" lines come in.  If the field is not null then it gets added to the final result - if it is null then it does not get added.

If you're doing that and still seeing blank lines, it could be because the field isn't actually null but maybe is just a blank space character instead.  In that case you'll need to change the formula to be more like this:

if not isNull({field}) and trim({field}) <> '' then
  ... etc...


If that doesn't make sense just let me know.
0
 
trevorayAuthor Commented:
ok, so you're saying if the formula field i have in my report returns no data whatsoever, then it should allow the fields below it that i have dragged and dropped onto the designer grid to automatically 'move up' and replace the spot where the formula field is?

tks
0
 
frodomanCommented:
>>> you're saying if the formula field i have in my report returns no data whatsoever, then it should allow the fields below it that i have dragged and dropped onto the designer grid to automatically 'move up' and replace the spot where the formula field is?

No, that isn't the case.  I was referring to lines within the formula - if the address is missing the city/state will move up.  If you want the fields below this one to move up that requires a different tactic.

First you need to place this formula in a section by itself and the other fields in a lower section.  Assuming this is the details area of your report, add another details section and place this formula in DetailsA and the remaining fields in DetailsB.

Edit the first line of this formula and change it to:  stringVar EntireAddress := '';  <-- That's 2 single quotes

This insures the formula always returns something - even if just an empty string.  Now rt-click on the formula and select format.  Click the formula button next to suppress and enter this formula:  (currentfieldvalue = '')   <-- That's 2 single quotes not one double quote.  This will suppress the formula if it's an empty string so it doesn't appear on the report at all.

To get rid of the white space where the formula is suppressed, you have to suppress the entire section.  Under the menu Report -> Section Expert, click on the section with your formula (probably DetailsA) and click the checkbox that says "Suppress Blank Section" - that will hide the entire section if there's nothing in it (which is why your other fields need to be in a lower section).

Hope that helps,

frodoman
0
 
trevorayAuthor Commented:
ok, before i go down this route, not being familiar with having multiple detail sections, i need to make sure it will behave the way i want it. so please verify that this will work correctly.

According to what you are saying, and from what I want to do, I will have something like this.

<---  Detail Section A -->
First Last
Company
Dept
Addr 1
Addr 2
City, St, Zip
<-- Detail Section B -->
Phone: <data>
Fax: <data>
Email:<data>


Basically, the phone, fax, and email fields are hard typed onto the report so that the user can see when those fields are blank. Will this work correctly?

Thanks
0
 
trevorayAuthor Commented:
ok, i'm going to close this and award points to frodoman b/c he basically answered my question. but, i still need help. and desperately. i haven't been able to go out and play this wknd and probably won't be able to b/c i can't figure this out. i am back to square one.

this is my problem: i got the report working from 5 fields that are assigned from my strongly typed dataset. i need to add additional fields, but it won't let me. i've tried everything i have found on the internet for doing this. something must be corrupt or something. my data is being pulled from a dataset that it populated by a call to a web service. so, in light of this. i'd like to be able to have the data returned back from the web service in a way that will allow me to force line returns in the data when it displays on crystal repports. but i don't know if this is possible b/c i've tried that and CR just reports out the BR or CHR(10) and chr(13) just like it was normal text without making a line break.

all that to say i am closing and reposting new problem under two other topics. and hopefully i will get an answer from one of them. if i could figure out how to add more fields to the report, then i'd be able to use frodoman's formula above. or if i could figure out how to have the SQL query return line returns, i could do it that way.  so frodoman, here's your chance to earn another 500! please help.

tks!

trevor

oh, and PLEASE help Trevor have time to go out and play this weekend!  :)
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 8
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now