SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 Reporting Services Development
 Group Aggregates in SSRS
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 08/08/2012 :  15:22:50  Show Profile  Reply with Quote
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
52309 Posts

Posted - 08/08/2012 :  15:36:34  Show Profile  Reply with Quote
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/

Go to Top of Page

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 08/08/2012 :  15:56:04  Show Profile  Reply with Quote
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
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/08/2012 :  15:59:46  Show Profile  Reply with Quote
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/

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/08/2012 :  16:17:19  Show Profile  Reply with Quote
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/

Go to Top of Page

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 08/08/2012 :  16:57:58  Show Profile  Reply with Quote
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!




Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/08/2012 :  17:31:18  Show Profile  Reply with Quote
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/

Go to Top of Page

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 08/09/2012 :  08:27:28  Show Profile  Reply with Quote
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?

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/09/2012 :  10:20:47  Show Profile  Reply with Quote
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/

Go to Top of Page

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 08/09/2012 :  15:55:27  Show Profile  Reply with Quote
Many thanks!!!
Go to Top of Page

SunnyDee
Yak Posting Veteran

76 Posts

Posted - 08/09/2012 :  15:55:49  Show Profile  Reply with Quote
Many thanks!!!
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 08/09/2012 :  16:21:58  Show Profile  Reply with Quote
welcome
let me know how you got on!

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000