Aggregating values in Tablix
Hi everybody,
I have a tablix with 3 column groups (ObjectGroup, ErrorNameGroup, PartGroup). Well, when i toggle the lowest group (PartGroup), I want to count average of numbers. That is not a problem, because I could define the expression like
=Avg(Fields!ErrorValue.Value)
Now I want to toggle/hide the second group (ErrorNameGroup), but want just simple sum. Well, expression like
=Sum(Avg(Fields!ErrorValue.Value)
is not allowed. Is there any other possibe way to show sum of averages?
To better illustrate the case ...
O1 O2
E1 E2 E3 E1 E2 E3
P1 P2 P3 P4 P1 P2 P3 P4 P1 P2 P3 P4 P1 P2 P1 P2 P1 P2
Day1 1 1 1 1 8 9 9 10 -8 -8 -4 -8 3 1 3 5 -2 2
Day2 2 2 2 2 -4 -2 -3 -3 7 7 11 7 5 3 -1 -5 6 6
... aggregate Ps (Avg)
O1 O2
E1 E2 E3 E1 E2 E3
Day1 1 9 -7 2 4 0
Day2 2 -3 8 4 -3 6
... aggregate Es (Sum)
O1 O2
Day1 3 6
Day2 7 7
Thanks for any comment.
February 15th, 2011 8:21am
Each function in our expression has a scope over which it applies. By default, that will be the whole range covered by the group, but it doesn't have to be. Your first expression is equivalent to:
=Avg(Fields!ErrorVaue.Value,"PartGroup")
by default, since you're grouping by PartGroup . So, in you second expression you should be able to use:
=Sum(Avg(Fields!ErrorVaue.Value,"PartGroup"))
i.e. take the average of ErrorValue for each PartGroup , and then add up the average for each
PartGroup .If this post, or another, answers your question, please click the 'Mark as answer' button so others with the same problem can find a solution more easily.
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2011 9:10am
Well,
looks great, but do not work. As I said - can't have a nested aggregate functions.
[rsAggregateofAggregate] The Value expression for the textrun 'Textbox82.Paragraphs[0].TextRuns[0]' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions
cannot be nested inside other aggregate functions.
February 15th, 2011 9:24am
Sorry, I thought I'd done that before and it had worked. Just tested it and got the same error though. In that case, I'd suggest you'd have to create the aggregates in an SQL query.If this post, or another, answers your question, please click the 'Mark as answer' button so others with the same problem can find a solution more easily.
Free Windows Admin Tool Kit Click here and download it now
February 15th, 2011 9:51am
Hi lu.mrk,
Which version of Reporting Services you are using? We can only create expressions that calculate an aggregate of an aggregate like =Sum(Avg(Fields!ErrorValue.Value) in Reporting
Services 2008 R2.
Based on your description and the example you posted, if hide the second group (ErrorNameGroup), you want to display the simple sum. Do you mean you calculate the sum by add each
average of Error Name Groups, for example, in the Object Group O1, the sum 3 is the sum of 1+9+(-7)? If so, we need to calculate the sum by using custom code.
Thanks,
Tony ChainTony Chain [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
February 24th, 2011 3:30am