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.g New 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) |
 |
|
|
|
|