Question

kimobr on Thu, 12 Jul 2012 16:47:04


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


Sponsored



Replies

SqlCraze on Thu, 12 Jul 2012 19:22:09


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

kimobr on Thu, 12 Jul 2012 20:39:06


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

SqlCraze on Fri, 13 Jul 2012 13:49:38


It should be this actually:

=RunningValue(Fields!Col2.Value,Sum,"Dataset1")

Do you want to get 51 as your result set? If yes, then you can also try this:

Right Click on "Col2" and then you will see an option like "Add total". Click on that. It will add a new row to your result set with the total of that column.

Syed Qazafi Anjum on Fri, 13 Jul 2012 14:33:30


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

kimobr on Fri, 13 Jul 2012 15:11:44


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.

kimobr on Fri, 13 Jul 2012 15:17:34


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

Syed Qazafi Anjum on Fri, 13 Jul 2012 15:28:10


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

kimobr on Fri, 13 Jul 2012 16:43:12


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.

Syed Qazafi Anjum on Sat, 14 Jul 2012 08:27:32


Hi There

Thanks for your posting. Please do the following then

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 value

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 value End Function

paste the following expression for the depriciation expression value

=Code.Lookup_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)))

=Code.Lookup_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)))

Now you are passing the despriciation expression to your code and returning the same value back but it will keep adding the value inside the Total_lookup_Sum. Now you can avoid to create new columns inside your table

now put this expression where you would like to get the sum

=Code.Total_lookup_Sum

I hope this will help

Many thanks

Syed Qazafi Anjum



kimobr on Mon, 16 Jul 2012 17:36:06


Is there something I can add to the code so when I use the code on a 2nd, 3rd or ... 60th column is will reset the value back to zero and not continue counting?

Public12345 on Mon, 13 Feb 2017 05:51:32


Hi Syed Qazafi Anjum Sahib

i got the same problem and i used the above mentioned custom code but the problem is that it gives only one row value while not adding the rest. can u please provide step by step or refer us to a video tutorial because i have been doing this for a week but in vain may be we are missing something thanks 

vectorcrael on Tue, 04 Jul 2017 14:58:53


Super code here. perfect actually. This worked well for me, after weeks of searching for a way to aggregate aggregated data.