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 FeeGoal 1 1000 100 2 1000 100 3 1000 100 4 2000 400 5 2000 400 My SSRS 2005 Report has: Group1 fields: CompanyID FeeGoal =Fields!CustomerNumber.Value =Fields!FeeGoal.Value The output when previewed looks like this: Company FeeGoal 1000 100 2000 400 Footer 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 field I 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

Hi, You may have two options: - use the "distinct" in your sql source - try SUM(Fields!FeeSchedule.Value,"yourtablegroup") Regards Ayzan

distinct won't help me in this case because the dataset is based on a set of records, Distinct won't make a difference. So you're saying that in an SSRS 2005 table, my footer can access field names in the same table? I kept getting errors when I tried to do that using ReportItems! but maybe your way will work ?!?!?! I'll try it

Unless I'm doing this wrong, there's no way to do what you're saying. When I try to preview this report, it errors out on my footer field: [rsInvalidAggregateScope] The Value expression for the textbox GrossGoal_gt has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing data region, or the name of a data set. Build complete -- 1 errors, 0 warnings My expression is: =SUM(Fields!GrossGoal1.Value,"CustomerNumber_Grp")

the field I'm trying to sum is in my table Group, so it's out of scope, my footer cannot do stuff like that on those fields.

I have similar problem. Can anybody help?

use query as shown below Select companyID,sum(FeeGoal)/count(*) fromTablegroup by companyID this returns CompanyID FeeGoal 1000 100 2000 400 and u can always use sum function to sum up the fee goal hope this helps

HEY GUYS, this is SSRS Expressions, not SQL!

i know that u r using ssrs. what i meant was, to use the query to generate the dataset and not in ssrs.

Yea, well, I am not coding this calculation in SQL, it is gonna be a nighmare, this cacluation contains about 10 others wrapped up that you don't see....SQL isn't set out for this code...so that option is not one for me here. also, I can't assume that's you you meant, all you showed me was SQL, you didn't state put that in a dataset...I can' assume everything you're thinking here, you need to be more specific. SSRS should be able to handle this, I'm not coding that calculation in SQL, that's just not viable in this report just for one darn sum....that's ridiculous. SQL can do calculations but the one I would have to do is outrageous for SQL to handle and would give me a migrane. That's why I want to use OO or SSRS functions since that is more suited to get this sum done but SSRS can't simply reference a group field from footer, that's ridiculous. I have to take in account commission % which I cannot just Sum up a % across the board for the GT. I have to take in consideration each value in the group and let those multiply but the commission %, then sum on that colum to get a true GT down the line.

And Azyan, why do you say referencing the group name as the scope works, it's not working and this is my whole problem with SSRS!!!

I'm having the same problem. First let me say that I'm an old hand with SQL (circa version 4 '94) but I'm quite new with report service and I find that examples are fairly lacking in bol. Report needed Drill down of employee time in a company: Division, person, date, time in, time out, task and finally task time. So there's 2 unequal groupI want to sum: OutminusIn and Task Duration. I know how to do it in SQL (Sum(distinct...)but I want to use the nifty group collapsing of SSRS, start by showing division summaries for user to drill down. It really makes then feeling empowered! I'm feeding a pretty dum flat-file likedataset to SSRS, that of course,repeatsDayIn and DayOut for each task in the day. In SSRS,I Have definedHierarchical groups: Department, Employee, Date. Tasks ends up in the detail row When I try to sum things up: Bam! if there's 5 task on a 12 hours day, Iget the correct task time butthat day ends up with 60 hours. Now I tried using sum and running values with scope.Doesn't work. It's a bit frustrating. There's a countDistinct function sowhere's the sum distinct? SowhatI would like to ask here is either a working sum distinctfunction or a better way to achieve the desired drill down effect - bothvia SSRS. The T-SQL way to do it I know very well. Thank you in advance

Well, I've decided to resolve this via t-sql after all. Problem can also be stated as that there is 2 different ranges in main dataset. So I added a subquery that counts task per day and I'm dividing daily duration by that number. I end up with only one range and it just become a matter of putting my sums in the correct groups.

Hi, I am not sure will this resolve your problem, what I would try is to use list instead of table, then create a function in custom code to take in the reportItems!FeeGoal.value (assumed textbox name is FeeGoal) and do a manual sum up to a locally declared variable. Then in the show sum place u just call the variable that stored the sum up value. Below is the code to put in the Custom Code part: Public sum_of_feegoal As Integer Public Function SumUp(ByVal Value As Integer) sum_of_feegoal = sum_of_feegoal + Value End Function then add a textbox next to your Fee Goal textbox that will have the following code: =Code.SumUp(ReportItems!FeeGoal.Value) then to output the sum, use the following code: =Code.sum_of_feegoal Hope this help. Daren

cool, let me try the list...thanks!

Dear All, I'm happyif this solves the issue. I got this Issue and Analysed while displaying the result in Grid For Example I have three tables 1. Employee 2.Monthly Salary 3.Other Incomes 1-2 is having a 1 to many and the same way 1-3 also has 1 to many relation with 1. ******Employee****** Emp No Name 1 X ******Monthly Salary ******* Emp No Salary 1 6000 *******Other Income******* Emp NoSource Salary 1Share trading 500 2 Brokerage 300 So In my report if I use sum of Total Monthly Salary, Total sum of other Income it leads to Emp No Monthly Salary Other Income 1 12000 800 this is because of 2 different Incomes for the employee. So I should not have used Total Monthly Salary in my report instead I just should have used the column Monthly Salary itself. This solved my problem and hope this helps you Regards, Kaladharan.M

What worked for me was just using COUNT. =Count(Fields!SerialNumber) Although the field was in a group, it would not take the group as the Scope parameter. Using the single parameter version of Count, the report was generated in exactly the way I wanted it to be, but of course not how I expected it.

Hi darentan, This code suggestion of yours is really cool. It worked. However, I am not able to access this public property value into another group's footer. Do you know of any way with which I could be able to display the output in an outer group's footer with all the rest of the totals? Please let me know. Thank you, NewToSSRS

Thanks Daren!!! Really very good code!! I was block!! Thanks very much!! Can you please give emailid. Thanks Nilesh deshmukh_nil123@hotmail.com

Crystal XI does not do a distinct sum either. I've been forced to do a sum on another column and carry that number throughout the returned dataset. Such that: TransID CompanyID FeeGoal TotalAmount 1 1000100 21000100 31000100 42000400 5 2000400 Try this approach? declare @50cent money set @50cent=( selectsum(FeeGoal) as FeeGoal --add another ID here to group by another dimension you wish to sum with from Company c inner join Forecasting f on f.companyID=c.companyID inner join TransactionPayment p on p.companyID=c.companyID declare @results table ( TransID int, --or KEY_TYPE/uniqueidentifier... it's not good practice to make an ID an int, even if it is numeric CompanyID int, --See above FeeGoal money, TotalAmount money ) insert into @results (TransID, CompanyID, FeeGoal, TotalAmount) select p.TransID, c.CompanyID, f.FeeGoal,@50cent from Company c inner join Forecasting f on f.companyID=c.companyID inner join TransactionPayment p on p.companyID=c.companyID select * from @results

