SUM problem in SSRS 2005 report

SUM problem in SSRS 2005 report

• Chintan Soni

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

• Shawn Burke - MS

  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!!!
  
  

• Bhavin Vyas

  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.
  
  

• nards_ocampo

  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 group I want to sum: Out minus In 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 like dataset to SSRS, that of course, repeats DayIn and DayOut for each task in the day. In SSRS,I Have defined Hierarchical 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, I get the correct task time but that 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 so where's the sum distinct

  So what I would like to ask here is either a working sum distinct function or a better way to achieve the desired drill down effect - both via SSRS.

  The T-SQL way to do it I know very well.

  Thank you in advance

  
  

• Rickahooy

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

• John Calcote

  use query as shown below

  Select companyID,sum(FeeGoal)/count(*) from Table
  group 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

  
  
  

• Macka007007

  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

  
  
  

• Teo

  I have similar problem. Can anybody help
  
  

• Comsin

  HEY GUYS, this is SSRS Expressions, not SQL!

  
  
  

• walt

  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
  
  

• Nicolai G. Andersen

  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.

  
  
  

• Parky

  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.

  
  

• dpomerantz

  i know that u r using ssrs.

  what i meant was, to use the query to generate the dataset and not in ssrs.

  
  
  

• jbothwel

  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")

  
  
  
• SUM problem in SSRS 2005 report