?
Solved

Border on first and last record

Posted on 2010-08-19
14
Medium Priority
?
709 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 3
  • 2
14 Comments
 
LVL 44

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 44

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
 
LVL 44

Accepted Solution

by:
zephyr_hex (Megan) earned 1000 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 101

Assisted Solution

by:mlmcc
mlmcc earned 1000 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 101

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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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. …
Jaspersoft Studio is a plugin for Eclipse that lets you create reports from a datasource.  In this article, we'll go over creating a report from a default template and setting up a datasource that connects to your database.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

800 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