Group summary

I have a report with foure columns: Group1, Name1, Status1, and Count1.

The fields are grouped by Group field. So at the end of each group I want to display the Sum for all Count fields for which Status1='L'

I've tried to set the new field to a formula:

Sum ({Count1},{Status1} , "L")

I keep getting an error "A number field or currency amount field is required here" in field Count1.

What am I doing wrong? Or how do I display Sum of all Count1 fields in each Group?
LVL 35
YZlatAsked:
Who is Participating?
 
bdreed35Connect With a Mentor Commented:
You need to create a formula first, then sum the formula:

//@StatusL
if {Status1} = "L"
then {Count1}
else 0

You can then insert a summary off of the formula or create another formula to do so:

Sum({@StatusL},{Group1})
0
 
YZlatAuthor Commented:
bdreed35, the source for my report was an XML file where Count1 is a numeric field, but for some reason Crystal Reports do not read it as numeric.


...
<COUNT1>43</COUNT1>
....

I've tried right-clicking on the Group footer, selecting Insert->Subtotal but in the combobox "Insert subtotal for the field" I don't see Count1. What's going on?

Is there a workaround?
0
 
bdreed35Commented:
You can try using this instead:

//@StatusL
if {Status1} = "L"
then val({Count1})
else 0
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
YZlatAuthor Commented:
but where do I put this code? Keep in mind that I'm a novice with Crystal Reports
0
 
bdreed35Commented:
From the View menu, Choose Field Explorer.
Right click Formulas, choose New.
Give it the name "StatusL" and click Use Editor.
Copy and paste the formula into the editor, and make sure you replace {Status1} and {Count1} with the full names.
0
 
YZlatAuthor Commented:
I've done all that. How do I display the value on the report itself now?
0
 
mlmccCommented:
In the field insertion popup show the formula tab abd drag the formula onto the report just as you do a database field.

mlmcc
0
 
YZlatAuthor Commented:
the problem is that the database for my report is an xml file where COUNT1 field is defined in the following way

<COUNT1>43</COUNT1>

and Crystal Reports does not perceive it as an integer
0
 
bdreed35Commented:
Is this the actual data that you see in the field?
0
 
YZlatAuthor Commented:
yes
0
 
YZlatAuthor Commented:
but Crystal Report reads it as a string "43" instead of an integer 43
0
 
YZlatAuthor Commented:
bdreed35 please help! I'll up the points
0
 
bdreed35Commented:
This formula should work: (it is the same as I posted yesterday)

//@StatusL
if {Status1} = "L"
then val({Count1})
else 0
0
 
YZlatAuthor Commented:
but how do I display Sum({@StatusL},{Group1}) on the report?
0
 
bdreed35Connect With a Mentor Commented:
Once you have the formula built, go to the the Insert menu in the designer, and choose Summary.
In the Summary dialog, choose the formula as the field to summarize, sum as the type of summary, and the location should be Group 1
0
 
YZlatAuthor Commented:
This is great! Thank you!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.