Solved

Sum and Avg in SSRS

Posted on 2009-05-04
7
1,155 Views
Last Modified: 2013-11-10
I have many tables that I need to sums and avg of.
Here is the table that has a calculated field.

=ROUND(Fields!JHVolume.Value)*(Round(Fields!BartleyEngery.Value/Fields!BartleyVolume.Value,4))

This works and give the value I need

Now I need to do a sum and a avg. From this table.

Here is what I have tried.
=sum(Fields!JHVolume.Value, "DataSetTotal")*sum(Fields!BartleyEngery.Value/Fields!BartleyVolume.Value,"DataSetTotal")

It gives me a crazy total.
Any help?

Sam
0
Comment
Question by:SamRunyon
  • 4
  • 3
7 Comments
 
LVL 21

Expert Comment

by:JestersGrind
ID: 24297084
You're multiplying the two sum together.  Are you sure that's what you want to do?  Also, you can use the AVG function to get the average.

Greg


0
 

Author Comment

by:SamRunyon
ID: 24297182
Jester, yes here is the expression I used.

=sum(ROUND(Fields!JHVolume.Value, "DataSetTotal")*(Round(Fields!BartleyEngery.Value/Fields!BartleyVolume.Value,4, "DataSetTotal")))

and this is the error message.

[rsMissingAggregateScope] The Value expression for the textbox textbox28 uses an aggregate expression without a scope.  A scope is required for all aggregates used outside of a data region unless the report contains exactly one data set.
Build complete -- 1 errors, 0 warnings

thanks, Sam
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 24297342
I don't think the SUM is necessary.  Does this work?

=ROUND(Fields!JHVolume.Value, "DataSetTotal")*(Round(Fields!BartleyEngery.Value/Fields!BartleyVolume.Value,4, "DataSetTotal"))

Greg



0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

Author Comment

by:SamRunyon
ID: 24297418
Greg, this is the error I get when I try that.

[rsCompilerErrorInExpression] The Value expression for the textbox textbox28 contains an error: [BC30519] Overload resolution failed because no accessible 'Round' can be called without a narrowing conversion:
Build complete -- 1 errors, 0 warnings
thanks, Sam
0
 
LVL 21

Expert Comment

by:JestersGrind
ID: 24297695
Oops, you have to explicitly convert the values like this:

=SUM(CDbl(Fields!JHVolume.Value), "DataSetTotal")*sum(CDbl(Fields!BartleyEngery.Value)/CDbl(Fields!BartleyVolume.Value),"DataSetTotal")

Greg


0
 

Author Comment

by:SamRunyon
ID: 24298031
Greg, that work for my avg. but the sum is double.

thanks for the help.
Sam
0
 
LVL 21

Accepted Solution

by:
JestersGrind earned 500 total points
ID: 24303660
Looking at that expression, I don't see any reason that the sum would be doubled.  Are you sure that the data underneath the expression isn't doubled?  The average would still look the same, but the sum would be doubled.

Greg


0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question