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

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics