How to aggregate a column that was itself an aggregate

I have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.

Below is a message:
The value expression for the textbox 'textbox13' 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.

Need help.
Thanks, MickB

Dude,

did you get any luck from anyone with this? It seems that this major piece of functionality has been omitted from Reporting Services? Or am I just being thick? It's certainly not as simple and straight forward as Crystal, if it can be done at all. Will M$ be adding this at some point in the future? Can anyone shed any light on either my lack of knowledge or if M$ is going to implement it?
Cheers,
Dunk

|||

Ditto. And runningvalue in a chart would be nice too.

|||Have you tried to create a new field that contains the RunningValue (let's call it NewField) and instead of make a Sum on the Running value you make a sum on the new field (=Sum(Fields!NewField.Value, "YourDataSet") )?
It works, as far as I can tell.|||

Is this in Report Designer?

I believe that the aggregate can show different values depending on the grouping and where on the table you put the column.

For Example, If you create a new grouping row with all of your monthly data in it and a monthly total then use the same aggregate in the row below it should give you a total of all of the monthly rows.

Don' know if i've explained this very well, it sounds better in my head!!

Hope this helps

|||Harley's right. You can NOT embed aggregate functions within each other, nor can you use a calculated field as an expression in an aggregation function. What you can do is specify at which level you want to apply the aggregate function.

For instance, if you want to add up all the sales in a grouping called salesDay, you'd do this :

RunningValue( Fields!Sales.value, sum, "salesDay" )

or salesMonth, same thing

RunningValue( Fields!Sales.value, sum, "salesMonth" )

If you want to add up all the sales for the current grouping ( if you're in the footer, for instance), you can leave the scope parameter blank:

RunningValue( Fields!Sales.value, sum )

If you place that code in the footer for your month grouping and in your table footer, i think that'll do what you need.|||

The problem I have is that one group nested inside another, and I do a CountDistinct on a column for the innermost group. This value represents a number of events that my report is supposed to track.

If I do the same CountDistinct on the outermost group, the CountDistinct is different because values that were unique within two groups are not unique within only the outermost group.

What I need to do is a CountDistinct on the innermost group and a Sum of the CountDistinct values I came up with -- I would show this in lieu of the CountDistinct for the outermost group.

But, alas, I don't see a way to do it. I'm open to suggestions if anyone has one.

|||

So are we saying that this in the grouping should or shouldn't work?

=Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value)

It works fine however try something a little more complex and we get the same error

=RIGHT(("00"&(INT(SUM(CInt((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600))),2) & ":" & RIGHT(("00" & (FLOOR((((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600)-INT((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/3600)))*60)))),2) & ":" & RIGHT("00" &(ROUND(((((SUM(Cint((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/60)-INT((SUM(CINT((Sum(Fields!AVG_Aban_Time.Value)/Sum(Fields!Aban_Calls.Value))))/60))))*60))),2)

Any ideas?