locked
=Sum(ReportItems!FinalCost.Value, "group_1") problem in group footer RRS feed

  • Question

  • User-1863700931 posted

    Hi,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

    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<o:p></o:p>

    500               250<o:p></o:p>

    1000             634<o:p></o:p>

    700              500<o:p></o:p>

    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.<o:p></o:p>

     If anyone has any ideas on how to resolve this issue I will be greatly thankful,<o:p></o:p>

    Cheers,<o:p></o:p>

    Ben<o:p></o:p>

     

    Monday, December 18, 2006 4:22 PM

All replies

  • User313759536 posted

    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

    City<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

    RequestID<o:p></o:p> Request Amount<o:p></o:p> ClaimID<o:p></o:p> Claim Amount<o:p></o:p>
    <?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-com:office:smarttags" /><st1:City w:st="on"><st1:place w:st="on">El Monte</st1:place></st1:City><o:p></o:p> 791<o:p></o:p> 52,982.00<o:p></o:p> 2157<o:p></o:p> 41,143.75<o:p></o:p>
    <st1:City w:st="on"><st1:place w:st="on">El Monte</st1:place></st1:City><o:p></o:p> 3691<o:p></o:p> 11,838.00<o:p></o:p> 3140<o:p></o:p> 8,231.14<o:p></o:p>
    <st1:City w:st="on"><st1:place w:st="on">El Monte</st1:place></st1:City><o:p></o:p> 3691<o:p></o:p> 11,838.00<o:p></o:p> 3141<o:p></o:p> 990.00<o:p></o:p>
    <st1:City w:st="on"><st1:place w:st="on">El Monte</st1:place></st1:City><o:p></o:p> 3691<o:p></o:p> 11,838.00<o:p></o:p> 3142<o:p></o:p> 2,615.00<o:p></o:p>

    and this is what I get

    City<o:p></o:p>

    RequestID<o:p></o:p> Request Amount<o:p></o:p> ClaimID<o:p></o:p> Claim Amount<o:p></o:p>
    <st1:place w:st="on"><st1:City w:st="on">El Monte</st1:City></st1:place><o:p></o:p> <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> <o:p> </o:p>
    <o:p> </o:p> 791<o:p></o:p> 52,982.00<o:p></o:p> <o:p> </o:p> <o:p> </o:p>
    <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> 2157<o:p></o:p> 41,143.75<o:p></o:p>
    <o:p> </o:p> <o:p> </o:p> 52,982.00<o:p></o:p> <o:p> </o:p> 41,143.75<o:p></o:p>
    <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> <o:p> </o:p>
    <o:p> </o:p> 3691<o:p></o:p> 11,838.00<o:p></o:p> <o:p> </o:p> <o:p> </o:p>
    <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> 3140<o:p></o:p> 8,231.14<o:p></o:p>
    <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> 3141<o:p></o:p> 990.00<o:p></o:p>
    <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> 3142<o:p></o:p> 2,615.00<o:p></o:p>
    <o:p> </o:p> <o:p> </o:p> 35,514.00<o:p></o:p> <o:p> </o:p> 11,836.14<o:p></o:p>
    <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> <o:p> </o:p> <o:p> </o:p>
    <st1:City w:st="on"><st1:place w:st="on">El Monte</st1:place></st1:City><o:p></o:p> Request Totals<o:p></o:p> 88,496.00<o:p></o:p> Claim Totals<o:p></o:p> 52,979.89<o:p></o:p>

    So request totals is incorrect. should be 11,838.00 not 35,514.00

    Wednesday, December 19, 2007 7:28 PM
  • User1923657670 posted

    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.

    Thursday, December 20, 2007 8:32 AM
  • User313759536 posted
    Thanks I guess I will do what you suggested. It just seems that a reporting tool should sum by group without having to create separate sql scripts for every total. I might be having these issues because I’m using sql report server 2000. I hope this works on sql report server 2005. Thanks [:S]<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
    Thursday, December 20, 2007 2:20 PM
  • User1923657670 posted
    Huertj,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>I'm sorry, I'm did mean to suggest that it couldn't be summed in the table - without seeing the actual data and formulas, it's difficult to see if there's a problem there or if it's a short-coming with SSRS.  I was simply suggesting an alternative way to total the data and display it.  With complex formulas, one misplaced parenthesis can make all the difference.  Have you tried a simpler query and report to see if works - summing the way you are looking for?<o:p></o:p>Corwin<o:p></o:p><o:p> </o:p>

     

    Thursday, December 20, 2007 2:39 PM
  • User1923657670 posted

    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.

    Thursday, December 20, 2007 2:59 PM
  • User-372990967 posted

    try this one

    =Sum(DISTINCT(ReportItems!FinalCost.Value, "group_1"))

    Monday, December 24, 2007 4:03 PM
  • User-1566504901 posted

    Hi,

    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.

     

     

    Sunday, April 6, 2014 1:28 PM
  • User313531656 posted

    Hi Vinitha,

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

    Thanks.

    Monday, May 11, 2015 4:50 PM