Solved

Sum and Avg in SSRS

Posted on 2009-05-04
7
1,147 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:SamRunyon
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now