[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1164
  • Last Modified:

Sum and Avg in SSRS

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
SamRunyon
Asked:
SamRunyon
  • 4
  • 3
1 Solution
 
JestersGrindCommented:
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
 
SamRunyonAuthor Commented:
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
 
JestersGrindCommented:
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
SamRunyonAuthor Commented:
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
 
JestersGrindCommented:
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
 
SamRunyonAuthor Commented:
Greg, that work for my avg. but the sum is double.

thanks for the help.
Sam
0
 
JestersGrindCommented:
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

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now