6 Replies Latest reply on Oct 5, 2015 8:02 AM by sunil72

# Conditional summary in a summary report

Hello Friends:

My Goal: to report state summary of different fields, in a summary report

Field Type1: Yes, or No

Field Type2: Natural numbers

Reporting for Field Type1: Total count of “Yes”

Reporting for Field Type2: Average of numbers

Problem: I tried using GetSummary function but it doesn’t filter out “No” items

• ###### 1. Re: Conditional summary in a summary report

One approach: create a calculation field as field1 = "Yes" and sum that field – or use Case ( field1 = "Yes" ; anyResult ) and count it. (Counting the first calc field would be pointless, because it either holds 1 or 0, but is never empty.)

Other approach: If field1 is essentially Boolean, use 1/0 instead of Yes/No, then sum that field directly.

• ###### 2. Re: Conditional summary in a summary report

Hey Oliver, thanks for the prompt reply you have always been a life saver for me. In the other approach, all I have to do is use: If(Field1="Yes", 1; 0). Am I correct? If correct, then I will need to get the sum for each of the fields and then sort the fields to get the state summary. Is this step also correct. Also, is there a way to automate If(Field1="Yes", 1; 0) for all the fields? Thanks a lot!

• ###### 3. Re: Conditional summary in a summary report

I did try what I was asking guidance for; the calculation field: Sum(Field1) is blank even after the field has been sorted.

• ###### 4. Re: Conditional summary in a summary report

sunil72 wrote:

In the other approach, all I have to do is use: If(Field1="Yes", 1; 0). Am I correct?

If you're summing that calc field to get the count, you can simply write

Field1 = "Yes"

which is 1 or 0.

• ###### 5. Re: Conditional summary in a summary report

sunil72 wrote:

the calculation field: Sum(Field1) is blank even after the field has been sorted.

You misunderstood me.

You have:

• a text field (field1) that is "Yes" or "No".

• a calc field (say, cField1Yes) as field1 = "Yes"

• a summary field as TotalOf: cField1Yes.

It is this summary field that will show you the correct results, as per its placement in a Grand Total or Sub-summary parts.

And the alternative:

Switch your field1 to be a number field where 1 means Yes, 0/<empty> means "No".

Then you'd only need

• a summary field as TotalOf: field1

• ###### 6. Re: Conditional summary in a summary report

Thanks Oliver!