We help IT Professionals succeed at work.

Crystal Reports summary field generates a count, not a sum

4,310 Views
Last Modified: 2012-08-13
I am using Crystal Reports for Visual Studio 2010. I create a group on a field to get a subtotal
for that group. When I choose the field to summarize on, I select "SUM" for Calculate the
Summary. The data comes from a data set and the field is defined as double. The result generated
is the "COUNT" not the "SUM." Please advise on how to get the "SUM." Thanks in advance.
Comment
Watch Question

Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
If you can select sum and select it, unless it somehow changed you are getting the sum.

Have you checked the summary to verify it wasn't changed somehow?

mlmcc
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
musalmanERP Consultant
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I have not solved the problem. Other fields that are printed within the group are non-numeric. The Employee code that is used to determine  a group is non-numeric. The field I want to subtotal on is numeric.
Thanks for the reply musalman. I do not know how to use the ToNumber field in this situation, can you please explain. Thanks Jerry
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
If the field is numeric you don't need it.

How are you choosing the field?
Is it on the report?

Where did you put the summary?
Have you right clicked the summary and tried to change it?
Does it show SUM or COUNT?

mlmcc

Commented:
Is it possible to attach the report and tell us what column you are trying to sum?
Yes, mlmcc has spotted an important thing, did you put the summary field in the correct section of the report, if you put it wrongly, it won't work properly.

Author

Commented:
Thanks for the reply. mimcc, I will aswer your questions as follows:
1. I do not understand your first statement.
2.When I create a new Crystal report and come to the grouping dialog, I select the Employee Code for "Group By". From the Summaries dialog, I select the amount field and choose "SUM".
3. In the Group Footer
4. Yes, Many times.
5. it shows "sum"
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
My first statement was saying since you say it is a numeric field there is no need to use the ToNumber function to convert the field to a number.

Since it shows SUM are you displaying the numbers in the field?

Have you summed them to know what the answer should be?
It is possible for 5 numbers to add to 5

What are the values in the field that is being summed?

Are you filtering the data in any way?

Try this
Create a NEW report using the same data source
Drop the numeric field in the details section
Do a SUM summary on it.

mlmcc

Author

Commented:
rgn2121, I could attach the report; but, let me explain where I think the problem is. I am producing the Crystal Report from a data set. I read Access Tables in VB.net and produce the Data set from the tables that I read. I create the Data set by going to Projects, Add New Item, Data, Dataset that creates a .xsd file.
I Add Datatable and then add the fields to the dataset. I click on a field and select properties to change the field to system.double for those fields that I want to be numeric. That creates the Dataset that I filll using VB.NET.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are you creating the report against the XSD file or a table?

mlmcc
Very seriously, when you print the individual record, kindly check all the data, I strongly believed at least one of them is not NUMERIC. The dataset was pull from Access and I don't understand why you have to CHANGE a FIELD to system.double, unless at the first place, the FIELD wasn't a NUMERIC FIELD.

Commented:
Let me make a suggestion since a do something similar except that I am pulling from Oracle tables.  Once you get your in memoery dataset and you are ready to report on it, I would recommend writing the schema of your dataset out and using that to build reports especially if they are not compiled reports.

I have a coworker that uses the method you described above and every time they add/remove a column or change a datatype, they have to do and manually alter that xsd in designer.  To me that can be tedious and prone to error.  What I prefer is to use the DataSet.WriteXmlSchema() method.  Once it writes out, I delete my old xsd and add an existing item to my project...the new xsd.  I then go to my reports and verify the database.  This way I know that what I am building my reports against is exactly what my dataset looks like.

Just a thought...

Author

Commented:
I have the problem solved, I don't know how to award points; but, thanks to you all.

The problem was the data being presented to Crystal Reports from the dataset was not numeric even though it is defined as numeric in the dataset. I used CDBL in crystal Reports to insure I had numeric data.
If you would to ask me, you can dedicate to myself and mlmcc, who are the ones telling you the numeric issue.
Actually first look from your problem remembers mine when I started using Crystal Report, that's why I keep asking you to check the data whether all are numerics.

Commented:
No points needed for me...I didn't do anything.  Split between the other experts...glad you got it working!

Author

Commented:
Reply, I will take Adriankohws advice and split the points between him and mimcc - Thanks to all
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
Please create sum of field on that field and select Reset on group value change.
Mike McCrackenSenior Consultant
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2013

Commented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Author

Commented:
I accept mimcc's solution

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.