Last post May 11, 2015 04:50 PM by Srav2016
Dec 18, 2006 04:22 PM|ben22|LINK
Im currently writing a report in SSRS which requires to take data in the fields, manipulate and perform functions on it then display it within its own textbox. For example the field Cost is manipulated so that
a column will show the cost depreciated after 5 years.
Cost Final Cost
My problem is that within each group (as the data is grouped by its type) I need to have a sum of the Final Cost data. As this is NOT a field I cannot use =Sum(Fields!FinalCost.Value, "group_1") but rather
I need to use =Sum(ReportItems!FinalCost.Value, "group_1"). I know that SSRS does not allow this, but after trying to find an answer going extensively through google and many forums, I am not able to find a solution on how to sum up the Final Cost column.
If anyone has any ideas on how to resolve this issue I will be greatly thankful,
SSRS Reporting Services bookmark pdf bookmarks
Dec 19, 2007 07:28 PM|huertj|LINK
Im having a group total error as well but Im having them on the group by fields. For example I have a dataset that is by city, request, claim. So I placed the claim information in the detail row which is claimID, Claim Amount and I am able to find the sums
of my claims by placing the claim Amount field in the group row for Request and I also copy the field claim amount to the city group row. If you copy the Final Cost field to the group this should work. Im not quite sure this is what you are asking. But by
the way do you now a solution for my problem? This is what I have
and this is what I get
So request totals is incorrect. should be 11,838.00 not 35,514.00
Dec 20, 2007 08:32 AM|Corwin|LINK
I'm not sure if I completely understand the problem, but can you determine the Total value using another query? If so, you could add an additional dataset for that summed total and display it in a separate textbox:
Query1 Select Cost From Table Grouped By Type
Query2 Select Cost From Table (no group by)
Display query1 (and derived final cost) in the table
Display query2 result in separate textbox
If you have many groups you need to total separately, you could create a query for each.
Hope this helps.
Dec 20, 2007 02:20 PM|huertj|LINK
Dec 20, 2007 02:39 PM|Corwin|LINK
Dec 20, 2007 02:59 PM|Corwin|LINK
I see, you're getting 35,514 because you have three records in that group (11,838*3=35,514) - you probably have an error in the formula. But you could sum then divide by the count to get the answer.
Dec 24, 2007 04:03 PM|Probleminfinity|LINK
try this one
Apr 06, 2014 01:28 PM|Vinitha Ramalingam|LINK
I am facing the same problem (Sum Total in a group) as mentioned above and i tried syntax Sum(DISTINCT(ReportItems!FinalCost.Value, "group_1")) but in SSRS there is no such Distinct function exists.
Can you please kindly suggest some work around.
Thanks in advance.
May 11, 2015 04:50 PM|Srav2016|LINK
First check the grouping of the tablix in your report. There might be a case where you are displaying just 3 rows based on your grouping, but when you run your stored procedure you might see more rows for the same scenario.
Example: Grouping the tablix based on product ID will give you 3 rows in your report (i.e. product id 1,2,3). But when you perform operations on the same data, the report will consider all the rows returned by the stored procedure. In this case the amount
related to product id 2 will be calculated twice.
Product Sub product PricePerProduct
1 1 100
2 1 200
2 2 200
3 1 300
Here you will be getting the sum of (Product 1 + Product 2 + Product 2 + Product 3) = 800 (Expected value would be 600).