Group Aggregates in SSRS - SQL Server Forums

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.

All Forums

Development Tools

Reporting Services Development

Group Aggregates in SSRS

Author

Topic

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2012-08-08 : 15:22:50

I've been trying to get the sum of the max of a group. I tried using the formula Sum(Max(Fields!Budget.Value,"GroupName")), but I receive the error:

"The value expression for the text box "BlahBlahBlah" specifies a scope that is not valid for a nested aggregate."

I'm not understanding why this is not possible. If I use the formula in the same text box Sum(Max(Fields!Budget.Value")), it returns the max of the group.

This is easily done in Crystal, yet it is a hassle in SSRS. I must be doing something wrong, but I'm not seeing it.

Any help is appreciated.
Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 15:36:34

what does sum(max()) mean? it means max itself right. max will always return single maximum value so whats point in applying sum() over it

so that expression itself doesnt make much sense in first place.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2012-08-08 : 15:56:04

Good point... so how would I get the sum of the max or even the first values of a group?

My query result set looks something like the fields below

Acct No Amount Budget
1234 50.00 1000.00
1234 30.00 1000.00
1234 20.00 1000.00
1111 55.00 2000.00
1111 45.00 2000.00

So I would like the end result to look something like this:

Acct No. Total Budget
1234 100.00 1000.00
1111 100.00 2000.00

My total column looks something like sum(fields!amount.value). I have a group on the account no.

I need the details for the drill down of the report, so I can't really sum the amounts up in the query.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 15:59:46

quote:
Originally posted by SunnyDee

Good point... so how would I get the sum of the max or even the first values of a group?

My query result set looks something like the fields below

Acct No

sorry i dont understand what do you mean by first values of group. do you have any criteria to determine?
or atleast show us some sample data and explain your max concept

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 16:17:19

quote:
Originally posted by SunnyDee

Good point... so how would I get the sum of the max or even the first values of a group?

My query result set looks something like the fields below

Acct No Amount Budget
1234 50.00 1000.00
1234 30.00 1000.00
1234 20.00 1000.00
1111 55.00 2000.00
1111 45.00 2000.00

So I would like the end result to look something like this:

Acct No. Total Budget
1234 100.00 1000.00
1111 100.00 2000.00

My total column looks something like sum(fields!amount.value). I have a group on the account no.

I need the details for the drill down of the report, so I can't really sum the amounts up in the query.

you need to bring data as detailed from table

in summary report add a grouping on Fields!AccountNo.value

and then apply SUM(Fields!Amount.value) for Total
and MAX(Fields!Budget.value) for Budget

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2012-08-08 : 16:57:58

Thank you for your response. I had done what you suggested, and sorry I wasn't clearer.

I need a total for the max of the budget items(which is the max of the account group, budget field)

Acct No. Total Budget
1234 100.00 1000.00
1111 100.00 2000.00
____________________
200.00 3000.00 Total

So in the above example, I can get the total of the amount for 200.00, but I can't get the total of the budget column using the sum function. When I use the sum function, I get the error message:

"The value expression for the text box "BlahBlahBlah" specifies a scope that is not valid for a nested aggregate."

Is this not possible in SSRS? Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-08 : 17:31:18

ok. for that one method is to do like this
instead of using SUM over max use like

=SUM(IIF(RowNumber("RowGroupName")=1,Fields!Budget.value,0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2012-08-09 : 08:27:28

Thanks... I get your idea, but now I receive this message...

The value expression for the textrun 'blahblahblah' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function or running value. Aggregate functions cannot be nested inside other aggregate functions.

Is there a limit to the number of aggregate functions that can be nested in SSRS? Any other ideas?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 10:20:47

ok. then add a column to your ssrs matrix/table make expression as RowNumber("RowGroupName"). make visibility as hidden. then in your current column use expression like

=SUM(IIF(ReportItems!FirstTextBoxName.value = 1,Fields!Budget.value,0))

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2012-08-09 : 15:55:27

Many thanks!!!

SunnyDee
Yak Posting Veteran

79 Posts

Posted - 2012-08-09 : 15:55:49

Many thanks!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-09 : 16:21:58

welcome
let me know how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources