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.
| Author |
Topic |
|
dba123
Yak Posting Veteran
90 Posts |
 Posted - 2006-02-08 : 14:57:50
|
| I don't know how best to explain this but will try.In my SSRS 2005 report, i have one group in my table. In that group, I have a field that for each company record in my dataset, this field value is repeated for each record. This is a transactions table so you'd expect several records in the dataset for each company..that's not the problem.example data from dataset:TransID CompanyID FeeGoal1 1000 1002 1000 1003 1000 1004 2000 4005 2000 400My SSRS 2005 Report has:Group1 fields:CompanyID FeeGoal=Fields!CustomerNumber.Value =Fields!FeeGoal.ValueThe output when previewed looks like this:Company FeeGoal1000 1002000 400Footer 209409730.83 (totals up all feegoals! not just unique instances!)I have a footer, and this is where the problem comes in. I am not able to sum 100 + 400 because if I do a SUM(=Fields!FeeGoal.Value) It doesn't just sume up 100 + 400 but rather (100 + 100 + 100) + (400 + 400)I can't find a way to sum up basically the top values for FeeGoal which is really what the Group Field is doing since I get 100 and 400 in my group field for feeGoal. I should end up with a total of 500 for my footer total for Fee Goal but not sure how to get this to work in this unique situation. FYI: FeeGoal is an input from an ASP.NET form to allow managers to update that one column in my report so that other calculations can rely on it in my group fields. Once the user finishes, I run a stored proc to insert that fee goal in every customer transaction record / feegoal fieldI guess is there a way to do some sort of Distinct SUM in an expression? I also tried:=SUM((Max(Fields!FeeGoal_AZ.Value)) / Fields!FeeSchedule.Value) * 100)but you can't have an aggregate function like this, I get this error:[rsAggregateofAggregate] The Value expression for the textbox 'GrossGoal_gt' 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.Build complete -- 1 errors, 0 warnings |
|
|
jhermiz
3564 Posts |
Posted - 2006-02-08 : 17:11:04
|
I answered your question over here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61324No need to repost.The solution I gave works pretty well, do you need help understanding it ?
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
 |
|
|
|
|
|
Sum problem in Footer