Trying to aggregate calculated values

Question

Trying to aggregate calculated values

Opus

Default port

Points: 1419
More actions
October 11, 2007 at 8:33 am

#375622

I know you can't aggregate a calculated value and I'm having a problem finding a solution
I have 3 groups: Course(1), Term(2), Category(3). Course = a particular class (i.e) Algebra; Term = 1 of 4 terms in our school year; Category = for a class, one may have multiple categories like, participation, homework, quizzes, test, etc.
A class can be weighted for a Category like homework and will count 20% of the grade, tests 40% of the grade and so on. For each Category, I calculate the weighted total using the following:
=iif(Fields!Weight.Value<>0,sum(Fields!Earned.Value)/sum(Fields!Poss.Value)*Fields!Weight.Value*.01,0)
My delemma is to sum up all those calculated values in the Term group footer.
Thanks

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ben Orlowitz SSC Enthusiast Points: 148 More actions · Answer 1

Are you using Access to do this? Is there a SQL Server back end? You could persist the calculated column by performing the calculations in a stored procedure and then using standard SQL aggregate functions. The application would take the values assigned for weights and feed them as parameters to the stored procedure. The sproc would in turn build the table. But this is not available if this is a pure Access application.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

Opus (10/11/2007)
=iif(Fields!Weight.Value<>0,sum(Fields!Earned.Value)/sum(Fields!Poss.Value)*Fields!Weight.Value*.01,0)

I am assuming you are using SSRS to develop this report. In SSRS you could put the calculation as a calculated field in the dataset and use that field in the group footer with the desired aggregate function.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Opus Default port Points: 1419 More actions · Answer 3

I'm using SSRS and a MSSQL 2005 back end. Never thought of trying to put it in the dataset. Seem there should be a way to do this with "RunningValue' or some other function.