[2 days left] Whatâ€™s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Sum and Avg in SSRS

Posted on 2009-05-04
Medium Priority
1,162 Views
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
Question by:SamRunyon
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 4
• 3

LVL 21

Expert Comment

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

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

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

Author Comment

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

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

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

thanks for the help.
Sam
0

LVL 21

Accepted Solution

JestersGrind earned 2000 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

Question has a verified solution.

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

Why is this different from all of the other step by step guides? Â Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff lâ€¦
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
###### Suggested Courses
Course of the Month14 days, 11 hours left to enroll