Solved

Border on first and last record

Posted on 2010-08-19
14
616 Views
Last Modified: 2013-11-16
How do I conditionally control the cell borders. I added borders to the cells in Details section. Lines and text boxes are not exporting to excel. That is why I am adding borders to the cells.There are somany records in the details sections. I don't want to show lines after every record. I want to show lines on top and bottom of the section. So, I want to have border on top of the first record and the bottom of the last record.  Is it possible?
0
Comment
Question by:Shamanth
  • 9
  • 3
  • 2
14 Comments
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
ID: 33477356
a few things here.  first:  put the lines in their own sections.  you should have one section for each line.  second:  put a formula for suppression on those sections
for the top line, the formula should be:  not onfirstrecord
for the bottom line, the formula should be:  not onlastrecord

make sure the line sections are at the top and bottom of the details.

Top Line section
Record Details
Botton Line section

another way to do this is to create a group on a field that only happens once in your record set.  then create a new section for each line at the group level.  you won't need the suppression formulas if you do it this way.
0
 

Author Comment

by:Shamanth
ID: 33478369
Is there any way I can format the Border Properties (There is a formula in border properties)
When I put 'not onfirstrecord' in the border properties, it returns a syntax error.
0
 

Author Comment

by:Shamanth
ID: 33478600
I am getting line breaks on the left border if I use onfirstrecord. I am also using the lest margin. I am looking for a box kind of formula using borders
0
 

Author Comment

by:Shamanth
ID: 33479209
Borders are also not working" if onlastrecord"   if there are any nulls
0
 

Author Comment

by:Shamanth
ID: 33479626
If  onlastRecord  then crSingleLine else crNoLine
Above is the formula I am using in the bottom border. Bu two fields in the last record are nulls and the borders are not displaying for those two fields. Please help!
0
 
LVL 42

Expert Comment

by:zephyr_hex (Megan)
ID: 33479689
you should be putting these suppression formulas in the SUPPRESS formula in the SECTION EXPERT for the section that the line exists in.  this is why i suggested that you put the line in its own section.

however, it sounds like you came up with a solution in your last comment.

if you have two nulls as your last records, put in an exclusion to ignore null.

if onlastrecord and not isnull({fieldname}) then ....
0
 

Author Comment

by:Shamanth
ID: 33479955
This" if onlastrecord and not isnull({fieldname}) then ....    still gives me blank border."

Where is the option to cotrol borders in section expert?

If I use formulas like 'if onlastRecord..' in section expert it gives me an error message saying that the formula must be boolean.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 42

Accepted Solution

by:
zephyr_hex (Megan) earned 250 total points
ID: 33480533
it's not an option to control borders.

it's an option to SUPPRESS THE SECTION.  in the section, you have a line.  if you suppress the entire section, the line doesn't show.

also, you can't use an IF statement in the suppression formula.  think of the suppression formula this way:
i want to suppress records where....
not onlastrecord or isnull({field})

in other words, it's the logical opposite of your IF statement... because you want to suppress that section for every occurance but the last record or null records.


also, regarding your border formula...  have you verified that your last two records are in fact NULL and not a blank space or "" ?  in order for isnull() to work, the field must be NULL and not just empty or blank.

and... you could always get rid of your null records at the end by creating a Record Selection formula that excludes null records...
0
 
LVL 100

Assisted Solution

by:mlmcc
mlmcc earned 250 total points
ID: 33482284
You can use an IF statement in a suppression formula  it just has to result in a true or false so using the condition of the if is generally easier

Usiing your example

IF not onlastrecord or isnull({field}) then
   TRUE
ELSE
   FALSE

SInce lines do export to Excel, using suppression on them has no effect.

Since the field is NULL, the border is not shown.  SUggest you use the option to CONVERT NULLS TO DEFAULT in the Report Options

mlmcc

0
 

Author Comment

by:Shamanth
ID: 33484711
I set the default report options. But still I am getting nulls. The fields ar Employee Name and Address.

Thanks much to you all for your help!
0
 

Author Comment

by:Shamanth
ID: 33486384
I used isnull in the formula for the fields. But still I see blanks for those fields on the report. That means they are not nulls? How do I handle blank fields?!!
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33490416
What do you mean?

Do blanks not get the border?

mlmcc
0
 

Author Comment

by:Shamanth
ID: 33500760
Yes. Blanks don't get the border. The workaround is using instr(). I found the solution online. I think they are just blank spaces.
Thanks all for your contribution.
0
 

Author Closing Comment

by:Shamanth
ID: 33500785
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…

930 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

12 Experts available now in Live!

Get 1:1 Help Now