How do I create a conditional Sum field? 2

[Codepoet42]

This should be really easy, but for some reason I can't figure it out. I have a report in Crystal XI that has a Sum field in the group footer. This field is the sum of the LineItemHistory.LineCost (number) value in the details section.

The sum is great at showing the sum of the LineCost values for all line items in the details section, but here's the problem: The LineCost value is in a record (LineItemHistory) that also has a boolean "Include" field, and I only need to show the sum of the line items that are included (ie Include = 1).

I can suppress the details section so only records with "{LineItemHistory.Include} = True" display. However, I can't figure out how to add a condition to the Sum field so it only totals the values of LineItemHistory records with a 1 (true) for the Include field.

I tried using a formula field (@SumLineCost) in the group footer to conditionally set "Formula = Sum({LineItemHistory.LineCost})" only when LineItemHistory.Include is true, but it keeps coming up with the total of all line items, not just the included ones.

Can anyone help me? The report draws all its data from tables in our MSDE 2000 SQL database. There are no .ttx files or stored procedures involved.

Thanks in advance...

 

[dgillz]

First, as you have discovered, suppressed records still evaluate in any sub or grand totals in Crystal.

You need to exclude these records from your report entirely with a record selection formula. Use the same formula you are currently using to suppress the records, only put it in the record selection formula.

If you need help on doing this, post again.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[Codepoet42]

Hey dgillz, thanks for the reply. You know, I did choose the "Select Expert" and I entered "{LineItemHistory.Include} = True" as the formula, but that didn't have any effect. The unincluded line item records still appeared in the details section, and the sum fields still included the line item costs for the unincluded records.

Perhaps I'm not entering the formula correctly to skip the records where LineItemHistory.Include = 0? As I said, I entered "{LineItemHistory.Include} = True" and I clicked [OK]. But then I opened the Select Expert again, and the formula field just showed "{LineItemHistory.Include}" without the "= True."

Is there another syntax I can use to make it work?

 

[lbass]

What are the field options displayed when you right click on {LineItemHIstory.Include}? If it is "1" and "0", then you need to set the field = 1 in the select expert.

-LB

 

[Codepoet42]

Hi lbass, thanks for the post to this thread. I drove in to work tonight to try your suggestion, but so far it hasn't worked. I tried right-clicking in the Select Expert dialog on the {LineItemHistory.Include} tab and also on the {LineItemHistory.Include} text in the Formula window when I click [Show Formula >>>], but I don't see any shortcut menu (or anything) when I right-click the tab, and the only option enabled on the shortcut menu when I right-click the text in the formula textbox is "Select All." Should I right-click anywhere else to see the 0 or 1?

I updated the formula to "ToText({LineItemHistory.Include}) ='1'", but that didn't filter out the unincluded records for the order. In fact, it gives me an "A boolean is required here." error if I just type "{LineItemHistory.Include} =1", and I don't get any error with the previous formula. I also tried "CStr({LineItemHistory.Include} =1", and it shows no errors, but it still doesn't filter out the unincluded line items.

Finally, I tried adding the "Include" field to the details section in the report and just suppressing it. I then formatted the field and set the value in the "Boolean" tab to "1 or 0," but that had no effect either.

So I've tried everything I can think of. Any other suggestions would be much appreciated.

Should I use a stored procedure to return the LineQty sum I need and just add that stored proc-based field to the report? What am I missing?

 

[dgillz]

This makes no sense, because your formula IS a boolean formula.

Don't use the select expert, just enter a record selection formula manually.

Click on report, edit selection formula, record and enter the formula.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

 

[lbass]

If you right click on the field when placed in the detail section, you will see "browse field data". Or if you right click on the field in the field list within the formula editor, you will see "browse data". You should report back with the datatype (it will say "type: "), and also with the field results it shows below.

-LB

 

[Codepoet42]

Thanks to everyone for all your input. I finally figured out the problem, and the cause was in a place I should have thought to look. This report is called from a VB 6 application, and it turns out that the RecordSelectionFormula value is set in the code before the report is previewed. So no matter what I entered in the Select Expert or the Record Selection Formula field in Crystal, it was being overridden by the formula set in the code.

I updated the selection formula in the code by appending "and {LineItemHistory.Include}" and now both the details section and the sum total fields appear correctly. Thanks again for all your input - I'm fairly new to Crystal and I learned a great deal from this issue.