With existing Reporting Services you cannot do a sum the way you suggest in the report. You can accomplish it with at relatively modest SQL query change. You'd need to return a row set that looks like:TransId MinTransId CompanyID FeeGoal1 110001002 110001003 11000100442000400542000400The MinTransId would be returned either as a self join or a sub select... something like:select x.TransId, (select min(x1.TransId) from X x1 where x1.CompanyID=x.CompanyID), x.CompanyID, x.FeeGoal From X xThen in the report do your aggregate like:SUM(IIF(MinTransId.Value = TransId.Value, FeeGoal.Value, 0) Another approach, which is possible with SQL 2008 Reporting Services is described in the following blog post. However, I would think from a maintainability viewpoint, the SQL query change would be better.http://blogs.msdn.com/robertbruckner/archive/2008/07/20/Using-group-variables-in-reporting-services-2008-for-custom-aggregation.aspxHope that helps,-LukaszThis posting is provided "AS IS" with no warranties, and confers no rights.

Hi, I need to generate a SSRS 2005 report like bellow: OrderNumber MultiInvoice SalesAmt TaxAmt TotalAmt 301256 1 302 20 322 369856 501 10 511 359826 456 1 457 785412 2 725 25 750 . . totalRow=4 totalRow=2 Sum=1984 Sum=56 Sum=2040 ----------------------->Footer --I added textbox in ther footer of that report table, and try to add expression for each, like 1>OrderNumber : =CountRows() 2>MultiInvoice : =Count(column name) //but it is return no of rows, where it should be only 2. How can do that? 3>SalesAmt : = SUM(Fields!SalesAmt.Value) //It is not returning actual summation of all rows for SalesAmt column. How can I do that? - - - Dipen Bhadra

Hi. I had the same problem in SSRS 2008 R2. However I found out how to solve my issue if you haven't already. I left my query as is, with a similar problem to yours. I then in the table in my report, for the cell I wanted to take the sum of the FeeGoal (in your case), but only once for each MinTransId, used the COUNTDISTINCT() function which is under the Common Functions> Aggregate section in the expression window. This worked a treat. Just did sum(Field)/countdistinct(Field). Hope that helps

you can do it in SQL query level, add a new field (Let's call it FeeGoal_Sum) only show real FeaGoal value in the first Trans, show 0 in the rest. you can do it by using Row_Number() function. the New result will be: · TransID CompanyID FeeGoal FeeGoal_Sum 1 1000 100 100 2 1000 100 0 3 1000 100 0 4 2000 400 400 5 2000 400 0 In your footer, instead of using =Sum(Fields!FeeGoal.Value), use =Sum(Fields!FeeGoal_Sum.Value). Hope it can help. Kevin