SSRS 2008 fixed assets reports created, have total is Avg , sub total is Sum, some sub total not adding up correctly
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
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
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.
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:
Add a total row of the group1, and then using expression below to get the Sum value:
There is an article about Using Custom Code References in Expressions, you can refer to it:
Hope this helpful. If there are any misunderstanding, please let me know.
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
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.
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 ,
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.
Is it possible that you can provide more specific details?