Alpha Video Training
Results 1 to 30 of 30

Thread: how to remove entire line if numeric field is 0.00 in a report

  1. #1
    Member
    Real Name
    Warren J. Wexelman, MD.
    Join Date
    Dec 2010
    Posts
    5

    Default how to remove entire line if numeric field is 0.00 in a report

    I am writing a report that lists 6 possible line items--not all will be used. The field fee1 will always be filled, but fee 2 to 6 may not be. When the fee is not used, it defaults to 0.00

    When this happens, how can I delete the entire line from the report?

    Fee 1 $100.00

    Fee 2 50.00

    Fee 3 0.00

    Fee 4 0.00

    How can I make Fee3 and Fee4 not show on the report? I have them in the detail section of the report

  2. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    I read this is as two different questions.

    How do you keep the whole line from printing if one of the fields (fees) is zero?
    How do you keep the field 3 and field 4 from showing zero in the report?

    Perhaps, instead of showing these fields horzontally as they would appear in a browse, you have stacked them vertically?

    If so you need an rtf object such as one would use for address lines. The rtf would display something like

    field1
    if(field2>0,field2,"")
    if(field3>0,field3,"")
    if(field4>0,field4,"")
    if(field5>0,field5,"")
    if(field6>0,field6,"")

    with soft returns after each line and have the rtf set to shrink.

  3. #3
    Member
    Real Name
    Warren J. Wexelman, MD.
    Join Date
    Dec 2010
    Posts
    5

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Thanks but your expression only holds if the fields are characters, our fields are numeric and cant return a blank value

  4. #4
    Member Bob Whitaker's Avatar
    Real Name
    Bob Whitaker
    Join Date
    Jun 2000
    Location
    Sutton-Coldfield, UK
    Posts
    695

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Set the format to be "blank if zero" and also set allow shrinking to yes.

    Bob
    Bob Whitaker


  5. #5
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by DR Wexelman View Post
    Thanks but your expression only holds if the fields are characters, our fields are numeric and cant return a blank value
    Perhaps there is some reason you don't want to use an RTF object, but if there is no good reason, then what Stan said is perfectly correct. It does not matter if the field is numeric. Thus if F1 etc. are numeric just create calculated fields in your report:
    c_1 = If(F1=0,"",str(F1,5,2))
    c_2 = If(F2=0,"",str(F2,5,2))
    c_3 = If(F3=0,"",str(F3,5,2))
    c_4 = If(F4=0,"",str(F4,5,2))

    Then put them (just my c_1 and so on, not the expressions) in an RTF object on the report with soft returns (Shift+Enter) after each calculated field (c_1, etc.). That way if the numeric value =0, the line won't print at all. Then as Stan says make sure to set the RTF object's properties to "Allow shrink when printing" (growth too, probably). Other data items can be above and immediately below the RTF object and you won't be able to tell in the report.

    Try it.

    Ray Lyons

    Edit: For these purposes a blank numeric field does = 0, assuming you are using DBF tables. You might also try <1 if zero is a problem.

  6. #6
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Set the field format in the report to "Blank if Zero" and check the box to allow the region to shrink.

  7. #7
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Could be wrong but what Bob and G suggest would work only if the numeric field was the only field on that line in the report. However, Warren seems to also have a label ("Fee 1:") in front of the numeric value. I think the only way to do that is what Stan suggested with an RTF box in the report. That way the "Fee" labels can be in the calculated fields, for example:
    c_1 = If(F1=0,"","Fee1: "+str(F1,7,2))
    c_2 = If(F2=0,"","Fee2: "+str(F2,7,2))
    c_3 = If(F3=0,"","Fee3: "+str(F3,7,2))
    c_4 = If(F4=0,"","Fee4: "+str(F4,7,2))

    Again, put these (just the C_1, C_2, C_3, C_4 with soft returns after each) in an RTF box set to shrink and I think Warren would be able to get what he wants. Again, a soft return is Shift+Enter.

    Ray Lyons

  8. #8
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Not the only way.
    If you have a lablel, put a calc field instead of the lable:
    Label_Text=if(field_value=0,"","Your_label")
    You can certainly use an RTF, or even a sub-report, or a conditional object, or ..

  9. #9
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by G Gabriel View Post
    Not the only way.
    If you have a lablel, put a calc field instead of the lable:
    Label_Text=if(field_value=0,"","Your_label")
    You can certainly use an RTF, or even a sub-report, or a conditional object, or ..
    Yes, but such a calc field (or like the ones in my examples) can't be set to be "Blank if Zero" in the detail region for the line to be skipped unless it is in an RTF box, can it? (I'd like to be wrong, and as you surely know, I frequently am.)

    As for conditional objects, yes, but from my experience in reports that can be a more difficult way to go as far as getting things to look and work just right, etc. Maybe it's just me though--I am pretty much retired from all this kind of stuff now and probably should not be trying to help anymore.

    Ray Lyons

  10. #10
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    No.
    As I said: if you have a label, let's use Warren's example, "Fee": instead of using a label with the "Fee" text on it, replace that with a calc field:
    calc_label=if(fee=0,"","Fee")
    If the field value in question is zero, it will be blank and so will the label.
    If you check to shrink the region, that entire line will not show in the report.
    Nothing against RTF, except it might be a bit more involved to line up the labels and the fields.

  11. #11
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by G Gabriel View Post
    No.
    As I said: if you have a label, let's use Warren's example, "Fee": instead of using a label with the "Fee" text on it, replace that with a calc field:
    calc_label=if(fee=0,"","Fee")
    If the field value in question is zero, it will be blank and so will the label.
    If you check to shrink the region, that entire line will not show in the report.
    Nothing against RTF, except it might be a bit more involved to line up the labels and the fields.
    I think you must be talking about a calc filed in a table whereas I was talking about a calc "field" in the report layout. The latter, one could argue, is not really a "field" but Alpha in various places calls them that, so I do too. I have not tested it but I'll assume that as a table calc field your suggestion will work. As to which is the better way to go, I suppose among other things it depends on whether one wants the calcs in a table or in the report layout. I can see reasons to go either way.

    Ray Lyons

    Later edit: I must still be missing something because in my testing what I thought you were suggesting (table calc field) still does not work, I guess because your table field has to be a character field. What am I missing?
    Last edited by Raymond Lyons; 02-15-2012 at 08:56 PM.

  12. #12
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    I am talking about a calc "field" in a report.
    Try it.

  13. #13
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by G Gabriel View Post
    I am talking about a calc "field" in a report.
    Try it.
    Got it. The problem was you stated above:

    Post 6: "Set the field format in the report to "Blank if Zero" and check the box to allow the region to shrink."
    ....
    Post 10: "If you check to shrink the region, that entire line will not show in the report."

    However, for a calc field there is no "Blank if Zero" format property as there is for a table numerical field. And the region I thought we were talking about shrinking was the detail region of the report, but that has no effect in my testing.

    Since it does work, I guess what you were trying to say was to set Allow Shrinking in the calc field properties, not the region, as setting the region to shrink is has no effect on removing the line in the report. Anyway, when I set Allow Shrinking in the calc fields, the line is removed when the numeric field in the character calc field returns 0. I did not know that would work. Good to know.

    Thanks.

    Ray Lyons

  14. #14
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Reluctant to add anything more to this, but some might be interested to know that the method talked about here allows real bulleted lines in reports--using more or less real looking bullet characters from the various Winding fonts/characters. Maybe others know some other way to do this, but years ago I tried and failed, so to me this thread has proved valuable for that reason alone. Using the method in this thread, one just has to create and put a calc field (set to a Winding font and character) in front of a related calc field and you have a good looking bulleted line. Of course if a list has lines that need to be skipped, follow what is stated above in this thread. Both the bullet and the related blank field will be skipped with no gap between lines.

    Raymond Lyons

  15. #15
    Member Bob Whitaker's Avatar
    Real Name
    Bob Whitaker
    Join Date
    Jun 2000
    Location
    Sutton-Coldfield, UK
    Posts
    695

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Just add if you put your calculated field in a rich text box that CAN be set to shrink.

    Bob
    Bob Whitaker


  16. #16
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: how to remove remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by Raymond Lyons View Post
    And the region I thought we were talking about shrinking was the detail region of the report, but that has no effect in my testing.
    It should work either way.
    That said, most of the time the label is used as a column heading in the report and rarely used as a repeating object in the detail area.

  17. #17
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    As to the bullets, a while back I attached a zip file showing how to do that.

  18. #18
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by G Gabriel View Post
    As to the bullets, a while back I attached a zip file showing how to do that.
    It's one thing to have a series of bulleted lines--that was never my issue from years ago. I just did not know how to have those lines skipped when the condition for the line was not met, as lines can be when soft returns are used in an RTF box. The method you outlined here gets around that by putting the appropriate calc fields right in the detail region and even allows for bullets in a calc field using a Winding character. If there is yet another way to do that, I'd sure like to know about it, even though I do little development nowadays.

    FYI, my use of these lines was in a letter listing a variable list of things the recipient needed to send back to the company. (For this I had a form based on a set and logical fields in one table, using a check box format, and a series of items clients may or may not need to send back to the company in another table linked to the parent table.) In an RTF box on a letter layout everything worked great except that in place of bullets I ended up just using "--". Not bad, but not the bullets I would have preferred.

    And Bob, yes an RTF box can be set to shrink (which is great) but I had found no way to put "real" bullets in an RTF box, though now that I think about it I guess I could have tried having 2 RTF boxes side by side with the first one using a Wingding font--I suppose that might work and in some ways might even be a better option. More testing! [Edit: I just tested this and it not only works nicely, in some respects it is more flexible and easier to set up in the layout. Why didn't I think of this method way back when!]

    And yes, the detail section can also be set to shrink (also great) but that alone does nothing to skip the lines we are talking about here and shrink the dead space left by skipped lines--at least not in my testing of all this. For that one seems to have to set the calc field properties to allow shrinking AND one has to be very careful with how these calc fields are positioned in the layout (i.e., no overlapping and consistent spacing for all fields and lines).

    Raymond Lyons
    Last edited by Raymond Lyons; 02-17-2012 at 12:06 PM.

  19. #19
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by Raymond Lyons View Post
    And yes, the detail section can also be set to shrink (also great) but that alone does nothing to skip the lines we are talking about here
    It does at least in v8. If I have some time, I will post an example.
    Can't quite remember how I did the bullets. It was a while back but as I said there should be an example posted.

  20. #20
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by G Gabriel View Post
    It does at least in v8. If I have some time, I will post an example.
    Can't quite remember how I did the bullets. It was a while back but as I said there should be an example posted.
    Once again you are correct! Something in my first test layout was keeping it from working (not sure what). But when I created a new test layout and just set the detail section to shrink (and not the Calc fields) it does work. Still, if I were re-doing my old project I think I'd go with side by side RTF boxes set to shrink, especially because in my old layout I had other, regular table fields off the the right side of my RTF box, and I have to think that would stop the dead space between calc fields from shrinking if the calc fields just in the detail region (but not in an RTF box).

    Raymond Lyons
    Last edited by Raymond Lyons; 02-17-2012 at 01:43 PM.

  21. #21
    "Certified" Alphaholic G Gabriel's Avatar
    Real Name
    G. Gabriel
    Join Date
    Oct 2004
    Posts
    7,204

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Again, nothing against RTF. In fact I use RTF a lot in reports. Just offering other options particularly when RTF might not be the best, as in lining certain fields vertically.

  22. #22
    Member
    Real Name
    Barry C.
    Join Date
    Oct 2006
    Posts
    13

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    With the advice above, What worked for me was to check mark "shrink contents in band" in group header. I have no detail section.

    Setting a fields format in the report to "Blank if Zero" works great, but only on that field. Doesn't wipe the line.

    Would be awesome if you could place a hidden calc field in the report that could wipe the entire line based on it's value!
    Tried "remove_blank_lines()" many ways, but couldn't get it to take the line out.

    In Alpha4 "DOS" you put "B-" in the format and if the value in that field was 0 it removed all content in that line. Was a beautiful thing!!!

  23. #23
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,685

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    A conditional object might do the same so the fields only display based on the calculation you use to test for zero or blank.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  24. #24
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,620

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by V7 Noob View Post
    With the advice above, What worked for me was to check mark "shrink contents in band" in group header. I have no detail section.

    Setting a fields format in the report to "Blank if Zero" works great, but only on that field. Doesn't wipe the line.

    Would be awesome if you could place a hidden calc field in the report that could wipe the entire line based on it's value!
    Tried "remove_blank_lines()" many ways, but couldn't get it to take the line out.

    In Alpha4 "DOS" you put "B-" in the format and if the value in that field was 0 it removed all content in that line. Was a beautiful thing!!!
    Must of missed this thread before.

    You'll want a conditional object with a condition of <yourfield>=0 with nothing in it and a default area of the fields you want to display.

    Use the shrink option in as many places as needed.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  25. #25
    Member
    Real Name
    Barry C.
    Join Date
    Oct 2006
    Posts
    13

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    The conditional object works Great!

    Didn't realize you could hide or show multiple fields with-in them.

    Was nice to see, although calc fields inside the properties, expression builder, don't show. You can still use them in the expression!

    Thanks so much!

  26. #26
    "Certified" Alphaholic DaveM's Avatar
    Real Name
    Dave Mason
    Join Date
    Jul 2000
    Location
    Hudson, FL
    Posts
    6,023

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Possibly a filter on the report that only allows >0.00 records based on the fields? Then you don't have to code much of anything
    Dave Mason
    dave@aldausa.com
    Skype is dave.mason46

  27. #27
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Interesting how this thread moved from one issue to one now far removed from the original.

  28. #28
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,620

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by DaveM View Post
    Possibly a filter on the report that only allows >0.00 records based on the fields? Then you don't have to code much of anything
    Dave

    Good idea.

    But, I went back to reread the issue and noticed that Barry is not using a detail section. So the summary could result in zero, but the report filter won't catch a 5, -5 pair of records.

    Ray

    Yeah. Kinda funny how the thread morphed, but a good job of finding an old thread that is related.
    Al Buchholz
    Bookwood Systems, LTD
    Weekly QReportBuilder Webinars Thursday 1 pm CST

    Occam's Razor - KISS
    Normalize till it hurts - De-normalize till it works.
    Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
    When we triage a problem it is much easier to read sample systems than to read a mind.

  29. #29
    "Certified" Alphaholic MoGrace's Avatar
    Real Name
    Robin
    Join Date
    Mar 2006
    Location
    Los Angeles
    Posts
    3,685

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Actually, I think the additional info is right on since the rtf object is not the only solution.
    Robin

    Discernment is not needed in things that differ, but in those things that appear to be the same. - Miles Sanford

  30. #30
    "Certified" Alphaholic
    Real Name
    Raymond Lyons
    Join Date
    Apr 2000
    Location
    Carlsbad, CA
    Posts
    2,143

    Default Re: how to remove entire line if numeric field is 0.00 in a report

    Quote Originally Posted by MoGrace View Post
    Actually, I think the additional info is right on since the rtf object is not the only solution.
    Hmm, not the only solution to what issue? It's not 100% clear to my tired eyes but I suspect Barry's issue is very different from the original 2012 issue that started this thread. As for the original issue, as usual Stan Matthews pretty much nailed it using an rtf. I'm not sure one or more conditional objects would have addressed that original question at all, or at least in any simple way, but I do know that one can do surprising things with rtf's very simply that I for one would be hard pressed to do with conditional objects. But yes Al, good job of Barry's for finding a somewhat related thread that suggested a solution to his particular problem.

Similar Threads

  1. Show/Hide not hiding the entire field
    By Tbrondolo in forum Application Server Version 10 - Web/Browser Applications
    Replies: 2
    Last Post: 09-08-2011, 05:57 PM
  2. remove line
    By Clunes in forum Alpha Five Version 10 - Desktop Applications
    Replies: 9
    Last Post: 03-01-2010, 08:09 PM
  3. Suppress a line in report if a field is blank
    By Rokrz in forum Alpha Five Version 9 - Desktop Applications
    Replies: 2
    Last Post: 02-10-2009, 01:05 PM
  4. Blank line in report for empty field
    By Doris Davidoff in forum Alpha Five Version 5
    Replies: 4
    Last Post: 02-18-2003, 07:17 AM
  5. Delete entire line format option
    By skmont in forum Alpha Five Version 4
    Replies: 3
    Last Post: 05-01-2001, 09:07 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •