aggregates within aggregates

Here is the code I have:

=Sum(IIF(sum( Fields!Total_Amount.Value, "Collat_Acct_Group2") < 0, 1,0))

I am trying to do a count but only if the sum of a value is greater/less than 0.

The error I get is:

The value expression for the textbox 'textbox146' 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.

Please help .

Thanks

Elias

I guess you want to count the number of groups which have sum of total amount > 0.

In the code (Report -> Report Properties -> Code tab), have a shared variable called count and initialize it to 0. Then have a function called IncrementCount which when called will increment count by 1.

In your Collat_Acct_Group2 header or footer, append the following expression to the any of the textboxes:

[old expression] & IIf(Sum(Fields!Total_Amount.Value, "Collat_Acct_Group2") > 0, Code.IncrementCount(), "")

and use "Code.count" in any field expression to get the count of all groups with sum of total amount > 0

Shyam

|||

Shyam,

Thanks, for the answer, but I have never written any code in RS if it would not be too much trouble could you please show me an example.

Thanks again,

Elias

|||

Sorry for the delay in my response

Your code will look something like this in VB.Net:

Private Shared count as Integer

count=0

Public Function IncrementCount() As String

count = count +1

IncrementCount = CStr(count)

End Function

Say one of the textboxes (or a column) in your table header has the following expression:

Fields!SomeField.Value

Now change the expression to:

Fields!SomeField.Value & IIf(Sum(Fields!Total_Amount.Value, "Collat_Acct_Group2") > 0, Code.IncrementCount(), "")

Then use Code.count in any of your field expressions to get the number of groups with total amount > 0.

Shyam