Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
darkdusky
Aged Yak Warrior
591 Posts |
 Posted - 2009-01-07 : 05:54:23
|
| How do you create an agregate in a calculated field?E.gNew calculated field:=Avg(Fields!x.Value, "datasetname")In another thread (http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=117174) visakh helped me create average columns instead of subtotals, using:=IIF(Inscope("matrix1_Time"),Fields!x.Value,Avg(Fields!x.Value))I am trying to apply this to a column already containing an average, so I get an error saying aggregates cannot be nested.E.g.=IIF(Inscope("matrix1_Time"),Fields!x.Value,Avg(Avg(Fields!x.Value)))If I could place average in calculated field I might be able to refer to it instead, e.g:=IIF(Inscope("matrix1_Time"),Fields!x.Value,Avg(CalculatedField)))Or any other suggestions? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-07 : 10:30:43
|
| can you explain why you've nest aggregation? can you show some sample data to illustrate what you're trying to achieve? |
 |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2009-01-07 : 10:54:40
|
| It is a business requirement to have a calculated column which uses an average as part of the calculation:ColumnA1 =(Fields!x.Value / Fields!y.Value) * AVG(Fields!z.Value)This is to display the performance at a particular time factoring in a different average.This is currently done in Excel which I'm basing Report on.This column is shown for each hour. My problem is when I create Morning and Evening averages of the columns already containing averages.I am trying to get average into calculated field using code behind:Public Shared Function AvColumnZ() As Double AvColumnZ= AVG(Fields!z.Value)End Function But I am getting error "Name Avg not declared" - I guess I need to include a reference / namespace? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-01-07 : 12:02:31
|
quote:
Originally posted by darkdusky
It is a business requirement to have a calculated column which uses an average as part of the calculation:ColumnA1 =(Fields!x.Value / Fields!y.Value) * AVG(Fields!z.Value)This is to display the performance at a particular time factoring in a different average.This is currently done in Excel which I'm basing Report on.This column is shown for each hour. My problem is when I create Morning and Evening averages of the columns already containing averages.I am trying to get average into calculated field using code behind:Public Shared Function AvColumnZ() As Double AvColumnZ= AVG(Fields!z.Value)End Function But I am getting error "Name Avg not declared" - I guess I need to include a reference / namespace?
then why dont you bring average along with your resultset and use it in place of AVG(Fields!z.Value) |
|
|
|
|
|
|
Agregate in calculated field