• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 633
  • Last Modified:

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?
0
YZlat
Asked:
YZlat
  • 9
  • 6
2 Solutions
 
bdreed35Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
bdreed35Commented:
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

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now