Hi: I am using sql server 2005 reporting services. I know rs does not support aggregate sum. This is just a regular report. Not a matrix. I have tried to research online. Different things like using runningvalue to achieve this. Putting the sum in footer. I just started to use reporting services. I had been using crystal report for a while. We are moving our reports from crystal to reporting services. So we can manage our reports within Sharepoint. Basically the brain is overloaded trying with different things. I hope some one can help. I have five columns. Project Type Project # Project Name Hours Productivity Type AB 123 Test 1 30 Productive AB 345 Test 2 40 Productive EC 222 TEST1 50 Productive CL 555 TEST 2 60 Productive SU 678 TEST3 10 Non-Productive TT 777 TEST4 15 Non-Productive Total 25 non-productive hours Total 180 productive hours Need to calcuate % of productive hours. 86.12% I need to group by Project Type and Productivity Type. I need to sum the hours for non-productive and productive hours. Calcuate the percentage. I am able to get the hours for both productive and non-productive hours. I need to calculate the % as well. Since these total hours are aggregate. I am not able to calcuate %. Any idea what I will need to do. Thanks for your assistance. kkmickey

Hi KKMickey, Here's what you can do: 1. Have some custom code to calculate a running total (snippet below): Dim prod_hours As Int Dim nonprod_hours As Int Public Function CalcSum (Val As Int , Type_of_Hour As Int) As Int Begin If Type_Of_Hour = "Productive" Then prod_hours = prod_hours + Val Return prod_hours Else nonprod_hours = nonprod_hours + Val Return nonprod_hours End If End 2. Have two hidden columns in your table. In your first hidden column (this one's for the Productive Hours), have an expression that calls your custom code as "=Code.CalcSum (Fields!Hours.Value, "Productive") " and in the second hidden column, have an expression as "=Code.CalcSum (Fields!Hours.Value, "NonProductive") " (this one's for the NonProductive Hours). 3. Finally, have a grand total for each of these columns. So you will have two text boxes in the row footer, lets say Textbox1 and Textbox2. 4. Calculate your percentage as "=ReportItems!Textbox1.Value / ReportItems!Textbox2.Value" Of course, there are few other approaches as well, but this is that, that comes to my mind first. Let me know if this helps you. Please mark correct answers :)

Hi KKMickey, Here's what you can do: 1. Have some custom code to calculate a running total (snippet below): Dim prod_hours As Int Dim nonprod_hours As Int Public Function CalcSum (Val As Int , Type_of_Hour As Int) As Int Begin If Type_Of_Hour = "Productive" Then prod_hours = prod_hours + Val Return prod_hours Else nonprod_hours = nonprod_hours + Val Return nonprod_hours End If End 2. Have two hidden columns in your table. In your first hidden column (this one's for the Productive Hours), have an expression that calls your custom code as "=Code.CalcSum (Fields!Hours.Value, "Productive") " and in the second hidden column, have an expression as "=Code.CalcSum (Fields!Hours.Value, "NonProductive") " (this one's for the NonProductive Hours). 3. Finally, have a grand total for each of these columns. So you will have two text boxes in the row footer, lets say Textbox1 and Textbox2. 4. Calculate your percentage as "=ReportItems!Textbox1.Value / ReportItems!Textbox2.Value" Of course, there are few other approaches as well, but this is that, that comes to my mind first. Let me know if this helps you. Please mark correct answers :)

Hi iceqb: Thank you so much. Reporting services is some what tricker to do when it comes to aggregate. It works perfectly. I was banging my head for the last few days. Thanks again. kkmick