Solved

Border on first and last record

Posted on 2010-08-19
14
605 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
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
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 42

Accepted Solution

by:
zephyr_hex 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

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

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
How to increase the row limit in Jasper Server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

759 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

20 Experts available now in Live!

Get 1:1 Help Now