none
SSRS 2008 fixed assets reports created, have total is Avg , sub total is Sum, some sub total not adding up correctly

    Question

  • I have created Fixed Assets Report, and  I have 3 group of total,  1st group just Avg total  because Acquistion cost is adding duplicate cost if I use Sum, so I used Avg function,

    and I group and Fixed asset group as Sub total , this is not total up correctly,

    when I use the Sum function from group 1 avg its not sum up correctly,  what can I do to that Subtotal add up correctly, its adding up all the  numbers from Acquisition price column, can some one please help me.

    expression I am using is

    =Sum(Fields!UNITCOST.Value)    Subtotal 

    )

    Wednesday, August 14, 2013 2:04 PM

All replies

  • can anyone help me with this issue,  I have tried every thing,  2nd group total to pick up the amount only the one only in BOLD , but its picking all 3 rows of ist assets and 3 rows of 2nd asstes when I add the exp in total,  what can I do to fix tho=is,  please Help. 161850+6722= 168572 total to shows this amount, I have to finish this report, thanks. 

    Assetgroup AssetId Name Dept Qty UOM         Transtype                  Acquisition         Price
                   
    Building 00000002_011 Building   1.00 ea 1 161,850.00
    Building 00000002_011 Building   1.00 ea 3 161,850.00
        Total   1.00     161,850.00
                   
    Building 00000107_011 Roof 1410 1.00 ea 1 6,722.00
    Building 00000107_011 Roof 1410 1.00 ea 3 6,722.00
        Total   1.00     6,722.00

    Wednesday, August 14, 2013 6:32 PM
  • please some one help me on above issue,  I really appreciate it,  I am sure for you guys its very simple solution, for me I have tried everything and search every where, have not found answer, can any one out there tell me how to put expression so it will add only total row not each row just want to sum +161,850+6722= 168,572 like I have other group,  what can I do.,  the expr I using is below that's adding up each row of acquisition price is actual (unit cost)colum, I just changed the name.  of 6 row, I have to finish this report for our auditors , please help me with this issue,  you guys have always helped.  unless my question is not clear, please let me know.

    Thanks  

    =Sum(Fields!UNITCOST.Value)   

    Thursday, August 15, 2013 12:07 PM
  • Hi Bitia,

    According to your description, you had got the average of Group1. And you want get the Sum of this average values.

    You can try to use custom code to achieve the same result you want . You can refer to the code below:

    Dim public SumAvg as Integer
    Dim pubilc Sum as Integer
    Public Function GetAvg (ByVal Avg AS Integer) AS Integer
        SumAvg = SumAvg+ Avg
        Return Avg
    End Function 
    Public Function Sum()                   
         Return SumAvg
    End Function

    In the report design surface, using the following expression in Group1 to get the average of it:
    =code.GetAvg(Avg(Fields!Acquisition_Price.Value))
    Add a total row of the group1, and then  using expression below to get the Sum value:
    =code.Sum()

    There is an article about Using Custom Code References in Expressions, you can refer to it:
    http://msdn.microsoft.com/en-us/library/ms155798(v=sql.100).aspx

    Hope this helpful. If there are any misunderstanding, please let me know.

    Regards,
    Alisa Tang

    Friday, August 16, 2013 11:07 AM
  • Hi Alia

    thanks  for reply,  I tried with this custom code, getting the error message,  I donot know much about custom code.

    =Code.GetAvg(Avg(Fields!UNITCOST.Value))  this code I entered in Group 1  to get avg   unit cost column is actual name of column =code.GetAvg(Avg(Fields!Acquisition_Price.Value))Acquisition column I changed the name, so I am not sure you have  Acquisition_Price on code  has any thing to do with it. since the actual name is unit cost, should I change this also,  I replace the acquisition with unitcost, and 2 code I added on total row I Created It.  I am getting Error Message

    There is an error on line 8 of Custom code [BC30260] Sum is already declared as Public Dim sum as Integer in the class

    what I am doing wrong, I really appreciate your help.  I really need help to fix this report.

    see attached the screen shot

    Friday, August 16, 2013 5:18 PM
  • Hi Alisa

    I fix the Custom code, its working,  only question I have

    I have 3 Level group Total 

    1st  avg   I got that one

    2nd level  Group of all the assetgroup   that one did not work

    3rd level  Grand total  when I used the code      =Code.Sum()    on Total  row  and when I look in SSRS screen it look like group total, some group are missing, but same time when I export this file to excel same total becomes as Grand Total,  I donot under stand this,  can you please help me understand this,  is this possible ?  and when I preview the report in SSRS its missing some groups.  because I export to excel so I can screen shot the group, that's the time I notice this.

    please help me.

    Thanks

    Friday, August 16, 2013 7:38 PM
  • HI ALisa

    can you please help me this

    Custom code are ok, 

    3rd level  Grand total  when I used the code      =Code.Sum()    on Total  rows  and when I look in SSRS screen it look like group total, some group are missing, but same time when I export this file to excel same total becomes Grand total amount,  Is this Possible In SSRS,  I donot understand this,  I am so confused about this ,   

    Wednesday, August 21, 2013 6:39 PM
  • Hi Bitia,

    Thank you for your reply. Very Sorry for my delay.

    In order to solve the problem more efficiently I need to clarify some information. Could you please post a more clearly screenshot and the dataset with sample data about your report? I need to learn more about your report. It is benefit for us to do further analysis.
    E-mail:  sqltnsp@microsoft.com
     
    Is it possible that you can provide more specific details?

    Best regards,
    Alisa Tang

    Thursday, August 22, 2013 10:43 AM