Selection Criteria to exclude records... 1

[bessebo]

I have a report that totals at a group level and there are 4 different totals at this group level. The group total field names are: Sum of @ExtPriceYTD, Sum of @ShippedQtyYTD, Sum of @ExtPriceLastYTD, and Sum of @ShippedQtyLastYTD.

All I want to do is not include the record if all of these fields are 0. So, what I did was go to the Select Expert and add a formula to exclude the record if all 4 fields are 0.
The problem is when I add this formula it actually puts a selection value in for each of the fields and sets it to where that field is not equal to 0. As a result, it excludes any of those records where there is a 0 and not where they are all equal to 0.

Can anyone help here?

Thanks,
Bessebo

 

[kenhamady]

You mention 4 totals, each of a formula field, and then you talk about the "field" being zero.

So, it isn't clear if you want to exclude all records where the formula fields themselves are all zero; or if you want to exclude all GROUPS where these SUBTOTALS are all zero.

Try this: Create a formula that adds the 4 values together, and then add a rule that this 5th formula has to be <> 0. Let us know if some of these values can be negative. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[bessebo]

I apologize if I was unclear. We want to exclude the total record (since I am supressing the detail above the group total line) if all of the 4 total fields are 0. Yes, some of the totals could be negative.

One other question, in this report I am using the YearToDate function and the user wants to be able to set an ASOF date and if I read the Seagate Help it looks like there isn't an easy way for a user to do this. Since we are running Seagate Info I assume that the date it's using is the date and time on the server. Also, we don't give our users any type of Designer rights so they can't do a Set Print Date/Time from the Report menu. Do you have any idea how I can give a user the ability to change the ASOF date (in other words, run the report As OF this date) when they are running a report from Seagate Info?

Thanks,
Bessebo

 

[bessebo]

KenHamady suggested:

Try this: Create a formula that adds the 4 values together, and then add a rule that this 5th formula has to be <> 0. Let us know if some of these values can be negative.

I created a new formula adding up these group total fields but when I go into the Select Expert I cannot see this new formula field to set criteria on it...

Bessebo

 

[balves]

Create a formula for each group total like this:

If @Sum1 <> 0
Then @Sum1
Else &quot;&quot;

Place the formulas on the Group footer. Format the Group footer section to suppress if blank.

If you have a label on the Group footer like &quot;Totals = &quot; you'll need to do a similar formula that checks all 4 totals otherwise &quot;&quot;. That way you'll have a bunch of objects that will be &quot;&quot; when all of the totals are 0 and the footer will be suppressed.

Hope this is what you want to do. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[kenhamady]

You don't want to eliminate a record (because the there is no total &quot;record&quot; to eliminate). You want to suppress a section so that it doesn't print, which is much easier. I recommend the following:

Use Format-> Section to open the section expert. Highlight the section that you need to suppress (probably a group footer) and click the formula button next to the suppress property.

Now enter the following formula by finding the 4 subtotals in the field list (look at the Group 'Sigma' fields) and double clicking each one:

Sum ( {@ExtPriceYTD},{yourgroup}) = 0 and
Sum ( {@ShippedQtyYTD},{yourgroup}) = 0 and
Sum ( {@ExtPriceLastYTD},{yourgroup}) = 0 and
Sum ( {@ShippedQtyLastYTD},{yourgroup}) = 0

---

If you prompt them for a date you can use this to calculate the 3 other dates you need to do these formulas. It takes more work from the designer but it can be done.

You would need to calculate the first of the year, the first day of last year, and one year before the prompt date. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[bessebo]

Thanks for your help. It worked like a charm...

Regards,
Bessebo