Crystal Reports SUM formula help. Don't SUM values with specific IDs

Question

I have a table with rows of Invoice data and I want to SUM the values of all line items where the Item ID is not equal to 0000 or 9999.

The Item IDs I want to exlcude 0000 and 9999 never change.

ITEM ID	NAME	WORK VALUE	TOTAL COMPLETED
0000	HOLD 1	0.00	1,234
1234	MATERIAL A	333.00	76.00
1235	MATERIAL B	567.00	7043.00
1236	MATERIAL C	981.00	321.00
1237	MATERIAL 4	430.00	5445.00
1238	MATERIAL 5	10.00	897.00
1239	MATERIAL 6	18.00	654.00
1240	MATERIAL 7	882.00	3.00
1241	MATERIAL 8	777.00	65.00
9999	ZY HOLD	0.00	111.00

So the value returned in the report from the example above should = 18,502.00 not 20,847.00

I have tried:

IF NOT((TONUMBER({Invoices.InvoiceItems~ItemNumber}) = 9999))

THEN (SUM({Invoices.InvoiceItems~InvoiceValue})+SUM({Invoices.InvoiceItems~TotalCompleted}

but this doesn't work, it still sums the value from the 9999 line item

Answer 1

I would create a Running Total Field to accomplish this.

A Running Total Field works very similar to the Summary Field, but allows a lot more control over which records are evaluated when summarizing the data. To setup a Running Total Field for your needs try the following steps.

1. Create a new Running Total Field.
2. Set the "Field to summarize" to use the Total Completed column from your database. Set the "Type of summary" to SUM.
3. Set the radio button in the "Evaluate" section to "Use a formula", then click the X-2 button to create the formula that will determine if a row of data should be included in the sum or not. Whatever formula you enter here will need to return a boolean value. When this value is TRUE, the row's data is included, and when it's FALSE the row's data will be excluded. I would use the following formula here: TONUMBER({Invoices.InvoiceItems~ItemNumber}) <> 9999 AND TONUMBER({Invoices.InvoiceItems~ItemNumber}) <> 0000
4. The last thing to do is the setup the Reset conditions for the Running Total Field. This would be used if you were grouping data in some fashion such as by Customer and would allow you to sum the data for a single customer, then reset to zero for the next customer. If you are not using any grouping you can probably just leave this set to "Never".
5. Click OK to finish. :)

At this point all you need to do is drop the Running Total Field you just created into your report. Be mindful about which sections you place this field within though. If you place it in a header section, you will likely find it doesn't add in the last record in your dataset because the report hasn't printed it to the details section yet. I recommend placing Running Total Fields in Footer sections to avoid this nuance.

I hope this helps! And if you do have some grouping levels that you need help with setting the reset conditions just let me know what the grouping levels are for your report and I can update this answer for you.