adding up fields

My problem right now is I have three layers in my sheet.
Objective level
measure level
region level
facility level
The facility level is not a problem, just a Field!Facility.Value, it's a
straight pull from the db.
Region is fine, it's a sum(Field!Facility.Value)
Measure is a bit tricky, but it's fine - (Sum(Field!PassFail.Value)/Sum(Field!
TotalFacility))*Field!MeasureScore.Value
My problem now is that when you add all the measures, they should equal the
Objective score. So say I have 2 measures that work out to
.61
.24
my objective score should be .85
The problem is, I can't sum the measure scores because I can't Sum an
aggregate function.
So, I changed one of my views so that I could calculate a measure score in
the datafield and just pull it to the spreadsheet. The problem is, when I
Sum these fields, it works for adding up ALL of the objectives, not for each
objective. And when I put in more than once facility, it adds up the scores
for all of the objectives for all of the facilities.
My guess is that I am grouping incorrectly in the view. I'm pulling a bunch
of columns into my view and grouping on them. Is it possible to pull a
column into a select statement without grouping by it? I am grouping by
region, facility, objective and measure where I think I should just be
grouping by objective. When I try to just group by one column, I get errors
all over the place.
thanks for any help you can give.Okay, so I have four levels and not three:) Shows just how confused and
frustrated I am :)
ravensensei wrote:
>My problem right now is I have three layers in my sheet.
>Objective level
>measure level
>region level
>facility level
>The facility level is not a problem, just a Field!Facility.Value, it's a
>straight pull from the db.
>Region is fine, it's a sum(Field!Facility.Value)
>Measure is a bit tricky, but it's fine - (Sum(Field!PassFail.Value)/Sum(Field!
>TotalFacility))*Field!MeasureScore.Value
>My problem now is that when you add all the measures, they should equal the
>Objective score. So say I have 2 measures that work out to
>.61
>.24
>my objective score should be .85
>The problem is, I can't sum the measure scores because I can't Sum an
>aggregate function.
>So, I changed one of my views so that I could calculate a measure score in
>the datafield and just pull it to the spreadsheet. The problem is, when I
>Sum these fields, it works for adding up ALL of the objectives, not for each
>objective. And when I put in more than once facility, it adds up the scores
>for all of the objectives for all of the facilities.
>My guess is that I am grouping incorrectly in the view. I'm pulling a bunch
>of columns into my view and grouping on them. Is it possible to pull a
>column into a select statement without grouping by it? I am grouping by
>region, facility, objective and measure where I think I should just be
>grouping by objective. When I try to just group by one column, I get errors
>all over the place.
>thanks for any help you can give.