Sum Column gives error Aggregate functions can be used only on report items contained in page headers and footers
Hi There Can you please try this =sum(CDec(iif(((DateSerial(Year(Parameters!PreviousMonth.Value), Month(Parameters!PreviousMonth.Value) + 2, 0)) <= Fields!Depreciation_Ending_Date.Value), ROUND((((((Sum(IIF(Fields!FA_Posting_Type.Value = "0", CDec(Fields!Sum_Amount.Value), CDec(0)))) + (Sum(IIF(Fields!FA_Posting_Type.Value = "1", CDec(Fields!Sum_Amount.Value), CDec(0))))) - 0 + (Sum(IIF(Fields!FA_Posting_Type.Value = "7", CDec(Fields!Sum_Amount.Value), CDec(0))))) * 30) / ( ((Fields!No__of_Depreciation_Years.Value * 360) - (iif(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value))))>Fields!No__of_Depreciation_Years.Value*360,Fields!No__of_Depreciation_Years.Value*360,(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value)))))))) - (30 * 0))), 2), 0)),"YourScope") =sum(CDec(iif(((DateSerial(Year(Parameters!PreviousMonth.Value), Month(Parameters!PreviousMonth.Value) + 2, 0)) <= Fields!Depreciation_Ending_Date.Value), ROUND((((((Sum(IIF(Fields!FA_Posting_Type.Value = "0", CDec(Fields!Sum_Amount.Value), CDec(0)))) + (Sum(IIF(Fields!FA_Posting_Type.Value = "1", CDec(Fields!Sum_Amount.Value), CDec(0))))) - 0 + (Sum(IIF(Fields!FA_Posting_Type.Value = "7", CDec(Fields!Sum_Amount.Value), CDec(0))))) * 30) / ( ((Fields!No__of_Depreciation_Years.Value * 360) - (iif(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value))))>Fields!No__of_Depreciation_Years.Value*360,Fields!No__of_Depreciation_Years.Value*360,(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value)))))))) - (30 * 0))), 2), 0)),"YourScope") Where YourScope is the scope where you would like to take the sum if it is group just put your group name otherwise please put your dataset name I hope this will help Many thanks Syed Qazafi Anjum
July 13th, 2012 10:52am

That column is not in a group so I used the Dataset name. I received this error. The Value expression for the text box Textbox290 has an inner aggregate in an outer aggregate that specifies a dataset scope. An aggregate that specifies a dataset scope cannot contain other aggregates.
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 11:30am

I tried the =RunningValue(Fields!Col2.Value,Sum,"Dataset1") and received this error The Value expression for the text box Textbox1 refers to the field Month1Date. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope. Letters in the names of fields must use the correct case. I added the total but it only created a blank row
July 13th, 2012 11:36am

Hi There Hi There Thanks for your posting again. you can use custom code for this . Please do the following Please go to your report properties and then go to code tab and please create the following function Public Total_lookup_Sum As Integer = 0 Public Function Lookup_Sum(ByVal value As Integer) As Integer Total_lookup_Sum = Total_lookup_Sum + value Return Total_lookup_Sum End Function Public Total_lookup_Sum As Integer = 0 Public Function Lookup_Sum(ByVal value As Integer) As Integer Total_lookup_Sum = Total_lookup_Sum + value Return Total_lookup_Sum End Function create another textbox next to where you are calculating these depreciation expression and put code like this, paste the following expression for the value =Code.Lookup_Sum(Reportitems!textbox1.value) where Reportitems!textbox2 and are report items which you would used for depreciation now put this expression where you would like to get the sum =Code.Total_lookup_Sum Many thanks Syed Qazafi Anjum
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 11:47am

This works, but now I have two columns in my report when I only want one. How do I hide it? If I set the visibility to Hide then it wont add the numbers.
July 13th, 2012 1:02pm

So, in simple words, just for example , you want SUM of SUMS? For eg. Col1 Col2 col3 12 13 23 10 15 7 ---------------- 22 27 30 Col4 Col5 Col6 20 30 40 So, what you want is, 22+20 = 42, 27+30=57 and 30+40=70 If taht is what you want, you can use a running expressions that is available in SSRS, just replace your expression with something like this: =RunningValue(Fields!FieldName.Value,Sum,"Dataset1")
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 3:11pm

Col1 Col2 Col3 Col4 Asset1 41 40 41 Asset2 10 11 10 --------------------------------- Total 51 51 51 I tried =RunningValue(ReportItems!Month1.Value,Sum,"Dataset1") and got the error "The Value expression for the textrun 'Textbox11.Paragraphs[0].TextRuns[0]' uses an aggregate function on a report item. Aggregate functions can be used only on report items contained in page headers and footers." So I tried moving it to the Page footer and got "The Value expression for the textrun Textbox11.Paragraphs[0].TextRuns[0] includes the aggregate function RunningValue. RunningValue cannot be used in page headers or footers."
July 13th, 2012 4:28pm

