Solved

Border on first and last record

Posted on 2010-08-19
14
637 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 43

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

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 43

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 43

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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…
Hi, I am very much excited today since I'm going to share something very exciting Tool used for Analytical Reporting and that's nothing but MICROSTRATEGY. Actually there are lot of other tools available in the market for Reporting Such as Co…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

808 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