Solved

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

Posted on 2006-06-08
16
539 Views
Last Modified: 2012-06-27
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
Comment
Question by:trevoray
  • 8
  • 6
  • 2
16 Comments
 

Author Comment

by:trevoray
ID: 16867234
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
 
LVL 10

Expert Comment

by:Spykair
ID: 16867523
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
 

Author Comment

by:trevoray
ID: 16868088
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
 
LVL 10

Expert Comment

by:Spykair
ID: 16868133
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
 
LVL 42

Expert Comment

by:frodoman
ID: 16869530
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
 

Author Comment

by:trevoray
ID: 16871664
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
 
LVL 42

Expert Comment

by:frodoman
ID: 16872170
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
 
LVL 42

Expert Comment

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

frodoman
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:trevoray
ID: 16872725
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
 
LVL 42

Expert Comment

by:frodoman
ID: 16872896
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
 

Author Comment

by:trevoray
ID: 16873747
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
 
LVL 42

Expert Comment

by:frodoman
ID: 16873904
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
 

Author Comment

by:trevoray
ID: 16873967
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
 
LVL 42

Accepted Solution

by:
frodoman earned 500 total points
ID: 16874010
>>> 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
 

Author Comment

by:trevoray
ID: 16874044
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
 

Author Comment

by:trevoray
ID: 16878934
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now