| Author |
 Topic  |
|
|
SunnyDee
Yak Posting Veteran
72 Posts |
 Posted - 08/08/2012 : 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
India
48097 Posts |
Posted - 08/08/2012 : 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
72 Posts |
Posted - 08/08/2012 : 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.
|
Edited by - SunnyDee on 08/08/2012 16:05:15 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48097 Posts |
Posted - 08/08/2012 : 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
India
48097 Posts |
Posted - 08/08/2012 : 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
72 Posts |
Posted - 08/08/2012 : 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
India
48097 Posts |
Posted - 08/08/2012 : 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
72 Posts |
Posted - 08/09/2012 : 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
India
48097 Posts |
Posted - 08/09/2012 : 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
72 Posts |
Posted - 08/09/2012 : 15:55:27
|
| Many thanks!!! |
|
|
|
SunnyDee
Yak Posting Veteran
72 Posts |
Posted - 08/09/2012 : 15:55:49
|
| Many thanks!!! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48097 Posts |
Posted - 08/09/2012 : 16:21:58
|
welcome
let me know how you got on!
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
Group Aggregates in SSRS