The red circle is what is giving me my problem In this report I am calculating depreciation for the next 60 months. The columns after Book value will give me the last day of the next month for 60 months. (=DateSerial(Year(Parameters!PreviousMonth.Value), Month(Parameters!PreviousMonth.Value) + 2, 0)) The <<expr>> under that is calculating depreciation for each asset =CDec(iif(((DateSerial(Year(Parameters!PreviousMonth.Value), Month(Parameters!PreviousMonth.Value) + 2, 0)) <= Fields!Depreciation_Ending_Date.Value), ROUND((((((Sum(IIF(Fields!FA_Posting_Type.Value = "0", CDec(Fields!Sum_Amount.Value), CDec(0)))) + (Sum(IIF(Fields!FA_Posting_Type.Value = "1", CDec(Fields!Sum_Amount.Value), CDec(0))))) - 0 + (Sum(IIF(Fields!FA_Posting_Type.Value = "7", CDec(Fields!Sum_Amount.Value), CDec(0))))) * 30) / ( ((Fields!No__of_Depreciation_Years.Value * 360) - (iif(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value))))>Fields!No__of_Depreciation_Years.Value*360,Fields!No__of_Depreciation_Years.Value*360,(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value)))))))) - (30 * 0))), 2), 0)) The last <<expr>> circled in red is where I am trying to total all the depreciation from each asset that is generated. That is when I get the error "Sum Column gives error Aggregate functions can be used only on report items contained in page headers and footers" =Sum(ReportItems!Month1.Value) I moved it to the footer but then it only gives me an amount of the assets shown on page. I need a grand total of all assets Thanks for your help
Free Windows Admin Tool Kit Click here and download it now
July 13th, 2012 5:38pm

Hi There Thanks for your posting. Yes that is right you cannot use report item collection for any aggregation inside tablix control. Can you please try this? =sum(CDec(iif(((DateSerial(Year(Parameters!PreviousMonth.Value), Month(Parameters!PreviousMonth.Value) + 2, 0)) <= Fields!Depreciation_Ending_Date.Value), ROUND((((((Sum(IIF(Fields!FA_Posting_Type.Value = "0", CDec(Fields!Sum_Amount.Value), CDec(0)))) + (Sum(IIF(Fields!FA_Posting_Type.Value = "1", CDec(Fields!Sum_Amount.Value), CDec(0))))) - 0 + (Sum(IIF(Fields!FA_Posting_Type.Value = "7", CDec(Fields!Sum_Amount.Value), CDec(0))))) * 30) / ( ((Fields!No__of_Depreciation_Years.Value * 360) - (iif(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value))))>Fields!No__of_Depreciation_Years.Value*360,Fields!No__of_Depreciation_Years.Value*360,(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value)))))))) - (30 * 0))), 2), 0)),"YourGroupName") =sum(CDec(iif(((DateSerial(Year(Parameters!PreviousMonth.Value), Month(Parameters!PreviousMonth.Value) + 2, 0)) <= Fields!Depreciation_Ending_Date.Value), ROUND((((((Sum(IIF(Fields!FA_Posting_Type.Value = "0", CDec(Fields!Sum_Amount.Value), CDec(0)))) + (Sum(IIF(Fields!FA_Posting_Type.Value = "1", CDec(Fields!Sum_Amount.Value), CDec(0))))) - 0 + (Sum(IIF(Fields!FA_Posting_Type.Value = "7", CDec(Fields!Sum_Amount.Value), CDec(0))))) * 30) / ( ((Fields!No__of_Depreciation_Years.Value * 360) - (iif(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value))))>Fields!No__of_Depreciation_Years.Value*360,Fields!No__of_Depreciation_Years.Value*360,(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value)))))))) - (30 * 0))), 2), 0)),"YourGroupName") please try to put ypur grpup name where you are calculating that. if it is not a group then just try to use this expression =sum(CDec(iif(((DateSerial(Year(Parameters!PreviousMonth.Value), Month(Parameters!PreviousMonth.Value) + 2, 0)) <= Fields!Depreciation_Ending_Date.Value), ROUND((((((Sum(IIF(Fields!FA_Posting_Type.Value = "0", CDec(Fields!Sum_Amount.Value), CDec(0)))) + (Sum(IIF(Fields!FA_Posting_Type.Value = "1", CDec(Fields!Sum_Amount.Value), CDec(0))))) - 0 + (Sum(IIF(Fields!FA_Posting_Type.Value = "7", CDec(Fields!Sum_Amount.Value), CDec(0))))) * 30) / ( ((Fields!No__of_Depreciation_Years.Value * 360) - (iif(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value))))>Fields!No__of_Depreciation_Years.Value*360,Fields!No__of_Depreciation_Years.Value*360,(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value)))))))) - (30 * 0))), 2), 0))) =sum(CDec(iif(((DateSerial(Year(Parameters!PreviousMonth.Value), Month(Parameters!PreviousMonth.Value) + 2, 0)) <= Fields!Depreciation_Ending_Date.Value), ROUND((((((Sum(IIF(Fields!FA_Posting_Type.Value = "0", CDec(Fields!Sum_Amount.Value), CDec(0)))) + (Sum(IIF(Fields!FA_Posting_Type.Value = "1", CDec(Fields!Sum_Amount.Value), CDec(0))))) - 0 + (Sum(IIF(Fields!FA_Posting_Type.Value = "7", CDec(Fields!Sum_Amount.Value), CDec(0))))) * 30) / ( ((Fields!No__of_Depreciation_Years.Value * 360) - (iif(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value))))>Fields!No__of_Depreciation_Years.Value*360,Fields!No__of_Depreciation_Years.Value*360,(DateDiff("m",Fields!Depreciation_Starting_Date.Value,Parameters!PreviousMonth.Value)*30+(iif(31-DAY(Fields!Depreciation_Starting_Date.Value)=0,30,(31-DAY(Fields!Depreciation_Starting_Date.Value)))))))) - (30 * 0))), 2), 0))) Many Thanks Syed Qazafi Anjum
July 13th, 2012 6:10pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics