Suppress entire group (H/F) if footer contains null field value 2

[traigo]

How do I suppress an entire group and everything in it if the footer contains a null field value. My footer has a total field. If there is no data in the details I can suppress the details and the footer. How do you suppress the entire section if there is a null value in the footer? I have tried
FORMULA=IIF(isnull({#Batch_sum_1}), TRUE, FALSE)
but that suppress all headers if there is one null value in any footer. I have this layout

ReportHeader
PageHeader
Groupheader1
Groupheader2
Groupheader3
details
Groupfooter3
Groupfooter2
Groupfooter1
Pagefooter
Reportfooter

My totals are in each of the footer sections. I have grouping set for all. I want to suppress everything between groupheader2 and groupfooter2 if a null value is found in groupfooter2 batch_sum_1 field for that group only. I'm not worried about group1 or 3.

Thanks,

traigo

 

[zootweller]

Tried conditionaly supressing the group and details sections using 'isnull({batch_sum_1})'?
Alternatively you could make a formula that has 'isnull({#Batch_sum_1})' and add it in the select expert, giving it a value of false. That way any running totals should not be affected by hidden data..

 

[traigo]

I've suppressed the footer and details sections separately. The details section will suppress if there is no data, and the footer will suppress if the value is null. The value will be null if there is no data. So an empty details section will suppress the details and the footer but the header still displays. If I set the header to suppress on isnull({#batch_sum_1}), it will suppress all headers for that group if there is a null value anywhere on the report not just that group. If there are no null values, then all the headers will display correctly.

 

[traigo]

Let me clarify a little. If the header is set to suppress on isnull({#batch_sum_1}), it will suppress all headers for that group if there is a null value in the field #batch_sum_1 anywhere on the report not just that in that specific instance of group.

 

[chelseatech]

How is this for an approach to your problem....

Create a formula to check for the nulls
If IsNull({table.field}) then 1 else 0

Then use GROUP SELECTION FORMULA to suppress the sections where the total of that formula is greater than zero.

Sum({@nullcounter},{groupfield}) >0


Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[johnstrang]

I had a similar problem - suppressing an entire group if none of the records in that group matched a parameter field.

chelseatech's suggestion (above) provided an ideal solution to the problem

Many thanks,
John

 

[lbass]

Not sure why the group header is appearing if there truly is no data in the group. But anyway, I think the problem is that you are trying to use a running total for suppression, and the value is not available until the group footer section.

I think that the sum of a value within a group is null ONLY when all values within the group are null. Therefore, you could use the field that you are summing in the running total in a minimum formula for suppression. Let's say that {#batch_sum_1} is a running total that sums {table.batchamt} with a reset on your groupfield (let's say that it's grouped on {table.date}). Then try the following in the section expert as a group suppression formula:

isnull(minimum({table.batchamt},{table.date}))

If there were any batch amount for a particular date group, then the minimum would be a number, and the sum would not be null.

-LB

 

[traigo]

That's all good for other situations, but won't work for me. Remember that I am in the second of 3 group sections. The first is suppressed entirely, it's just there for group on record. The second, where I am, is grouped on another within that, and the third, is, well, I'm not really sure. This is a report someone else wrote that I have to try and figure out without entirely rewriting it. That sounds like it's going to be the case. I'm having a really hard time trying to follow what they did. There's suppressed fields all over the place for record numbers, sums, running totals, etc. But I have to deal with it for now, so I'm trying to make it work with what I've got.

There are many null values that are weeded out in the detail section. Given this

Field1 Field2
Value1 1234
Value1
Value1 1234
Value1
Value2

When grouping on field1, the group that contains Value1 will have a sum of 2468, but it will diplay all records. There will be 4 records shown under the group details. For Value2, 1 record will show but will have a null value for the sum. I suppressed blank values, so now only 2 show up for Value1 and nothing for Value2, but it still displays the group headers and footers for Value2 with a blank where the sum should be, a null value. I can easily suppress the footer if the value is null, but not the header. Is there a way to say formula=iif groupname footer.suppressed=true, true, false?

There are 10 types of people in the world, those that understand binary, and those that don't.

 

[lbass]

Are you responding to me? I think my method will work for you, please try it. But certainly you need to know what your third group is--you should be able to see what this is by running the cursor over the gray area to the left of the display that says GH3 while in design mode.

-LB

 

[traigo]

I've tried both lbass and chealseatech's approaches. Neither work. Chealseatech's says "can't create summary/running total field" and lbass' doesn't suppress anything. The thing is that it isn't a summary of a field in a table, its the sum of a formula field. It's formula is:

iif({GL_TX.ORIG_CODE} = '1' or {GL_TX.ORIG_CODE} = '6' or {GL_TX.ORIG_CODE} = 'A', iif({@ACH_Depos} = 0, {@S_amt}, 0), 0)

and @s_amt is:

iif({GL_TX.CRDR} = 'D', {GL_TX.AMOUNT}, -{GL_TX.AMOUNT})

There are 10 types of people in the world, those that understand binary, and those that don't.

 

[lbass]

It looks like these formulas would result in "0's", not nulls. Are you sure you don't have the results formatted to "Suppress if zero"? If you do, then you should be conditionally suppressing if values = zero, not if they are null.

Is this the formula you were calling {#batch_sum_1}? If so, why are you labeling it with a "#" which ordinarily indicates a running total?

-LB

 

[traigo]

#batch_sum_1 is a running total of @batch_amt. I want it to suppress the entire group if there is no total. The running sum comes up with a null value for some reason if it is a sum of zero. It does so even if @batch_amt has 2 records, one is 10 and the other -10. The sum is 0, but it shows up null. I can suppress the footer with iif(isnull({#batch_sum_1}),true,false). It works fine, but I still have a header. This damn thing is hard to follow. I don't know what this person was doing when they wrote this, but I haven't been able to rewrite it and get the same results yet.

There are 10 types of people in the world, those that understand binary, and those that don't.

 

[lbass]

Chelseatech's solution will suppress an entire group if there are ANY nulls in the group. My understanding is that you only want to suppress the group if ALL records are null for that value.

What is the group field for the group you want to suppress?

Go to format->section->group header->suppress->x+2 and enter:

isnull(minimum({@batch_amt},{table.yourgrpfield}))

Use the formula that you are accumulating (not the running total). Repeat for the details section.

You should also right click on your running total field in both detail and group footer->format->number->customize->and uncheck "Suppress if Zero" so that you can see when you actually have nulls versus zeros. I think this might be confusing the issue.

-LB

 

[traigo]

I give up. I'm scrapping the report. I can't figure out what they did or how they did it. If they want the report later, I will write it myself. Thanks for all the help anyway guys and/or gals!

There are 10 types of people in the world, those that understand binary, and those that don't.