Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Border on first and last record

Posted on 2010-08-19
14
Medium Priority
?
773 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 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

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…
Hi, In my previous Article  (http://www.experts-exchange.com/Database/Reporting/A_15199-Introduction-to-Microstrategy.html)I discussed some basic understanding of Microstrategy that how we can get in Intro of Microstrategy (MSTR). Now it's tim…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

972 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